Forum Discussion

ChristianStampe's avatar
ChristianStampe
Qrew Trainee
7 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 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)

17 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. 
    • ChristianStampe's avatar
      ChristianStampe
      Qrew Trainee
      10 fields should be more than enough for my need. I have two separate table forms or record types I need to track edits for, with only 6 - 8 fields in each record I'd need to track field modifications for.
  • There is a code solution if you search the forum - IOL javascript by Dan
    • ChristianStampe's avatar
      ChristianStampe
      Qrew Trainee

      Hello Joshua,

      Thank you for the suggestion, that is something I've been researching via the below forum post. In combination with Append or Prepend text fields. The challenge I had there is the walkthrough is specific to text fields. I also have Date and checkbox fields to contend with and would need to build this out for approx. 15 fields individually between my two table records. This is one possible solution I have yet to try, but wanted to research my options and see what other suggestions where out there.

      https://community.quickbase.com/quickbase/topics/logging-edits-to-a-record

    • JoshuaTate's avatar
      JoshuaTate
      Qrew Cadet
      Hi Christian,

      Below handles text and checkbox's - Numbers and dates will also record so long as a text box or checkbox is edited after - yet to work out how to resolve this. I have also added a script to capture and insert the IP address of the logged in user. please see below code:


      // Below sets up the IPPromise to be called later 
      var IPpromise = $.ajax({
      type: "GET",
      url: "https://api.ipify.org/?format=jsonp";,
      dataType: "jsonp"
      });   
      $.ajaxSetup({data: {apptoken: apptoken}});
      $("#_fid_6, #_fid_9, #_fid_64, #_fid_98, #_fid_150, #_fid_72, #_fid_114, #_fid_132, #_fid_77, #_fid_85, #_fid_7, #_fid_8, #_fid_11, #_fid_12, #_fid_13, #_fid_14, #_fid_15, #_fid_16, #_fid_17, #_fid_18, #_fid_19, #_fid_20, #_fid_21, #_fid_22, #_fid_23, #_fid_24, #_fid_25, #_fid_26, #_fid_27, #_fid_28, #_fid_29, #_fid_30, #_fid_31, #_fid_32, #_fid_33, #_fid_34, #_fid_35, #_fid_36, #_fid_37, #_fid_38, #_fid_39, #_fid_40, #_fid_41, #_fid_42, #_fid_43, #_fid_44, #_fid_45, #_fid_46, #_fid_47, #_fid_48, #_fid_49, #_fid_50, #_fid_51, #_fid_52, #_fid_53, #_fid_54, #_fid_55, #_fid_56, #_fid_57, #_fid_58, #_fid_59, #_fid_60, #_fid_61, #_fid_62, #_fid_63, #_fid_65, #_fid_66, #_fid_67, #_fid_68, #_fid_69, #_fid_70, #_fid_71, #_fid_91, #_fid_100, #_fid_102, #_fid_122, #_fid_127, #_fid_129, #_fid_133, #_fid_136, #_fid_139, #_fid_140, #_fid_147, #_fid_149, #_fid_153 , #_fid_154 , #_fid_155 , #_fid_156 , #_fid_157 , #_fid_158 , #_fid_159 , #_fid_160 , #_fid_161 , #_fid_162").on("change", function() {
        log = [];
        _.each([6, 9, 64, 98, 150, 72, 114, 132, 77, 85, 7, 8, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 65, 66, 67, 68, 69, 70, 71, 91, 100, 102, 122, 127, 129, 133, 136, 139, 140, 147, 149, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162], function(fid) {
          oldValue = $("[name=_fid_oval_" + fid + "]").val();
          newValue = $("#_fid_" + fid).val();
          newcheckValue = $("#_fid_" + fid).prop("checked");
          oldcheckValue = $("[name=_fid_oval_" + fid + "]").prop("checked");
          type = $("#_fid_" + fid).attr("type");    
      if (oldValue != newValue) {
            label = $("label[for=_fid_" + fid + "]").text();
            if (type == "checkbox" && oldcheckValue != newcheckValue) { // Push to log if checkbox AND values unequal
      log.push(label + " was =" + (oldcheckValue ? "true" : "false") + "; now =" + newcheckValue);
           } 
        else if (type == "checkbox" && oldcheckValue == newcheckValue) { // Do nothing if checkbox AND values equal
        } else { // Push to log if not a checkbox or date
              log.push(label + " was =" + oldValue + "; now =" + newValue);
           }
          }
        })
      // Below pushs the IP address from the promise above
      $.when(IPpromise).then(function(json) {
      var ip = json.ip;
      log.push("IP of User: " + ip);
      });
        $("#_fid_152").html(log.join("\n"));
      });
    • ChristianStampe's avatar
      ChristianStampe
      Qrew Trainee

      Hi again Joshua,

      So far your suggestion seems to meet my needs best based on its description. My use of coding in QB apps has been very limited however, I consider myself a novice in that area. Any chance there's a walkthrough example somewhere I can reference to get my bearings? Basic steps etc. The related forum discussions bounce all over and many of the links no longer work.

  • 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

      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
      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

      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

  • 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>"