Best way to track record history

(0) Share

Report

Posted on by KoJaPD

601

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

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Verified answer

cchannon 4,702 Moderator on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
KoJaPD 601 on at

Like (0)

Report
Copy link

Link copied!

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...

Was this reply helpful? Yes No
cchannon 4,702 Moderator on at

Like (0)

Report
Copy link

Link copied!

This is why OOB audit history tracks only the delta and only unstructured.

Was this reply helpful? Yes No