I’m investigating the impact of a customization unintentionally modifying fields that it wasn’t supposed to in a previous version, 2018 R1, and if it is still occurring in our current version, 2019 R2. Luckily I have quite a bit of audit history to refer to. I was able to to use this query from the  Acumatica Developers blog to pull data and then parse it out into something more readable, but I’m unsure of how to validate my results to ensure that I pulled every occurrence of the modified field.

I’m specifically looking for every instance where the SOLine unit price was modified by a particular screenID. Would the following query be sufficient in returning all changes to the unit price? Is there a better way to find what I’m looking for?

SELECT CompanyID, 
       BatchID, 
       ChangeID, 
       ScreenID, 
(
    SELECT TOP 1 username
    FROM Users u
    WHERE PKID = UserID
) as Username, 
       ChangeDate, 
       Operation, 
       REPLACE(CAST(CAST(CombinedKey AS VARCHAR(MAX)) AS VARBINARY(MAX)), 0x0, ',') as CombinedKey, 
       REPLACE(CAST(CAST(ModifiedFields AS VARCHAR(MAX)) AS VARBINARY(MAX)), 0x0, ',') as ModifiedFields
FROM AuditHistory
WHERE ScreenID = 'INSERT_SCREENID'
      AND REPLACE(CAST(CAST(ModifiedFields AS VARCHAR(MAX)) AS VARBINARY(MAX)), 0x0, ',') LIKE '%CuryUnitPrice%'
ORDER BY ChangeDate DESC