?
Solved

Date timestamp on GP 10 Tables

Posted on 2011-10-05
5
Medium Priority
?
879 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:b_levitt
  • 2
  • 2
5 Comments
 
LVL 2

Expert Comment

by:Trackrabbit
ID: 36917182
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')
0
 
LVL 18

Accepted Solution

by:
Steve Endow earned 2000 total points
ID: 36918123
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
0
 
LVL 11

Author Comment

by:b_levitt
ID: 36918646
@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'.
0
 
LVL 18

Expert Comment

by:Steve Endow
ID: 36919218
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
0
 
LVL 11

Author Comment

by:b_levitt
ID: 36919447
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.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question