SQL Scripts for Microsoft Dynamics GP: Set Account Categories To User-Defined Field 2

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

Read original post SQL Scripts for Microsoft Dynamics GP: Set Account Categories To User-Defined Field 2 at azurecurve|Ramblings of an IT Professional