This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script was created for a user who was migrating from producing financial reports using Management Reporter to using Jet Reports.
Their account categories had been simply set to and Income and Expenditure for Management Reporter which could easily use the user-defined fields. However, reporting off the user defined fields in Jet Reports is somewhat more complex so they added new categories into Dynamics GP (using my insert categories from text file script) and needed to update the categories assigned to the G/L Accounts.
Fortunately, the previous entries in UDF2 were the exact values inserted as categories, so we could use a simple script to set the account category to the same as UDF2 by doing a lookup on the Account Category Master (GL00102) table.
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT USERDEF2,ACCATNUM,* FROM GL00100
GO
UPDATE
['Account Master']
SET
ACCATNUM = ['Account Category Master'].ACCATNUM
FROM
GL00100 AS ['Account Master'] --Breakdown Account Master (GL00100)
INNER JOIN
GL00102 AS ['Account Category Master'] --Account Category Master (GL00102)
ON
(['Account Category Master'].ACCATDSC = ['Account Master'].USERDEF2
WHERE
LEN(RTRIM(['Account Master'].USERDEF2)) > 0
GO
