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

  • 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

Comment

  • 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

Children
No Data
Generic Content
Generic Content

*Community Hub is the new name for Sage City