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 (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). */ 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.