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

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.