Creating a Search Select Advanced Field that links a CRM table to a Parent Table in an External Database

Hi Jeff,

I have made two SSA of external tables and I am trying to make a restriction between them. They both show data but the restrictor doesn´t work.

I have put the id fields, named fields XXX_tableXid YYY_tableYid and OPPO_tableXid and OPPO_tableXid in the Opportunity table but without any result.

Is this possible to acomplish?

Regards,

Eduardo Launa

Jeff,

Do you have any ideas on why the search select advanced field wouldn't be displaying any data when clicking the mag glass? When checking the SQL log, it's returning a query that when run via SSMS, data is returned. Any thoughts on this?

Thanks in advance!

Samantha Knigge

Curious as to whether you were ever successful in getting this to work.  I am interested in customizing our Sage CRM 7.2 in order to be able to search from CRM baack to our Sage 300 ERP 2014 OE Invoice detail optional fields.

Excellent Guide Jeff, still works fine even in CRM 7.3.

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.

Hi Adam,

I currently have exactly the same problem. Have you found a solution? About a tip I would be happy.

Thanks,

Chris

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

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