Historic Log - Field / Record Modifications by User

  • 2
  • 1
  • Question
  • Updated 8 months ago
  • In Progress

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)

Photo of Christian Stamper

Christian Stamper

  • 134 Points 100 badge 2x thumb

Posted 8 months ago

  • 2
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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. 
Photo of Christian Stamper

Christian Stamper

  • 134 Points 100 badge 2x thumb
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.
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
There is a code solution if you search the forum - IOL javascript by Dan
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
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"));
});
Photo of Christian Stamper

Christian Stamper

  • 134 Points 100 badge 2x thumb

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.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 48,898 Points 20k badge 2x thumb
.. or use my no code suggestion below ....
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
Hi Christian,

Unfortunately not, below is as basic as i can do. The no code suggestion from Mark is a good one for small qty of field logs - I understand Quickbase is bringing out audit logs shortly as well - you should be able to get in on the BETA.

Simple steps are:
1. setup IOL technique
2. add the code above to your module.js
3. add a text field with logging set as required
4. get the logging field id in the code above fid_152 is the field you replace with your fid.
5. Replace all of the below field IDs with the ones you wish to trigger record and update into your code page:
$("#_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]
Photo of Christian Stamper

Christian Stamper

  • 134 Points 100 badge 2x thumb

I'm soaking up the knowledge, I can see your suggestion being applicable to us as well. Especially if we need to track a larger # of fields later down the road. It's still much appreciated. Also, if you have details on that beta please share.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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
(Edited)
Photo of Christian Stamper

Christian Stamper

  • 134 Points 100 badge 2x thumb

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.



Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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.
Photo of Christian Stamper

Christian Stamper

  • 134 Points 100 badge 2x thumb

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

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
I will respond in the morning .....
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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

[Table being Logged] & "-" & [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([Table],
"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>"