Referencing records from a Custom Table in a Advanced Search Select field.

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

Referencing records from a Custom Table in a Advanced Search Select field.

  • Comments 9
  • Likes

In the screen shot below you can see that in my customised implementation of Sage CRM 7.3 sp3, I am able to associate an Opportunity with an record held in an external database.

The external table in my system is called 'Contacts'

To allow the table to be referenced by an Advanced Search Select I had to add the following translations into the system.

  • family=SS_Entities, code='Contacts', capt_us='Contacts'
  • family=SS_SearchTables, code='Contacts', capt_us='Contacts'
  • family=SS_ViewFields, code='Contacts', capt_us='ContactName'
  • family=SS_IdFields, code='Contacts', capt_us='CompanyId'

Once the translations have been entered then you should be able to define a field as an Advanced Search Select and see 'Contacts' in the entity drop down in customise fields.

Then you add a list called 'ContactsGrid' and a screen called 'ContactsSearchBox' then these will be used when you click on the search button. And you can put whatever fields you like in the search box and list box.
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.

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

  • I discovered that i need to add 'Tags' in the Caption Family Type to show the entity in the Search Select Advanced.

  • Jeff,

    In response to this and because I have had to do this task a number of times I have created a Component for Sage CRM v7.1 which allow you to enter the four parameters and that will then create the translations for you.

    Can we make this available for the community as I am sure lots of members trying to achieve this process will find it of use?


  • Justin

    That's a great idea.  Are you a member of the Dev Program? If you are then we can add this into the Marketplace... or you could write a guest blog that explains how it was done and include the component.  Contact about writing the article.

  • The original post says -

    "Then you add a list called 'VacancyGrid' and a screen called 'VacancySearchBox' then these will be used when you click on the search button"

    When setting up VacancyGrid, what value should be placed in "Table or view to select fields from".

    I've tried Vacancy and vVacancy without success. Because there isn't a Views tab I don't know the names to use.

  • Steve

    If you have no Views tab then it sounds like you are linking to an external table.  Have you seen this article?

  • Jeff:

    Does this approach work in 7.2d or is there another approach that needs to be taken to do a lookup to another table (custom table)?  I have followed the instructions above and added the appropriate caption family type (i.e. choices; tags).  I can see the fields when I add the search select advanced field to my company field and I selected the appropriate fields that I want to do a "lookup" on from the custom entity table.  However, nothing displays.  It is a though there is no data in the custom table (custom entity).  However, the data is there....I am puzzled as to why this is not working.

    Any help would be greatly appreciated.  Thanks!

  • Follow up to post:

    I talked with technical support.  He indicated that it appeared I had followed the instructions above...however, it was not working.  Instead of a Search Select Advanced field he had me use an Intelligent Select field and it works!

  • Michele

    I am glad that you have got what you needed.

  • Jeff

    I tried to use this method to add MailChimp campaigns as an SSA on Wave Activities and it behaves as expected on the entry screen (the MailChimp Campaigns are listed by description and selectable), the MailChimp campaign id saves to the Wave Activity record but then the record is unviewable in the Wave Activity summary screen with the following error:

    "Access violation at address 1A5E8C12 in module 'eware.dll'. Read of address 00000036"

    Any idea why that is happening and is there a way to link the MailChimp Campaign to a Wave Activity?