Forum Discussion

ChristianStampe's avatar
ChristianStampe
Qrew Trainee
8 years ago

Historic Log - Field / Record Modifications by User

I'm trying to track edits / modifications by user, for multiple fields in a form or record. I cant seem to figure out how to create a basic audit log. That gives me a simple historic view of changes ...
App builders
Ask about Quickbase functionality
Forms
QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
8 years ago
OK, so a no code native solution

Make a child table of Audit Logs for the table you are tracking

Make fields for User (type User) date/time, old value, new value, and field name.

Then have an action that fires where the record is changed and [Field 1 changes]

The action will be to  add an audit log record and you will map the values or the old values into the various fields.  Be sure to map [Last modified by] into the User field  - that is who made the change.

Read up about Actions if you have not used them before. https://help.quickbase.com/user-assistance/creating_a_quickbase_action.html
  • ChristianStampe's avatar
    ChristianStampe
    Qrew Trainee
    8 years ago

    This is clicking a bit more for me. I can see where this is going with how it functions a little better.

    Quick question though, you say "create a child table of Audit Logs for the table you are tracking". I need to track from multiple tables, two currently with the possibility of a third we planned to add. Will I need to create more than one child table for audit logs. Or can I simply map or build relationships between my Audit Log table fields to fields in both my Customers table & fields in my Support tasks table?


    Customer (Parent Table) - = Support Tasks (Child Table)

    Customer - = Slow Pay Tasks (Child Table - Potential NEW table)

    ------

    Audit Log (Child Table)

    Fields: User, Date/Time, Old Value, New Value, Field Name (Mapped to Field in Table I'm tracking?)

    -----

    For some context here some examples of the fields and tables I need to track.

    Support Task Table - Fields: Start Date, Sent Date, Finished Date, On Hold (Checkbox), Cancelled (Text Drop Down)

    Customer Table - Fields: Agent Assigned (Text Dropdown), Renewal Date, Renewal Amount (Numeric), Contact Date, Contact Type (Radials / Checkbox)

    I think I will give this a try as it seems a little more simplistic before I resort to Joshua's method. Your time is greatly appreciated. Hope I explained this properly.



  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    OK, let me give you more detail as to how I implemented audit logging for a client on about 15! tables and many fields.

    I set up a single table for Audit Logs.  They are not actually in a relationship with the tables being logged as I did not want to set up 15 audit logging tables.

    I write the values for the table being logged and the field being logged into that Log table.  I also record the record ID# field of the record being logged.

    If you did want the actual audit logs to appear on the table being logged as an embedded report, you could do that via a report link field.  The report link cold be based on a calculated field on the table being logged such as 

    "Orders-" & [Record ID#]

    Then on the audit logging table you would create the same field by formula - the table that the entry was logged from eg "Orders" and the Record ID#

    Then you put the report link field on the form and show the records as a report on the form.
  • ChristianStampe's avatar
    ChristianStampe
    Qrew Trainee
    8 years ago

    I've already tested this and it appears to work for what I need so far. The only thing I'm struggling with is the "Then on the audit logging table you would create the same field by formula - the table that the entry was logged from eg "Orders" and the Record ID#"

    I might have something mixed up. Take a look below.

    Audit Table Screenshot - See Task # field. This is fed by an action from my Task table based on the Record ID in the Task table fed to a text field in the Audit Table. I would like to have a URL Button taking you to that Task # record from Audits to Tasks. Is that what you meant? And Vise Versa between tables.

    Formula not working -

    URLRoot() & "db/" & [_DBID_TABLE_2] &"?a=er&rid="&[Record ID]&"&dfid=2&tab"

    Task Table Screenshot - Report Link, as you can see I'm now showing the audit trail data I need in the record via report link field. However, I do not need the Task # formula button here.

    Action Under - Support Tasks Table

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    I will respond in the morning .....