How to Leverage Acumatica’s Auditing in Code

More by
Audit in code

Auditing Feature Summary

Let’s start with a refresher on what Auditing is in Acumatica and how to set it up.  Acumatica’s ERP allows a user to track changes made to almost any Acumatica field.

Let’s say you want to track whenever a credit line value is changed for a customer and when the Address Line 2 is changed, and any changes to a customer attribute of INDUSTRY.

Select the Customers maintenance screen on the Audit screen and notice that several tables and fields might already be selected for you.  Assume that this might already be set up and our solution needs to only focus on the field or fields that we are interested in.  We’ll need to include Customer, BACCOUNT, and CSANSWERS for attributes.  When you are finished selecting the proper tables and fields make sure to check the Active box to start auditing.

Now we can test a change.  Open ABC Holdings or any customer and change the credit limit.  I’m using the Sales Demo database, so I’m going to change the credit line to $123,456.78.  I’m going to change Address Line 2 to Suite 122, and I’m going to add an attribute of BANKING.

If you then open the Acumatica Audit History screen you can see your changes.

What’s Going on Behind the Scenes

First, let’s look at the AuditHistory table in SQL.

Notice that not just the ABCHOLDING customer had changes, but 2 other customers had changes as well.  But we didn’t touch those.  See if you can guess why as we continue on.  I’ll explain more in the next section.

What we see by reviewing the AuditHistory table are two records for our customer, one for the Customer table and one for the base BAccount table.

  • The BatchID will give us a grouping of when a change was made. In my example I changed three different things on the customer screen, so they all got neatly grouped together
  • ChangeID will be a unique ID of each change
  • For Operation you will see a U for updated and I for inserted
  • TableName obviously tells us what table was changed
  • CombinedKey will provide information on what master record was changed
  • The ModifiedFields field on the Customer record shows us what field was changed

It goes without saying that we never want to read SQL directly in the Acumatica world so let’s take a look at what we get when reading the AuditHistory records from the data access layer.

I created a button on the Customer Maintenance screen for quick debugging to show you all the extras that you get.

public static List<AuditHistory> FindScreenRecordsTriggeredbyDateFrom(PXGraph graph)
{
    var distinctScreenID = "AR303000";
           
    // change date as needed - here for debugging only
    DateTime? lastDateTimeFromParameter = Convert.ToDateTime("05/15/2022 17:00:00");

    var historyRecords = SelectFrom<AuditHistory>
        .Where<AuditHistory.changeDate.IsGreater<@P.AsDateTime>
        .And<AuditHistory.screenID.IsEqual<@P.AsString>>>
        .OrderBy<AuditHistory.changeDate.Desc>
        .View.Select(graph, lastDateTimeFromParameter, distinctScreenID).RowCast<AuditHistory>().ToList();

    return historyRecords;
}

When I debug this line of code and examine the HistoryRecords collection I can see that we get the same record count that we saw in SQL.  Let’s break down what we see.

Under the CombinedKey for this record, we have the BACCOUNT.ACCTCD value pointing us to the customer that was changed.

On the ModifiedFields field, we have a key/value pair separated by a “\0”.   We can parse this data into a dictionary giving us what field was changed and what value it was changed to.  Notice we didn’t see this by just doing a SQL query of the table.  In your code, expect that this might contain more than one value pair as you will see later.

For address changes, we now have an example of more than one key-value pair on the ModifiedFields field.  We didn’t directly change the RevisionID but the business logic on the screen did, so keep that in mind if you only want to track very specific fields, you’ll have to filter out what you don’t need.

The CombinedKey value for an Address change will contain a string value representing the AddressID int, which you can prove out by doing a quick query.  The lesson here is that the CombinedKey won’t always contain the ACCTCD value, it might carry the record ID, so you’ll need to convert it from a string to an int value in some cases.

Even more interesting things show up for Attribute audits.

Here our key-value pair in the CombinedKey field represents a NoteID GUID pointing to the BACCOUNT by NoteID where the change occurred.

The NoteID value is separated by the “\0” showing which Attribute key was changed, which in this case was “INDUSTRY”.  Since the TableName is CSANSWERS, it could contain any attribute that had changed.

The ModifiedFields fields for attributes will always have “Value” parsed by the new value which is BNK for Banking.

This query shows the match-up for what customer was changed.

Other Insights

In our first section, I pointed out that more than one customer was triggered as changed when I only made a change to ABCHOLDING.  This is because of the nature of child accounts for customers.  ABCVENTURES and ABCSTUIDOS are child accounts for ABCHOLDING and under Acumatica’s business logic and setup they are forced to share the credit limit information so changing one triggered the same change for the other two.  It’s important to expect oddities like this to occur so you’ll need to do a lot of defensive programming and testing to accommodate your solution goals.

I hope this BLOG is helpful to you in providing some basic information that you might need when designing a programmatic solution that requires you to pull Acumatica audit information.

Happy Coding!

Leave a Reply