Tracking record history

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Tracking record history

Post by graham » 2021-05-04 15:27

Hi, I've had a request to create the ability to log changes so we can see a record of mods - for example a record was changed on 02/04/21 by Fred, again on 09/04/21 by Jill and so on. It would also be nice to be able to somehow show the actual changes - so when a field has been changed, the old vs new is available and the date this took place and who made the modification. Could anyone provide some initial thoughts on how I might go about this? (I don't need to be able to re-create data for a given date - that would be a different requirement.) Thanks. Any thoughts appreciated.

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: Tracking record history

Post by graham » 2021-05-04 15:48

I found this at https://dev.to/zhiyueyi/design-a-table- ... abase-10fn. The database I need it for doesn't hold many records and gets fairly light use, but it would be good to have a solid solution for further use on a database with a larger number of records.

3. Use an Audit Table
A better solution is to create an audit table to record every single change in every field, which saves the spaces by eliminating redundant information. The table looks like this:

Audit

Column Name Data Type
Id int
Table varchar(50)
Field varchar(50)
RecordId int
OldValue varchar(255)
NewValue varchar(255)
AddBy int
AddDate date
For example, there is a transaction looks like this:

Id Description TransactionDate DeliveryDate Status
100 A short text 2019-09-15 2019-09-28 Shipping
And now, another user with id 20 modifies the description to A not long text and DeliveryDate to 2019-10-01

Id Description TransactionDate DeliveryDate Status
100 A not long text 2019-09-15 2019-10-01 Shipping
Hence, there are 3 fields changed, Description, ModBy and ModDate. Respectively, there should be 3 records added into the audit table as shown below.

Id Table Field RecordId OldValue NewValue AddBy AddDate
1 Transaction Description 100 A short text A not long text 20 2019-09-17
2 Transaction DeliveryDate 100 2019-09-28 2019-10-01 20 2019-09-17
Lastly, update the original record in Transaction table into

Id Description TransactionDate DeliveryDate Status
100 A not long text 2019-09-15 2019-10-01 Shipping
In that case, it's very easy to query for field changes. For example, if I want to find out all the description changes for the transaction with ID 100, I can just query by

Select * from Audit where RecordId = 100 and Field = "description".

[Also part of the text - not my comments] ... The downside of this approach is the possible huge increase of records. Since every change in different fields is one record in the Audit table, it may grow drastically fast such as tens of changes resulting in hundreds of audit records. In this case, table indexing plays a vital role for enhancing the querying performance.

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Tracking record history

Post by pbottcher » 2021-05-05 05:58

Hi,
if you search the forum, there is a long thread about audit-log. Which is a very nice feature. As you stated already, note that using an audit-log may create a huge datavolume.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

graham
Veteran Member
Posts: 84
Joined: 2020-09-29 12:30

Re: Tracking record history

Post by graham » 2021-05-05 17:36

Thank you pböttcher. I had already searched the forum but hadn't seen the thread. So I've taken a quick look and can see 'audit-log' is the search term to use! So I will look closer. Thanks.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: Tracking record history

Post by onoehring » 2021-05-06 08:42

Hi,

for Audit-Log, you can see my footer.
Concerning size of the log:
records/rows: 265,865
data size: 41,484,288
index size: 21,544,960
data free: 4,194,304

No speed problems (except, if you create a child-link and show record changes in a child register).

For audit-log there is a plugin now as well which takes care of all installation! See my footer for autit-log

Olaf

Post Reply