Using DEX_ROW_TS as SourceControl with MS Dynamics GP (Great Plains) (version 3.2.91i)
By Michael Bock - May 15, 2015
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.
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 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, needs 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 send to the destination system.
Michael founded Rapidi on technological excellence, fantastic customer service and continuous improvement. A data integration specialist since 1987, he remains focused on creating technology that solves real business problems.