Best way to track record history

Like (0) Share

Report

Posted on 24 Jun 2021 11:51:07 by KoJaPD

595

Dear,

What would be the best way to keep track of the history of a record, and be able to show it to users (in model-driven and/or canvas apps) and in analyses...

For example, records get assigned back and forth between users, I want to know how many records where assigned to a specific user on a specific date...

I see multiple ways, but I don't know which would be suited best OOB:

- Audit Logs (imho not very useable for analysis and showing it to an end user?)

- Use a Many-To-Many table that holds "UserRecordAssignments" that holds the RecordID, UserID, BeginDate, EndDate... But that would mean I need to create such tables for each recordfield I want to keep track of...

- Use a custom LogTable that keeps track of all changes: RecordID, FieldName, OldValue, NewValue, TimeStamp... this "EAV" type does not really care about the type of the values and is therefore not very useable...

- ...

Thanks,

Koen

All responses (3)

Answers (1)

cchannon 4,702 Moderator on 24 Jun 2021 at 16:41:35

Like (0)

Report

Re: Best way to track record history

This is why OOB audit history tracks only the delta and only unstructured.
KoJaPD 595 on 24 Jun 2021 at 14:27:21

Like (0)

Report

Re: Best way to track record history

While discussing this topic with some colleagues, we started thinking about the OOB function in SQL server, where you can create "temporal" tables that create copies of "old" data in a "history" table... this creates a lot of overhead, since the whole record is copied over to the history table when only one field is changed...
We also have been looking at a "bitemporal" implementation, but that would bring us too far...

I wonder how "standard" (ERP) packages handle all these historical data...
Verified answer

cchannon 4,702 Moderator on 24 Jun 2021 at 12:06:09

Like (0)

Report
Re: Best way to track record history

Correct on all counts.

Audit history is a feature set in dire need of overhaul from DV; it hasn't gotten any love since 2016 or so when they first introduced 'archiving' logs.
Creating your own generic log can work, but by its very generic nature it is also not very readable and if you need to report histories against records in bulk using FK references instead of lookups will slow you down quite a bit.
And creating a specific, related history record is the easiest on users and has to be written all over again for every entity you want to log.
Other, less comprehensive options include a PCF on form (configurable so you don't need to re-code every time but only tracks client interactions - it would miss any server-side interactions) and a PCF on the generic audit form (lets you build a friendlier, more record-specific UI but would take a fair bit of planning and coding and it doesn't address issues with reporting in bulk).

Sadly this is one of those moments when there is no perfect option; you just need to decide what is most important (UX? reportability? dev efficiency?) and go with that.