cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KoenJanssensPD
Resolver I
Resolver I

Best way to track record history

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

1 ACCEPTED SOLUTION

Accepted Solutions
cchannon
Super User
Super User

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.

View solution in original post

3 REPLIES 3
cchannon
Super User
Super User

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.

View solution in original post

KoenJanssensPD
Resolver I
Resolver I

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

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (1,505)