I was tempted to start a new series of articles called "Mysterious Tables of Sage CRM" but since this is supposed to be a sensible library of "Hints, Tips and Tricks" I have resisted. Nevertheless there are a few tables in the Sage CRM database that whose purpose maybe obscure to you. Generally Sage CRM's database could not be simpler; the table that stores the company information is called "company", the table that stores address information is called "address" and so on.
But there are a few tables that may leave you scratching your head, especially when there seems to be SQL activity associated with them during insert, update or delete of other records. The Journal table is one of these database structures that is maintained as data manipulation is carried out on tables linked to the Person and Company entities.
This article was prompted by a colleague who noticed that the journal table was written to during the merging of two person records. The journal table is only relevant to Sage CRM systems that are integrated with other Sage ERP systems using a particular integration mechanism. Sage CRM offers the ability to handle integrations with other software in a wide variety of ways. An article I wrote so time ago called "Integration with External Systems and the Synchronization of Application Data" discusses the range of techniques available to a developer.
Certain Sage ERP systems are integrated with Sage CRM using a SOAP based web services contract. This is not an open API but is very similar to the general SOAP web service interface that has been discussed lots of times on this site.
The Journal table is used to keep track of Create Update Delete activity on company, person, address and phone tables etc which is the information needed by the synch engine to make sure that the CRM data changes are posted to the ERP system.
If you monitor the SQL log you will notice the Journal table is written into when a person or a company is added or changed via the main user interface, the web service interface or the COM and .NET api objects.
Note: Direct manipulation of data using SQL would not update the jounal table and so in those systems which use the SOAP contract this may be an issue.
The Journal Table was introduced in Sage CRM 6.1 which is when the SOAP contract was first started to be used by Sage ERP development teams.
To the best of my knowledge the following is true. The Extended Enterprise Integration with Sage MAS 90 and Sage MAS 200 does not use the journal table, neither does the Sage Accpac ERP integration.
The following is a list of some of the different Sage ERP products which in their Integration with Sage CRM make use of the journal table.