Good afternoon
We have a requirement to use a change history to create reporting for one of our business units. Being that the sys_audit hosts all (or almost) all records, it would be the best option to capture the data that is needed.
Our business partners need to see all changes to any field on a custom table. We have been utilizing sys_history_line to capture the data, yet this proves to be an improper way of doing this. We have found that it does not hold historical data, nor returns all of the data of a period yet, will return the data we're looking for.
With the sys_history_line, it shows the display values vs the sys_id or choice values from the sys_audit. This is the ideal output for the business partners that cannot translate values they cannot see.
I have attempted to create a database view using the sys_audit table and joining many other tables to try to replicate the info without much success.
I can get records to return
Here I have the newvalue of the sys_audit tied to the sys_user (as well as the sys_choice_value)
Things that are an issue:
- I cannot tie both oldvalue and newvalue to the name field as it can only show 1 value. I have other fields also referencing sys_user and sys_user_group that I haven't gotten to show yet.
- It does not show 'email'
- It doesn't include the record checkpoint of 0 which would be the initial creation of the record
Looking for any insight or other possible solutions to get this data for our business partners and VP's for reporting.
