Field Audit Trail Reporting

ihiggins

Hi all,

We are single-tenant M3, with FAT (field audit trail) enabled and a replica DB that we use for reporting.

I'm trying to write a report (using an external reporting tool) that interrogates the FAT data, allowing us to show who's changed certain fields in a summary format, rather than having to click F9 on individual records in M3 to check them.

I understand the FAT data is stored in a separate schema to M3 data, and I've found the FATJDTA table, but this doesn't appear to hold all the information I'm looking for (no "to" and "from" field data for example).

Can anyone point me in the right direction here?

Much appreciated,

Iain

Daniel Henningsver

The FAT table in FATJDTA will only hold the value before the change. If you want to se the value after the change you need to either look at the later change in the same FAT table or if no more changes has been done, in the M3 table.

There is a sequence number column in the FAT table that can be used.

So if you have a change in FAT table and you want to find the value after the change, you first need to check in the FAT table (with the same unique index) and a higher sequence number (if multiple changes has happened, you need to pick the one of those with the lowest sequence number). If no later changes can be found, you retrieve the value after the change from the actual M3 table.