Hi,
Is there any solution for this? Did somebody manage to create this SSA with external table? I tried several times with different CRM versions ( 2019, 2020) but with no luck. As Gianni, my dropdown is empty, I cannot add a record in the SSA. Even with the search screen, when I select a record, I come back to the SSA but it is still empty. If anyboby knows what is the trick, I'll appreciate it ;-)

Thank you for your response

I managed to get it working (although not 100%) by adding an empty column named like the error said in the external table. For example in my last comment:

The external table Articulos has no Articulos column, i then created a new column called this way (Articulos) of type smallint, all filled with 0s etc etc and then it started working

This way, i can now see the rows of that external table but not being able to click a in row to then add the data to the CRM field

In this thread community.sagecrm.com/.../how-do-i-add-the-id-from-a-custom-table-table-as-an-search-select-advanced.aspx it says "In the list box, set hyperlink on the Contact Name (or whatever field) to Opportunity (for example) and it will do some clever stuff to go back to the calling screen." while this works, i go back to the calling/opener screen the data is not updated in the CRM field

Looking at the html of the BancosGrid i see this javascript on each row/hyperlink on a onclick event

(Example is now with Bancos, as i created another field to keep trying. I did the same as explined above but for the external table Bancos)

try {if(event)event.returnValue=false;} catch(err) {} if(window.opener.SetIDcomp_muranobancos) window.opener.SetIDcomp_muranobancos('0001');window.close();

That looks pretty ok to me but even as the popup windows closes, the CRM field (SetIDcomp_muranobancos) is not updated

I'm guessing that have something to do with the BancosSummary.asp as said in this post but it is neccesary? What do i put in the BancosSummary.asp? Mostyle because if a see the html of the popup screen with the BancosGrid i see the above javascript but inspecting the results when clicking in the maganifize glass i see this:

document.getElementById('hiddenShowPopupMenuDiv').style.display='none' Setcomp_muranobancos('','0000','/CRM/CustomPages/Bancos/BancosSummary.asp?SID=150828682130929&F=&J=Bancos/BancosSummary.asp&Key58=0000&CodigoBanco=0000','Bancos','');$crmPopupParent.oPopupcomp_muranobancos.hide();

There is a call (guessing it a call) to CRM/CustomPages/Bancos/BancosSummary.asp so i'm guessing that BancosSummry is the when to return the data to then be putted into the CRM field, but what do i put in the BancosSummary.asp?

Thanks

I am not sure what to suggest. I've just tried this again with an external database and everything seems to be working as I've outlined.

The first thing that I did to make sure the communication with the external database was working correctly was to test the list with a call from a menu using runblock. That just made sure that the query generated was going to the correct database.

The other thing that I did was to keep the definition of the Advanced Search Select field as simple as possible - didn't play about with restrictor fields or tied fields and I left the 'default' blank.

Sometime ago I wrote a series of articles looking at the accessing Sage X3 tables directly from Sage CRM UI: See:

community.sagecrm.com/.../sage-crm-7-2-extending-the-sage-erp-x3-integration-part-8.aspx

As apawsey says, i'm having this issue too

Creating the Avanced Select field looks ok, adding it to a screen ok,.

I have also create a screen called AritculosSearchBox whit this configuration:

Screen type: Search screen

Foreign table: Aritculos (It is a table from Sage Murano)

Foreign table column: IdArticulos

Also created a list ArticulosGrid whit whis configuration:

Table or view to use: Articulos

I think the problem is here, the name of the table. When using the search field im guessing that takes Arituclos as a local table (as apawsey said)

When i try to use the field, it does not return data, if a click on the mag. glass and then click on the search button, gives this error:

Unexpected event: Exception: field Articulos not found

Seeing the logs, show this:

'sep. 1 2016 13:17:49.018 3136 5024 1 fselectsql,time,sql,errormsg 15 SELECT TOP 17 IdArticulo, Articulos FROM Articulos WITH (NOLOCK) WHERE (Articulos LIKE N'%' ESCAPE '|' OR COALESCE(Articulos, N'') = N'') order by Articulos Invalid column name 'Articulos''

So, i'm guessing it have someting to do with the SELECT TOP 17 IdArticulo, Articulos (Articulos is not a field in the external table) or FROM Articulos (guessing that i will be like externaldb.Articulos)

Any help would be much appreciate it

HI Jeff,

I have tried and double checked this about 10 times now, and I cannot get it to work. I am trying to lookup data against a SAGE X3 database. I have added the Connection.. all good, added the table, and I get a list of 'fields' when I look at it under Customization so seems CRM can happily connect to the database. The table is called FACILITY, the Table Caption is FACILITY.

However I then add the translation fields as follows:

Caption Family Caption Code US Translation Caption Family Type

SS_Entities FACILITY FACILITY Tags

SS_IdFields FACILITY FCY_0 Choices

SS_SearchTables FACILITY FACILITY Tags

SS_ViewFields FACILITY FCYNAM_0 Choices

and I get nothing in the drop down box, and when I run SQL profiler to look at what's happening, I see a perfect query... being run against the wrong database! The query is exactly correct, it just runs it against the CRM database and not the X3 through the connection.

I've tried to resolve this in many ways but I get nowhere. The 'entity' can be queried through the customization pages, but somehow this Search Select will not tie up to it.

Any help would be enormously appreciated as I've been fiddling with this for days on and off.

Thanks,

Adam.