I was recently putting together a report for a client on Fixed Allocation Accounts and came across some corrupt data in the Fabrikam, Sample Company, Database. To avoid encountering this issue again, next time I am working in this area, I put together a simple script to remove the corrupt records by joining Fixed Allocation Master (GL00103) with Breakdown Account Master (GL00100) to identify the data which should not be there and delete it; this makes the script generic enough that I can use it on a live company should the need ever arise.
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).
*/
DELETE FROM
['Fixed Allocation Master']
FROM
GL00103 AS ['Fixed Allocation Master']
INNER JOIN
GL00100 ['Account Master']
ON
['Account Master'].ACTINDX = ['Fixed Allocation Master'].ACTINDX
AND
['Account Master'].FXDORVAR = 2
As always, before you run any script, ensure you have a good backup of your database and verify the results after the script has been run.
