Visit Sage CRM Main Website  |  About Sage CRM
Visit Sage CRM Main Website  |  About Sage CRM

The Journal Table

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

The Journal Table

Jeff Richards
3 Sep 2018 2:52 AM
  • Comments 6
  • Likes

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.

Comments
  • R Graham Macleod
    28 Oct 2011 7:19 PM

    Jeff, thanks for thist post.  Another "mystery table" is the CRMEmailPhoneData.  It's not such a big mystery but it certainly appears to be poorly documented.  I've searched the ecosystem, the 7.1 What's New, Sys Admin Guide and Installation / Upgrade guides and haven't found hardly any references to this table at all.  What might be helpful would be a discussion of how it's updated and if there are any scenarios (e.g. upgrade vs. new install) in which this data might need to be manually refreshed.

  • R Graham Macleod
    26 Sep 2012 12:31 AM

    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?

  • Jeff Richards
    26 Sep 2012 12:07 PM

    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.

  • Michele Gaw
    5 Feb 2016 10:52 PM

    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!

  • berghuismeereboerk
    19 Nov 2016 12:50 PM

    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

  • Jeff Richards
    21 Nov 2016 6:45 AM

    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.