Can't get field level auditing to work

Hi @Joe21,

What do you see in User SEcurity, Inquiries, Audit History when you select the Vendors screen at the top?

Choose table...

Hey @Naveen Boga  thanks for the tip!  Sorry if I am conflagrating here...I don’t have a technical issue with this (yet) but, FYI, the whole reason for this Audit stuff...The current audit screen and reporting is unsatisfactory to the customer.  For example, if you run any reports on the Audit History, it shows “Id’s”, not the actual values associated with those Id’s.  The auditor doing the analysis wants to know the actual customer number here, not the table Id of the customer.  The same goes with many other fields like Branch, Location etc.

I’m tasked with creating a more readable report from the audit tables.  I’m planning to do this with a SQL view where I can do my own joins to get useful info.

For example, this ID is useless to an Auditor:

If you try to make a GI against the AuditHistory table, there is only one table you can join to

I don’t see a way to use Acumatica out of the box to be able to create reporting from the AuditHistory table where you can join to other tables to get Vendor Numbers etc.  

Curious to know if you have ever done this before.  I’m thinking using SQL Views is probably the best approach.

OMG.  Sorry...the customer is using NUMBERS for the CD value.  My bad.  HA!  :-)

They still want a way to run an actual report, but there isn’t a report we can find to run which will present data the way the “Tools - Audit History” screen shows.

If you run a GI on the AuditHistory table, you get this

The changes are mashed into a single column.  They want this info parsed out into a useable format.

Also, just running the data out of the table doesn’t give you the “changes” like the on screen inquiry gives.  SO, this will be a complicated query.

@Naveen Boga  Do you think using a custom DAC that references a SQL View would be the best approach?  This would be the same process I used for creating a custom Data Provider for the export scenario you helped me with a month or two ago.  The custom DAC would pull from the SQL View and then it could be used on the GI screen.  I’d LOVE to get the code Acumatica uses to pull the data to the Audit History screen, but I don’t think they would give it to me.  I could use that to simply cycle through the changes and dump them to a report.  

I think I could also do this in C# instead of using a SQL View.  It would be pretty nasty to do, but it might be easier to do that and just export the results to a report rather than using a GI and SQL.  SQL is a bit harder to work with for looping etc.

Love to hear your opinion.

@cshaheen26 

The CombinedKey field is a “null” delimited field that uses whatever the Keys are for the respective table on the line.  I don’t think it is going to be possible for you to pull the key values out of that field without using C# coding.  It is possible to do it in TSQL and C# but I don’t think Acumatica has a feature to allow you to parse out a field based on a null delimiter in a field and break it into multiple columns for a join to another table.

I managed to do it but I had to use C# coding to do it.