Skip to main content
Question

Historic Log - Field / Record Modifications by User

  • March 9, 2018
  • 0 replies
  • 441 views

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 to a record. I need to show the user who made a change and what fields were changed on that record with date & time stamp at the minimum. It also needs to allow simple reporting.

Our basic app layout

Customer Table (Primary) - Support Task Table (Child)

Customer Form (Cust Record) - Support Task Form (Task Record)


Each customer record is assigned an Account Manager (User)

Task records share a related field with customers for Account Manager to show whos assigned to that account.

Support Task Records have a 2nd assigned agent, Fulfillment Assistant (User) not present in the customer table.


Looking for something like the below per record. Similar to the last modified by but logging the complete trail and specific fields changed , suggestions?

Example 1:

Task # 123456

 ----

(Field Name) Modified by John Doe 3/6/2018 11:18 AM (PST)

(Field Name) Modified by Christian Stamper 3/7/2018 9:10 AM (PST)

(Field Name) Modified by Christian Stamper 3/7/2018 9:18 AM (PST)

    This topic has been closed for replies.

    How many different fields do you need to log?  There is a no code solution with Actions, but you are limited to 10 firing at once and I think 10 Actions per table. 
    • JoshuaTate
    • Quickbase Alumni
    • March 9, 2018
    There is a code solution if you search the forum - IOL javascript by Dan
    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
    OK, new day and new energy ....

    The Action looks pretty good but you will also need to log the name of he table being logged.  For example "Orders".


    The main direction that you want to access the logs from is from the Record being logged, I will pretend it is the Orders table where you want easy access to the Logs of the changes. 

    The Low Tech way is to make a Report Link field.  A Report Link field just runs  a report while sitting on a form and adds an extra filter to the report where a value in a specific field on the Main record matches the the value in a specific field on the target table.

    So on the orders table make a field called link Link to Logs with a formula of "Orders-" & [Record ID#].

    On the Log file make a field with the formula

    & "-" & [Record ID# of table being Logged]

    so for example they would both calculate to Orders-123 for the record ID# 123 on the orders table.

    Then put that Report Link field on the Order record form.  You can choose to actually list the log records as an embedded report on the Orders form or just have a link and the user can choose to see the audit logs if they wish. 


    .....................

    Now if you do want to link in the opposite direction, then you will need a formula like this as a formula Rich text field

    var text Words = ToText([Record ID of Changed Record]);
      var text DBID = Case(
    ,
    "CO", [_DBID_CONTRACTS],
    "CCI",[_DBID_CUSTOMER_CONTRACT_ITEMS],
    "ICI",  [_DBID_CONTRACT_ITEMS],
    "PCE",[_DBID_PROJECT_PHASE_COST_ESTIMATES],
    "CI",   [_DBID_COST_ITEMS],
    "T",    [_DBID_LABOR_TAKEOFFS],
    "P",     [_DBID_PROJECTS],
    "CIE",  [_DBID_CONTRACT_INCLUSIONS___EXCLUSIONS],
    "CAL", [_DBID_CONTRACT_APPROVAL_LOG]);
    var text URL = URLRoot() & "db/" & $DBID & "?a=dr&rid=" &ToText([Record ID of Changed Record]);
    "<a href=" & $URL & ">" & $Words & "</a>" 
    Terms & ConditionsAccessibility statement