x Want to participate? Join the group to interact
Sage CRM
Related
Recommended

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

1 minute read time.

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

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

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

  • Michele

    I am glad that you have got what you needed.

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

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

Generic Content
Generic Content

*Community Hub is the new name for Sage City