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

  • 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

Comment

  • 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

Children
No Data
Generic Content
Generic Content

*Community Hub is the new name for Sage City