Finding the best date to use for when an record was last modified

I have a sync app running and it's exporting out from Sage: Orders, Shipments, Customers, Inventory, and Credit Notes. I want to only grab the records that have changed since the last time I ran the sync. For some of these it seems I can grab the DATELASTMN, but for orders, shipments and credit notes, I'm trying to find the best date to use. I was using last post date, but when I post the order, this date doesn't always seem to change. I'm wondering should post date always change when I post and if there is a better date record I can use for orders?

  • 0

    Part of the challenge is: What do you define as a change?  The creation of each of those documents is easy, use the last xyzUNIQ field that is the primary key for the order, shipment, invoice, credit note tables.  Customer's have the date last maintained, that's easy.

    After that, do you need to capture when the expected shipment date changes on the order?  When the description field is changed?

    Day-End will also make changes to your records that you may or may not want to capture.

    If you have access to Orchid's Extender software, it can be told to log specific changes to your different records into a table and then your software can just can that table for a list of all of the documents that need to be synced.

    Otherwise, you might look at database triggers on those tables and let SQL do the work. They could be quite sophisticated (but keep them fast) and at least help your process find the records that it should look at for sync purposes.