x Want to participate? Join the group to interact
Sage CRM
Related
Recommended

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

1 minute read time.

A customer had the requirement to be able to create records in Sage CRM that contained lookup fields to another database.

In my example I want to be able to create a case and then link that case to a field in an external system.

Below is an image that shows that I have been able to link cases to records in table called 'Contacts' in an external database called 'PanoplyTech'.

Once the record is selected the field will behave similarly to a standard Search Select Advanced field.

Note: I have assumed that the contactssummary.asp exists, as that is the default hyperlink

Note: The icon points to an image with the name of the entity e.g. Contacts.

http://[servername]/[installname]/Themes/img/color/Icons/Contacts.gif

You will need to make sure that two versions of the icon exist (contacts.gif and small_contacts.gif) within each of the folders under the Themes used by your system e.g.

  • C:\Program Files\Sage\CRM\[installname]\WWWRoot\Themes\Img\Color\Icons
  • C:\Program Files\Sage\CRM\[installname]\WWWRoot\Themes\Img\default\Icons
  • C:\Program Files\Sage\CRM\[installname]\WWWRoot\Themes\Img\neutral\Icons

This is very easy to be able to accomplish and does not require any code to create the link.

Step 1: Create the link to the Database (Documented here) https://community.sagecrm.com/developerhelp/Default_CSH.htm#Developer/DC_CreateNewDBConnection.htm

Step 2: Create a link to the Table (Documented here) https://community.sagecrm.com/developerhelp/Default_CSH.htm#Developer/DC_CreateNewTableConnection.htm

I also made sure that I provided my ID field for the external database table.

Step 3: Add the Translations that enable the Search Select Advanced to work. This is discussed in the article "How do I add the ID from a Custom Table table as an Search Select Advanced?". http://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2007/06/23/how-do-i-add-the-id-from-a-custom-table-table-as-an-search-select-advanced.aspx

In my example these were

  • family=SS_Entities, code='Contacts', capt_us='Contacts'
  • family=SS_SearchTables, code='Contacts', capt_us='Contacts'
  • family=SS_ViewFields, code='Contacts', capt_us='companyname'
  • family=SS_IdFields, code='Contacts', capt_us='companyid'

Step 4: Add the field case_panoplycompanyid to the cases table as type Search Select Advanced.

Step 5: Add the field case_panoplycompanyid to the CasesWebPicker screen.

The work is done and the link has been created.

Parents

  • 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.

Comment

  • 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.

Children
No Data
Generic Content
Generic Content

*Community Hub is the new name for Sage City