What if you need to discover which Sage CRM tables are related to Companies and Persons?

If you have access directly to the database a very easy way of discovering the children of the company or person table would be to access the custom_tables information.

select bord_name, bord_idfield, bord_personupdatefieldname, bord_companyupdatefieldname from custom_tables
where bord_personupdatefieldname is not null
and bord_webservicetable = 'Y'

The above query finds 11 tables exposed to web services. This includes the Notes table which is related to person and company records using the rather awkward notes_foreignid mechanism.

The notes table has been discussed in other articles.

Technically it would be possible to expose the custom_table to webservices so that you could discover the releationships.

The custom_table it self is modelled in the custom_table metadata table and by setting the field value bord_WebServiceTable to 'Y' the table would be exposed to webservices.

But I really dislike exposing metadata tables to webservices. This is because there is no possibility of just creating a 'read' access. Once the table has been exposed then meta data can be altered. It is this potential integrity issue which I dislike.

So I wanted to find away that I could use standard webservice calls that can find out foreign key information.

The key webservice methods are the getallmetadata() and getmetadata() methods.

I have made the assumption that foreign keys will follow a naming pattern. So if I am looking for the tables that are children of the 'company' table then those child tables would have a field with a name like [xxx]companyid (e.,g. primarycompanyid or simply companyid. If the assumption is correct then these fields can be discovered.

I have uploaded to the resources section a sample Visual Studio 2005 C# application that uses the getallmetadata() and getmetadata() methods to discover the children of company, person, opportunity and the communication tables. It makes the assumption that foreignkeys follow the predicatable naming convention described above.

It's the discovery of the primary key naming convention that causes a problem, which unfortunately does not always follow a consistent naming convention.

If you are working with CRM tables then the primary key typically follows the pattern

prefix_tablenameID

e.g.

Table: Person
PKey: pers_personid

Table: Opportunity
PKey: oppo_opportunityid

But there are plenty of exceptions such as:

- "Case_CaseId" (following convention should be: "Case_CasesId"?)
- "Mrkt_MarketingItemId" (following convention should be: "Mrkt_MarketingId"?)
- "Note_NoteId" (following convention should be: "Note_NotesId?")

In the KeyExplorer application I have posted I make the assumption that primary keys will either follow the standard convention or at least contain the first 4 letters of the table name but importantly be of the XML data type 'xsd:int'. If the application can't find a clear candidate for the primary key it will return the message 'Primary Key Ambiguous'.

To download the Visual Studio 2017 C# project follow this link: KeyExplorer.zip