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.