The Journal Table

2 minute read time.

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 may be 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 the 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 journal 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 100 (MAS 90) does not use the journal table, neither does the Sage 300 integration.

Sage CRM over 7 years ago

No. There isn't a way to tell Sage CRM to stop using the Journal table. This behaviour is deep in Sage CRM's code. If it is writing into a table it is because it needs to write into that table.
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel
berghuismeereboerk over 7 years ago

Hi Jeff,

We use an On Premise Installation of Sage CRM 7.3. As I read it, we don't need the Journal table, Is it possible for us to tell Sage to do not use this table?

Thanks,

Karin
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel
Michele Gaw over 8 years ago

Jeff:

Thanks for the explanation. This is an interesting table that I didn't know about. It seems that this could be a useful table if you were needing to do your own integrations! :-)

Thanks!
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel
Sage CRM over 11 years ago

Graham

You would need to look at whether the data in the journal table is accessed by any views of the system in fetching data to the screen or whether it is only accessed during updates and deletes. If it is only added to and never read from them that would be a candidate for truncation. But test, test, test test.

There is another article that discussed the DData table which is used for the Key Attribute data and so very much important for all types of marketing data.

Rep_deletes is used I think within the SOLO synchronization.
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel
Graham MacLeod over 11 years ago

Jeff, for standalone CRM installs, it would seem safe to periodically truncate the Journal table, yes? Two other tables I'm curious about are Rep_Deletes and DData. I know DData is used in data uploads and Groups, but does it also store data that could be obsolete? And if so, how would we identify these rows?
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel

Sage CRM

The Journal Table

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING