You might be facing the dilemma of figuring out where to start with tracking the changes to Dynamics GP tables and then exporting those changes to some file. You might be out of ideas on what to do, and by some chance, you end up at this blog post. Well, you’re in luck; this post is on how you can approach tracking changes to Microsoft Dynamics GP tables and then exporting them.
MSSQL (from here onward, referred to as SQL) includes a tool named triggers that allows one to run custom query code in conjunction with inserts, updates, or deletes done to a table. Triggers have access to two special temporary tables – the inserted table and the deleted table – that hold information on what was changed when the change occurs. The inserted table includes information on inserted and updated records, while the deleted table has data on what was deleted. In order to track the changes to a table, a trigger can be programmed to pull information from the inserted and deleted tables and then log them to a custom log table. If there was a need to track newly created records to the Vendor card in GP, for example, one would add triggers to at least the PM00200 (Vendor Master File – Purchasing) and PM00300 (Address Master – Purchasing) tables and use information from the inserted table to create entries to the custom Vendor card log table. Some words of caution: before deploying any triggers to production, be sure to test the trigger extensively. Having a bad trigger that fails can interrupt GP functionality. If, for example, the insert trigger on a PM00200 fails while creating a Vendor card, the Vendor card will fail to be created as well.
There are two main options for exporting changes from a logging table.
- The first would be having someone with some SQL know-how to go into SQL Server Management Studios and then query the logging table. If the table contains a logged datetime field, the user can add a date filter to the where-clause of the query and look at log entries from a particular interval. The query results can then either be saved to a file by choosing to execute the results to file or they can be viewed within SQL Server Management Studios by executing the results to a grid.
- The second option involves building a custom application that exports the changes. The application would have to connect to the custom log table in SQL and execute a query that pulls the appropriate data and then write this data to some file.
This article was written by Kevin Huynh, Dynamics Technical Consultant for Tridea Partners. Tridea is a leading Microsoft Dynamics GP provider serving CA, UT, CO and surrounding regions.