Data Repair: Microsoft Great Plains Dynamics GP

Data Repair: Microsoft Great Plains Dynamics GP – Alba Spectrum

Alba Spectrum Group, http://www.albaspectrum.com help@albaspectrum.com 1-866-528-0577

First of all we have to make public statement that this article should only be published on AlbaSpectrum website and not intended for open press. Alba Spectrum has copyright on this article and doesn’t authorize anyone to republish it in free press

When we are talking about Microsoft Great Plains Dynamics GP data fixing, you should be familiar with GP data consistency validation tools, such as Check Links. If you have custom logic, or custom integration, than check links might not be recommended by your modification partner, please check with them. If check links doesn’t do the job then you need to consider the following:

Select SQL statement data probing. Try to construct data aggregation query (with sum logic) that you transaction header agrees with your document lines. Good example is this: if you suspect that your SOP invoice doesn’t match with invoice lines, try to summarize SOP10200 (this is Sales transaction amount table) qty, cost and price with SOP10100 (Sales header table)
Update statement building. If you figured out that your header tables doesn’t agree with lines counterpart (and check links doesn’t fix the issue), you should prepare yourself to data fixing necessity – the reasons of data inconsistency are typically one of the following: computer crash during data entry or posting, inaccurate customization or integration
Prior to Updating. Make your company db backup or backup the table that you intend to modify, use select * into logic if backing up single table. When you restore table from backup, you should drop first DEX_ROW_ID column from table backup and then use insert into statement
Cursor vs. Update. This is up to SQL DBA or DB developer. In some cases, there is no way to reconstruct original table (good example is IV10200 table, where you rebuild UNITCOST and ADJUNITCOST), but do it from SQL cursor; as a rule try first to deploy update statement, prior to rescuing to SQL cursor dues to the fact that cursor is a way slower and in the case of huge volume of transactions may not be feasible

Alba Spectrum Group: http://www.albaspectrum.com help@albaspectrum.com 1-866-528-0577, 1-630-961-5918, serving Microsoft Dynamics GP Great Plains customers in USA/Canada nationwide. Local service is available in Dallas and Houston, TX: Richmond, Sugar Land, Katy, Rosenberg, Missouri City, Pearland, Friendswood, Meadows, Mission Bend, Jersey Village; Chicago, IL: Naperville, Aurora, Bolingbrook, Romeoville, Joliet, Wheaton, Lisle, Downers Grove, Schaumburg, Elk Grove, Lombard, Woodridge, Darien, Westmont, Batavia, St. Charles, Elgin, Crystal Lake.

International: Brazil-English Brasil-Portuguese

We serve large scale Microsoft Dynamics GP Great Plains Customization, Data Conversion/Massage/Migration, version upgrade, custom software development and integration to GP, heterogeneous reporting for GP: SQL Server Reporting Services, Crystal Reports; Visual Studio.Net programming for GP, including eConnect, ADO to GP database; complex GP data repair (when somebody damaged GP data via inaccurate UPDATE SQL statement)