cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KoenJanssensPD
Post Patron
Post Patron

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.

KoenJanssensPD
Post Patron
Post Patron

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
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (4,583)