TeamDesk Knowledge Base & Support

   Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Column History Tracking
Allow administrators select which columns to monitor. All history entries will include the date, time, nature of the change, and who made the change.

If you would like to see this feature, please promote this idea and add your comments.
ID
54
Category
User Experience
Author

Slava Shinderov  Staff 
Date Created
12/21/2006 5:54:09 AM
Date Updated
5/21/2024 10:14:21 AM
Status
New Idea
Score
570
Promoted By
jeffb@irrg.netGuillermo Vazquez Billy Hodges
Dániel Varga [Natural Proteins]Robert P.Heather Clemons
John GordonAshif AsharaphMichael Clay
Jacques du Plessismarianne@farmtrace.co.zaSilke Bögelein
ProgrammerDean GuidryYvette Ynigo
Daniel Vazquez GerIvan Papishpeter forman
Patricio BustosBernie H Hester III - VPDave Ah Ching
Dale OliverPhilipp Matuschka (MMB)cooper collier
Rick CogleyChristof Sulzermartin oliver
Fred WestholmLocation LogicJan Schoon
lucas benitesMichael Ver DuinMartin Odendaal
jeremy nelsonShem SargentPhil Gilbert
Damon SavageRick CogleyMichael Rennert
Kevin Smithca@showtimegolf.comRyan Van Der Vorste
gerardo garciadavid sultanishilss
Kevin CookTom BinkowGii Systems
Anthony CarterDamar ChristopherMatan Barnea
Rainer HochkoepplerAlexander SepeRichard Ketley
Pierre CarbonnelleScott MillerSlava Shinderov  Staff 
Comments
Scott Miller 12/21/2006 10:30:16 AM
This is something i require. Example is a data field which is used to track whereabouts. An audit trail would be invaluable.
Alexander Sepe 1/8/2007 6:10:00 PM
Hello, I really need this feature to track specific columns. Please could you give me information related the progress of the development of the idea.

Thanks!!!
Alex.
Kirill Bondar  Staff  2/22/2007 8:38:52 AM
The history is a system-wide general purpose structure storing the table identifier, the column changed, the record identifier and old and new values.

Turning on the history tracking for the table should render additional read-only table and the relation to the tracked table to allow rendering the list changes in the detail section of the view form.

Generally every user should have own view on a history table, according to access rights given to his/her role.

First, the question: should the tracking be turned on/off per column or per table as a whole?

Second, it seems there are several concerns in implementation that are hard to resolve.

1. Ideally old and new values should be stored separately and presented to the user according his locale settings (dates, numbers etc). First problem is the format the data should be stored: since every column value stored as a record the type of value is indetermined and the value should be stored in a kind of universal format (as text, for example). Before displaying the text should be converted back to the type of the column and rendered according to user's settings. First, this rendering is hardly supported by TeamDesk's core. Second, this makes data manipulations impossible. So far simplest way to resolve it is to get rid of locale-avare rendering and simply store the text in a sort of "User A has changed the value of column B from old-value to new-value", where old-value and new-value are rendered according to locale settings of the user that made the change.

2. What should happen if column type changes? TeamDesk tries to convert existing record from old to new data type. Even if conversion may succeed on an existing data, it is not necessary it will succeed on a history data. Should not column history be simply cleared? As type changes are made mostly during initial application setup, and rarely in a middle of application lifetime, it should be a big deal-breaker.

3. User had no access to the column. He was not aware of the changes made to the column's data. Later, the administrator gave his role column access. In theory the history of changes till certain date should not be displayed to the user. The solution might be to store the snapshot of column's access rights per role along with history record.

4. User had no access to the record. He was not aware of the changes made to the record. Either administrator changed access settings or record's data was changed to appear in user view (in case access was restricted by formula). The history should not be displayed to the user up to the time the record appears in his view. As formula controlled access may render different views per user (not per role), storing the snaphot of access rights may be resource consuming.
david sultani 7/15/2008 2:05:52 AM
The applications should create a log table automaticaly, with restricted access rights to view, keeping the records of, user name, time stamp, table name, coloumn name, type (delete,modify,add). The log will provide a great facility to find out who did what in the application. Only last modified user can be tracked currently. However in a serious application you need to know all the changes done by users. With multipule access rights to columns it is not possible to trace who did the change after few changes on the record.
Slava Shinderov  Staff  4/15/2010 8:39:58 AM
Merged with:
214 - System Log
Rick Cogley 7/11/2010 5:04:04 PM
Having a detailed log, also available via RSS feed, would be very useful especially when trying to track down errors and to guide further training on the system.
Philipp Matuschka (MMB) 1/10/2012 11:17:17 AM
I would like to see this implemented. I think it makes most sense just to do this at a record level. When any or several fields in a record are changed, write the previous value of the whole record to the same table with a "History flag". That resolves many of the questions raised above. It also means that an authorised viewer could press say a little + sign beside a record in a view and see it's history with the layout of that view.
gerardo garcia 2/10/2012 4:10:59 PM
Kind of related to this, an idea is that email notifications of modified registers could show the original (previous) and modified (current) value of the column marked with the orange background.
Shem Sargent 7/19/2012 10:13:20 AM
Could this simply be implemented without history snapshots? If a user has access to the column, they could have access to the full history of changes for the column. A conversion of field contents to text based on user's locale would be sufficient in the majority of cases that I can think of. However, the time stamp should be rendered according to the viewer's locale.
Location Logic 8/18/2012 1:52:32 AM
It would be very useful to be able to add columns that would show the audit log details of a particular record so that users could see who changed what and when in addition to just having last modified by, date modified, created by and creation dates.
Slava Shinderov  Staff  8/18/2012 2:19:10 AM
Merged with:
547 - Expose Audit Log To User Environment
Jan Schoon 9/24/2012 10:16:12 AM
Is it possible to receive an update on this idea?

Thanks
JP
Fred Westholm 2/4/2013 12:29:05 AM
Under Hipaa rules for Medical Practices All users of a Application have to be identified as to login and logout plus any entries they make and changes that they make. a company named Caspio.com has this to go with their database application it is an extra charge per month. Maybe that would be a way to admin it to the medical applications that you have on team desk. I know I would gladly pay for it.
Dean Eberly 2/12/2013 3:08:38 AM
One way to create a log of data changes is by using workflow triggers. First create a separate table with at least one text column to store audit data. In the table that you are tracking, create a new record change trigger to occur when the value changes in any column. Add an action with type "new record create", and list the audit table beside "create record in". Create a new assignment, use the list function in the From side to create a text string containing all the columns (record id, user, date modified,...) that need tracking. In the To side, choose a text column from the audit table. This will create a new line in the audit table each time a user creates or edits in the monitored table. Triggers can be created to be more specific, such as when record is deleted, insert "record deleted" in the list function. Change the properties of the audit table to disable creating, modifying, and delete. Also viewing can be restricted based on role.
Slava Shinderov  Staff  6/19/2014 4:24:05 AM
We've added new placeholder %RecChanges% which will insert a list of the record changes in the notification.
Rick Cogley 6/19/2014 6:28:07 PM
wow, nice! just tried it and it works perfectly.

cooper collier  11/18/2014 3:06:47 PM
I am promoting this idea as well...

The suggestion by Dean Eberly a year ago, is an excellent suggestion. But it is cumbersome.
-If you have a table with a large number of columns the List command gets complicated,
-you have to remember to change the rule any time you change the data base
-if you want to record which field is actually changed. you have to make a rule for every single column.

We are implementing this on our tables to become hippa compliant. But this is one ugly way to do it..

Ideally there would be two things!

1. a single audit table that allows administrators to pull reports. Filter by table, user, date, column changed, etc.

2. a rule that can be applied on save, that will extract the changed data only. This way a column can be made that lists the latest updates to the record. Similar to the %rechanges%, Slava made for notifications above.. but a more granular.


3. A BONUS: record the modification date per column, maybe make is so is you hover over the corner of the column box, it will display last updated and by whom??

Slava Shinderov  Staff  11/21/2014 6:48:15 AM
@All This sample database shows possible scenarios of implementing column history tracking:
http://www.teamdesk.net/tracking_column_history.html
Rebecca Sell 1/3/2018 7:50:48 AM
This came up for us when a member of staff was adamant that other people were changing her client records. What we have now is a copy of the CLIENT table and any change to a CLIENT record creates a copy of that record as at that moment. In effect, keeping a history of changes.

This works really well!

When the table gets too big we'll just clear it down from a certain date backwards.

Rebecca
Dean Guidry 2/20/2018 9:46:09 AM
When I used Trackvia, the history of a record was an essential element of the database functionality. This needs to be implemented at the record level asap. I was shocked to find no documentation on this at all.
Programmer 5/19/2020 1:56:11 PM
This would be extremely useful, we don't need other tables for history. It would be nice if the history was hidden on main form and you click view history icon next to the edit icon for the line item. It will be very clean looking form. Additionally, have an audit log to see all changes made and be able to query it
Dean Guidry 7/28/2020 8:40:09 AM
Any movement in the direction of tracking record history?
Jacques du Plessis 7/21/2021 2:02:09 AM
I think a great place to start is to have the data that is currently shown in record change email notifications, stored in a related table or even a Text multiline column (with append only setting) in same record.

This will use the same %RecChanges% placeholder.

We could setup a workflow action to assign the %RecChanges% to a text field or XHTML field.





Philipp Matuschka (MMB) 11/3/2021 10:51:40 AM
I agree with Jacques's comment
Michael Clay 11/3/2021 12:04:15 PM
We use the email notification feature to send an email anytime any field is added, modified, deleted. We created an email account to hold only those notifications. With the right codes in the subject line you can filter by user, etc.
John Gordon 6/9/2022 9:14:31 AM
So many other tools make this audit trail really easy to implement. The user should be able to see the audit trail for the table as a readable text field.
John Gordon 5/20/2024 4:44:02 PM
Still really keen to see this feature appear. So much needs to have an audit trail today. Quality records should always have audit histiry.
Billy Hodges 5/20/2024 11:48:11 PM
I agree, John Gordon. This has been up for consideration since 2006, and with comments from as recent as 2020, 2021, 2022, and now 2024…it’s obvious the need is still very much real.

I hope implementation of some sort of column history tracking can be delivered soon. Even if it is not “perfect”, it would be great to at least see a starting point implemented…then it could be tweaked with use and input from users.
John Gordon 5/21/2024 1:58:38 AM
Billy, I agree. I am normally really really impressed with the Team Desk crew but on this I have been disappointed.
Audit trail for database entries and changes is a must in most modern tools. I have tried to construct my own option for this but with the built in options available it is not easy. It only needs to be a prepended text field with the changes (as per the email version instruction) plus date-time and username. The score on this has been very large for some time now. I am struggling for Team Desk adoption when there is no audit trail.
John Gordon 5/21/2024 1:58:55 AM
Billy, I agree. I am normally really really impressed with the Team Desk crew but on this I have been disappointed.
Audit trail for database entries and changes is a must in most modern tools. I have tried to construct my own option for this but with the built in options available it is not easy. It only needs to be a prepended text field with the changes (as per the email version instruction) plus date-time and username. The score on this has been very large for some time now. I am struggling for Team Desk adoption when there is no audit trail.
marianne@farmtrace.co.za 5/21/2024 3:21:21 AM
We have been using a multi-line text column to store changes. In this example it is [Audit log]
Below is a sample of the default calculation.
You will need to specify all the columns where the audit trail is important.

If(not IsNull([Date Created]),

Concat([Audit log],
"\n\n----------------Logged values for ",ToText(Now())," by ",ToText(User()),"------------------\nLast Modified By: ",ToText([Last Modified By]),
"\nDate Modified: ",ToText([Date Modified]),
"\nStaff First Name: ",[First Name],
"\nStaff Last Name: ",[Last Name],
"\nID Number: ",[ID Number]))

Hope this helps someone.
John Gordon 5/21/2024 4:01:06 AM
with multiple columns listed does it only report the changed fields and new value?
not sure it can report "old value" - "new value" which would be useful, but cannot see how to do this although there is an email function for this?
marianne@farmtrace.co.za 5/21/2024 5:11:19 AM
No, it reports all the columns listed.
The "Old value" will be stored when the record is saved. With each edit the new values will append to the Audit Log column.
Yes, there is an email function as well.


John Gordon 5/21/2024 7:08:14 AM
So, most of the solution is present. The two things that are needed to make this really useful

1 The same ability to capture old and new value on change as happens in the email option
2 The ability to capture audit for multiple fields at once but only capture those that changed.
Dean Guidry 5/21/2024 8:40:36 AM
I was a subscriber to TrackVia.com for years as a case management system for my law practice. Their column history tracking was phenomenal and essential to my practice. It’s probably malpractice not to have a history solution like that as I use Teamdesk, Implement asap please.
John Gordon 5/21/2024 10:14:21 AM
I use Podio for case management with a client because it has a good audit trail. Would be hard to use Team Desk in that instancebecuase of the difficulty in tracking changes.
Feedback
 
Back to Search Results