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 10
  • Likes

In the screen shot below you can see that in my customised implementation of Sage CRM, 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

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

  • 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 sagecrmteam@sage.com 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?

    community.sagecrm.com/.../creating-a-search-select-advanced-field-that-links-a-crm-table-to-a-parent-table-in-an-external-database.aspx

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

  • Jeff:

    I need some assistance in modifying an existing SSA field for companies with related companies.

    On an Opportunity for the field oppo_primarypersonid, a SSA field is used to display a list of people for the opportunities associated company.  In this scenario, the company has related companies associated with it and they are defined in the table RelatedEntityData.  It is a parent child relationship (i.e. Parent = Corporate Child = Location)

    The users want to be able to see all people from the related parent and child or the related child and parent when making a selection for the SSA field for oppo_primarypersonid.  Therefore, if the user was on an opportunity for the parent company, they would see all the people from the parent and child.  If the user was on an opportunity for the child, they would see all the people from parent and child.  In the oppo_primarypersonid, they want to store the pers_personid in this field.  This person might be from the parent or the child company.

    I don't have a lot of experience setting up a SSA field, therefore, I am having trouble wrapping my brain around how I might do this or if I could even do this with a SSA field.

    Am I on the right track with this?  I am hoping you can push me in the right direction.

    Any assistance that you could provide would be greatly appreciated!