Date timestamp on GP 10 Tables

I'm exporting data out of some gp tables on a regular basis.  I would like to do this incrementally.  However there doesn't seem to be any consistency with any sort of timestamp column.  Some columns have a dex_row_ts column.  Some have a modifdt, but in many cases that date is not the same as dex_row_ts.  Some tables have no modified date at all.  I don't suppose there's a table that can be joined that might have this last-modified info?
LVL 11
b_levittAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TrackrabbitCommented:
To my knowledge, there isn't a consistent method of time/date stamp within Great Plains. However, for your purposes, you may be able to usage stats. Take a look at the code below (change Company DB to your company DB name!) :

SELECT  OBJECT_NAME(OBJECT_ID) AS DatabaseName,
        last_user_update,
        *
FROM    sys.dm_db_index_usage_stats
WHERE   database_id = DB_ID('COMPANY DB')
        AND OBJECT_ID = OBJECT_ID('sop10100')
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

If you are trying to track new records, changed records, or deleted records for export, you should use eConnect Requester.  Timestamps in GP will not be sufficient, as you have discovered.

Review the eConnect help file for basic info, and see my recent blog post on eConnect Requester for more detailed info:

http://dynamicsgpland.blogspot.com/2011/09/limitations-of-dynamics-gp-econnect.html

I just recommend reading the eConnect_Out table directly and joining to the source data tables, but you could use MSMQ and the Requester service if you wanted--but I find those to be total overkill for nearly all typical GP customer situations.

If you have any questions about configuring or using Requester, please let me know.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
b_levittAuthor Commented:
@Trackrabbit - interesting approach but this is probably a little too generic.  I wouldn't know how to join this to an individual record.

@Steve - this looks interesting, but based on the article you pasted, I'm guessing this doesn't track some of an ancillary tables like the cost categories, salesmen, users, etc.

I'll keep looking around but ultimately it looks like the simple answer might be 'no'.
Steve EndowMicrosoft MVP - Dynamics GPCommented:
Hi,

Actually, eConnect Requester is pretty comprehensive, tracking inserts, updates, and deletes for 105 objects in the Dynamics GP company databases.

It does track Project Account Cost Categories (PA01001) and a lot of other PA tables, and it does track RM Salespersons (RM00301).

By "users", if you mean GP users, then it does not track that, as those are setup centrally in the Dynamics database and via SQL permissions.

Thanks,

Steve Endow
Dynamics GP Certified Trainer
Dynamics GP Certified IT Professional
b_levittAuthor Commented:
Wow, I found the list and all but one of my tables are covered:
http://msdn.microsoft.com/en-us/library/bb625133.aspx

The last table is very small so I don't mind getting the full table.

Excellent help as usual Steve.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.