Questions Generic inquiry with information from Audit history(CT301000)

Some of those DACs aren't actual tables - the trick unfortunately is going to be to workout which of the audit DACs exist in the database.

AuditFilter is most definitely not a table (any DAC with Filter in the name isn't usually a table) - it's used for specifying criteria on the page. If you can access the database you can see which Audit tables really exist. Otherwise do some test queries looking at the data in each Audit table (or getting an error if it's a non-DB DAC).

Thanks for the reply! I investigated a bit more and found out that the columns in Audit history are created dynamically(which is logical as different screens/tables have different fields). So I'll need to check a bit more on which tables might be involved in this specific scenario and see if I somehow manage to get out the information needed. If anyone has some ideas or tips on what tables to look for I would appreciate any help=)

Audit history is stored in AuditHistory table. Here's a simple query reflecting a change I made to vendor contact and address:

The problem is that all of the changes are recorded in ModifiedFields field, which has the following structure:

RevisionID0x00<Revision Number>0x00<FieldName>0x00<FieldValue>[<FieldName>0x00<FieldValue>]*

In other words, Revision Id and then one or more field name & value pairs. The delimiter used is unicode Null, or 0x00 (UTF-8 hex).

Here's an example string in JSON encoding:

"RevisionID\u00002\u0000Salutation\u0000Joe Blogs\u0000AssignDate\u000011/07/2014 00:34:05"

Decoded this would be: "RevisionID 2 Salutation Joe Blogs AssignDate 11/07/2014 00:34:05" - so you can see Salutation and AssignDate were updated, although I actually only modified Salutation. The system updated AssignDate. 

The problem is, I can't find any way to replace unicode null with a whitespace in GI. On the Audit History page, the code is obviously parsing this string to assign the dynamic columns in the lower table on that page. I cant find the source code for the PX.SM.AUAuditInquire so I can't see how Acumatica are doing this. Find in Files also doesn't match "modifiedfield". Even if this issue can be resolved, you're still left with a string of fieldname value pairs which you'd need to decide what to do with as it's not exactly end user friendly.

Seeing as Report Designer has the same functions as GI, don't think you'd have any luck with that either. 

Hi

Also, the AuditHistory table doesn't show any information if/when a row has been deleted. This was one main point to be able to show. If that would have been included I think we could have used the somewhat "messy" information in the ModifiedFields-field. Either way, in this case now we will just use the Audit history(SM205530) screen to view the information. Thank you very much for your help and investigation! 🙂 

When you said "doesn't show any information if/when a row has been deleted" I thought you meant that it doesn't Audit deletes, which it does. But the data from the deleted row isn't recorded in the Audit table, which is what I think you were after. 

You could try adding on to this idea:

https://feedback.acumatica.com/ideas/ACU-I-259

Unfortunately, AugSQL (click here) doesn't support MySQL at this time which MYOB runs on, but I was able to do the following in an Acumatica SaaS environment (which runs on Microsoft SQL Server) using AugSQL.

Now, I know that there could be pipes in the data so this isn't perfect, but the following delimits the fields with pipes. You could use something other than pipes or maybe use multiple characters like two pipes together.

I don't understand why I had to use "COLLATE Latin1_General_BIN", but I found that in a Google search somewhere and it seemed to work so I used it.

SELECT TOP 100 ModifiedFields,
REPLACE(ModifiedFields COLLATE Latin1_General_BIN,NCHAR(0) COLLATE Latin1_General_BIN,'|') 'ModifiedFieldsWithPipe'
FROM AuditHistory
WHERE CompanyID=2

I'm using this technique to create a SQL View that is the AuditHistory table, but with the new version of my ModifiedFields column that has pipes for the delimiter. Then I'll be able to work with it in Generic Inquiries.

CREATE VIEW dbo.MyViewsAuditHistoryWithPipes AS
SELECT CompanyID,BatchID,ChangeID,ScreenID,UserID,ChangeDate,
Operation,TableName,CombinedKey,
REPLACE(ModifiedFields COLLATE Latin1_General_BIN,NCHAR(0) COLLATE Latin1_General_BIN,'|') 'ModifiedFields'
FROM AuditHistory