When setting up RapidiOnline to transfer data from an ERP system to another system, we always try to find a way to read only the changed records for a given type of data. Some systems have a timestamp column in each table - this is e.g. the case for MS Dynamics NAV. The MSSQL column type timestamp is a number column that is automatically increased on every change to a record in that table by the MSSQL server, which makes it very convenient to detect changes.
With other systems like MS Dynamics GP, we are not that lucky. So what options do we have here?
Introducing The DEX_ROW_TS Column
In MS Dynamics GP version 2010 and later, there is a column on some tables called DEX_ROW_TS. The field is of type DateTime and is maintained by some scripts on the SQL Server side. The field is updated with a current datetime UTC value each time a record is created or changed and can be used as SourceControl in RapidiOnline (a SourceControl field is used by RapidiOnline to detect changes to a table and only transfer these changes to another system).
This sounds like a perfect answer to our needs - the server will maintain the field automatically and we can just use it to detect changed and new records to be transferred.
Using the DEX_ROW_TS Column in RapidiOnline
To use the DEX_ROW_TS column as SourceControl in RapidiOnline, you just fill in the Source Control Field like in the picture above. When the Transfer is running, it will automatically keep track of the highest value successfully transferred and set filters to only read new or changed records from that table.
Use the DEX_ROW_TS column carefully
There is however a number of limitations as also pointed out by this blog post http://www.timwappat.info/post/2014/12/04/Beware-DEX_ROW_TS-for-data-synchronisation
- the DEX_ROW_TS column is only implemented on some 40 tables in GP and hence cannot be used in general for all tables.
- for some of the tables such as the Customers and Items tables the DEX_ROW_TS column is also updated when related information in other tables (such as Customer Master Address File and Item Quantity Master) is changed. In this way you get more data transferred than actually needed.
- you should also be aware of that the Demo data coming with GP 2010 have the DEX_ROW_TS values set with the year 2017 - they must have put the server date forward when creating the Demo data. This is not a big problem as it is just Demo data, but if you want to test the functionality on the Demo data, you will need to update the DEX_ROW_TS column to some date back in time, so that newly changed records will have the highest values in DEX_ROW_TS.
- Last but not least, other custom scripts or imports can be made in a way that the DEX_ROW_TS column is not updated. Other programmers and admins working on your GP system, need to be aware of that the DEX_ROW_TS is used and needs to be updated correctly for all changes.
These are some unwanted side effects and so the use of the DEX_ROW_TS is not a no-brainer like using the timestamp column in NAV - you need to consider each use case closely before you choose to use this column as SourceControl.
Alternative ways to detect changes
As an alternative to using SourceControl, RapidiOnline can use its Mirror Technology to detect changes in MS Dynamics GP. The Mirror Technology will automatically keep a copy of the data that you read from each GP table and use this copy to compare and deliver only the changed or new records to the destination system. The technology is very fast and stores and compares only the columns that you actually read (not all columns in the table), which further reduces the changes sent to the destination system.
To use the DEX_ROW_TS as SourceControl, your RapidiConnector needs to be on version 3.2.91i or later. Contact Support if you need to upgrade.
As always, comments are most welcome!
Michael
