Hi,
I couldn’t find a complete guide on how to create a GI that used Audit History, so I wanted to share my success with creating one, in case it helps someone out there looking for something similar.
Our business scenario is that we would like to create a report that lists all of the changes made to suppliers’ bank accounts as a control.
To do this, we created a GI that listed all the changes made to the “Account Number” field within the “Payment” tab of the AP303000 form.
Note: We already had the audit history functionality enabled for the field:
To do this, a join was done on the AuditHistory table and AP.Vendor tables.
Because the AuditHistory table only has the “CombinedKey” field as the Key, the join required filtering out the bAccountID (first 5 characters) from the Combined Key and joining it with bAccountID from the Vendor table:
Note: If you added the CombinedKey field to the GI it would come up something like this:
Which if you then looked at the Audit History screen, you’d be able to see it was made of the below fields:
Then it became a case of selecting the table with the data we were looking for, which was “VendorPaymentMethodDetail”, and pulling the relevant field, which was row 1 of the audit history based on the above screenshot.
Once done, the results tab could be populated to one’s liking, including the operation (update or insert), Supplier ID, date of the change, user that performed the change etc. The only key one was filtering out the new value for the bank account, which is illustrated below
This is because the bank account appears as the last 16 digits within the “Modified field”
Note: the above has been partially sanitised
Which ends up with a GI (partially sanitised) like this:
Thank you to this blog post for helping understand how the AuditHistory table works:
How to Leverage Acumatica's Auditing in Code - Crestwood Associates
For those of you who are familiar with Audit History and GIs - would you have done this another way?