You may have a business requirement to cascade a change through the child records of a company.  I have discussed previously how a table level script can be used to update all opportunities if the company is reassigned to a new account manager.  See the article "A Table Level Script to update Opportunities when a Company is Reassigned".

But what if you want to update all child records with that information?  How can you know in code what records may need updating?  If the system has had custom entities added into the data model using the Advanced Customization Wizard, how can we know that these are child records of the company?

The information we need is all held in the Meta Data table "custom_tables".

Consider this SQL query


select bord_name, bord_idfield, bord_progresstablename, bord_progressnotefield, bord_personupdatefieldname, bord_companyupdatefieldname, bord_assigneduserid, bord_channelid from custom_tables
order by bord_name
 

From this we can find out which tables are child records of Company, which tables are the children of Persons.  We can also see which are directly linked to Users and the Teams (ChannelID).

So if you want to know what tables you need to update if certain company data is changed then that is a simple matter of asking the database


select bord_name, bord_companyupdatefieldname from custom_tables where bord_companyupdatefieldname is not null
 

A version of this query can be run using either the QueryObject or the Record object.  This will give you a list of all the entities including custom entities.  It is future proof too.  If Sage decides to add new entities then these will also be listed.

In my system the query above has given me

bord_name

bord_companyupdatefieldname

Address_Link                            AdLi_CompanyId
Cases                                   Case_PrimaryCompanyId
Communication                           cmli_comm_Companyid
Company                                 Comp_CompanyId
Email                                   Emai_CompanyID
Library                                 Libr_CompanyId
Marketing                               Mrkt_CompanyId
Notes                                   Note_ForeignId
Opportunity                             Oppo_PrimaryCompanyId
Person                                  pers_companyid
Phone                                   Phon_CompanyID
Comm_Link                               CmLi_Comm_CompanyId
Person_Link                             PeLi_CompanyID
Lead                                    Lead_PrimaryCompanyId
Account                                 Acc_CompanyId
Project                                 proj_CompanyId

 This provides the foreign key column to allow us to find the child records of a company very easily.

Note

  • Account is a table only visible in installs of Sage CRM that are integrated to certain Sage ERP systems.
  • In the example above Project is a custom table added as a child of Company.