Having troubles trying to make a SSA work tha retrieves data from an external DDBB Table, in this case, a table from Sage Murano

Have been guiding me by this post

https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2011/11/16/creating-a-search-select-advanced-field-that-links-a-crm-table-to-a-parent-table-in-an-external-database.aspx

But i still having troubles

The connection to the external DDBB works

The connection to the external table works also, i can see it as a secondary entity, i can see its fields all in the CRM interface

I have added the required translations

Created the AutonomiasSearchBox screen and AutonomiasGrid list (The external table is Autonomias from a Sage Murano standard installation)

Created the SSA field in the Company entity, can select Autonomias as its entity search

Added the field to the CompanyBoxLong screen

But here is the problem:

When trying to use the search funcionalty of the SSA field, it gives an error:

"Se ha producido un evento inesperado.: Exception: field Autonomias not found"

"Unexpected error: Exception: field Autonomias not found"

Checking the logs i see this:

nov. 17 2016 12:05:25.919 5260 2468 1 Error de SQL : El nombre de columna 'Autonomias' no es válido (SELECT TOP 17 Autonomia, Autonomias FROM Autonomias WITH (NOLOCK) WHERE (Autonomias LIKE N'%' ESCAPE '|' OR COALESCE(Autonomias, N'') = N'') order by Autonomias)

Which basically says "SQL error: The Autonomias column name is not valid"

In the external table the column "Autonomias" does not exist

How can i solve this? By Sage CRM is putting that another field to the SQL, which it is not even a column in the external table from the beginning?

Do i have put more data the screen or list? Like a view, foreign table etc

In the screen and list can put a view:

view Autonomia does not exist

view Autonomias does exist

But with same results

Tried to solve the issue by creating a view in Sage Murano like this:

CREATE VIEW vtestAutonomias AS

SELECT *, Autonomia AS 'Autonomias' FROM Autonomias

Added a new column called Autonomias (note the s) and then add this view as a external table in CRM.

This way at first seems to work, the search functuonality of the SSA field works, it even updates the hidden inputs the field (input of type hidden with name and id equals to the field name, like comp_tpc) but then when save the screen it throws another error:

"Unexpected error: Exception: SQL error" (Do not have the text right now)

And checking the logs i can see:

nov. 8 2016 9:41:27.116 2624 2508 1 fselectsql,time,sql,errormsg 125 select from CustomVAutonomias WITH (NOLOCK) where CodigoAutonomia = 61 Incorrect syntax near the keyword 'from' (This log is from another try but it is the same error)

This time the SQL is not even correct, the select statement does not have a field/column to select, not even an *

The value is saved into the DDBB correctly, at least i think so. I can see the ID of the row i selected from the list of the SSA field in the Company table, in its correct column (comp_tpc in this case)

I have read a lot and can not get it to work, any help would be much appreciate it thank you