Logging edits to a record

  • 1
  • 1
  • Question
  • Updated 1 year ago
  • Answered

Is it possible to set up a multi line text field that can log every edit made to a record with the field that was touched, user who did it and a date stamp?

Photo of Krishna

Krishna

  • 10 Points

Posted 5 years ago

  • 1
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Yes this is possible. An append or prepend text field (QuickBase calls this property "Log entries" now) accumulates a short log of changes to itself. However, when used in conjunction with the image onload technique you can gather those fields that have been changed along with the old and new values and stamp this information into the append or prepend text field. In other words, the append or prepend text field would never receive human keystrokes - rather the script injected into the page will monitor for changes to field values and assemble a string of text reporting these changes and automatically insert the generated text into the append or prepend text field. When the form is ultimately saved the append or prepend text field will be holding a miniature report of changes to all fields being logged and QuickBase will stamp the date/time and user using the native operation of the append or prepend field.. This may seem fantastic that you can do this but the edit form actually contains both the new values of the fields and the old values of the fields in hidden form elements.

I will come up with an example shortly - I have to finish watching my morning cartoons now.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
I don't care about the stupid points. Asking a new question just helps focus someone tagging on a new question to an old post. But don't even bother since there is a demo I created that does this and I should have included it in my reply. I just have to find it ...

hang on
Photo of Joshua Tate

Joshua Tate

  • 1,016 Points 1k badge 2x thumb
:D - sent you an email but all good how ever we go lol
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,448 Points 50k badge 2x thumb
I know that there is a third party add on which does this.  It basically received the Notification emails when a record was changed and parses out the changes  and records them to a QuickBase database.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
That is a ridiculous solution. Why not retarget a spy satellite and then call the QuickBase API when the changes are beamed back to HQ. This feature can be directly implemented in a QuickBase form.
Photo of Krishna

Krishna

  • 10 Points
Thanks. Curious about the image onload technique, will wait for the example.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Here is your example:

Come Mr Tally Man Tally Me Bananna
https://haversineconsulting.quickbase.com/db/bi5q4vzj9



Edit Record 1 and Log Everything Like the NSA Does
https://haversineconsulting.quickbase.com/db/bi5q4vzmc?a=er&rid=1

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=305

Notes:

Back to watching cartoons ...
Photo of Samuel Trachy

Samuel Trachy

  • 82 Points 75 badge 2x thumb
Hey Dan, 

First of all, Big Fan! Long time listener, first time caller:  

Looks as though the "Edit Record 1 and Log Everything LIke The NSA Does" link no longer works.  I'm trying to implement this solution as well and I'm running into some issues and I'm thinking I may just not be seeing the whole picture.  Help?

thnx - Sam Trachy
Photo of Carlos

Carlos

  • 796 Points 500 badge 2x thumb
kind of a newbie here... where is the code inserted? and how would you log entries for a database that has 350 fields (and growing). I could really use this as an audit trail for my records!!

Great answer by the way!
Photo of Rahul

Rahul

  • 0 Points
Hi  Dandiebolt,
Thanks for providing such a wonderful information above and prompt response !!

I checked out link provided by you https://haversineconsultingr.quickbase.com/db/bgm2m4g?a=dr&rid=305
and found java script code for on change action which logs the old and new values of input field in log type field.

In Home settings-->Pages--> I created test.js page and writen the similar code but its not working.
Do i need to add any js or html library in order to execute the js code ??

Please Dan provide your valuable suggestion to me so that i can execute that script properly.

Thanks in advance !!!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
I am in conference mode and unlikely to be able to reply until next week.
Photo of Rahul

Rahul

  • 0 Points
Thanks Dan ...not a problem .. Please take your time ..and i will be waiting for your response. :)
Photo of BHW

BHW

  • 0 Points
Can you please explain how this is done step by step. Thanks
Photo of BHW

BHW

  • 0 Points
Can you please explain how this is done step by step. Thanks
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,208 Points 4k badge 2x thumb
You may also want to talk to Trinity Integrated Solutions (trinityis.com) about their Log Tracker; which would probably be more useful as a reporting tool.
Photo of Rahul

Rahul

  • 0 Points
Hi Dan.. I hope you are doing good !!
Could you please elaborate about the above script. and  how it will get trigger and what are the modifications required in order to use it in my application.

Eagerly waiting for your response :)

Regards,
Rahul
Photo of Tara

Tara

  • 10 Points
Hi, Dan! I'd like to +1 Rahul's request above. This is my first attempt with the image onload technique. I tried it several different ways over the weekend and was not able to get the log field to work at all. I've got to be missing something. If there's any way you could provide a step by step on how to use the image onload technique specifically to create this log field it would be greatly appreciated. I can think of numerous instances where having this log in my records would have been a great help! Thanks in advance for your help!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
The image onload technique is described at length in a step by step manner here:

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=294
Photo of Tara

Tara

  • 10 Points
Thanks, Dan! That is helpful and much more thorough then the instructional I found. I do have a couple of questions. When it says to create a user defined variable where do I do this at? Also, I see where I can add a page. It gives me three options...1. Home Page, 2. Rich Text Page, 3. HTML/Code Page. Is this the right way to create a user defined page or is that something entirely different? I have tried searching for more info on this but did not find an answer. I appreciate your help and apologize if this information already exists somewhere and I'm not seeing it.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
User defined variables are created from the Application Settings page (look for "Variables") and user defined pages are created from the same Applicaiton Settings page (look for "Pages" and select code page):

https://<subdomain>.quickbase.com/db/<dbid>?a=AppSettingsHome

Application variables act as text fields that can be used in a formula in *any* table in the application.
Photo of Tara

Tara

  • 10 Points
Ok. Thanks! When I put the following code into my image onload field [-]:

[iol] & "module.js & [/iol]

I get the following error:

Formula syntax error

Expected a valid expression after the "&"

[iol] & "module.js & [/iol]

More on syntax errors...

Any idea on what I'm doing wrong? Thanks for your help!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
you are missing the second double quote:

[iol] & "module.js" & [/iol]
Photo of Tara

Tara

  • 10 Points
Yes that was it. Thanks you! One more question. Does the script you provided for logging changes in the example above (https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=305) replace the generic image onload code (https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=293) or do they both have to be in the user defined page module.js?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Yes - I typically just post the essential code that would go in module.js. You may have to make additional modifications depending on your specific needs. Note that this fragment of code does not contain the generic wrapper that decodes what page you are on and places all the code within a closure so that your JavaScript variables will not conflict with QuickBase's globals (they sure have a lot of globals!). See

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=293
Photo of Tara

Tara

  • 10 Points
Thanks! I'm not very knowledgeable in javascript so I apologize for my repeated questions. I still can't seem to get it to work. Below is the code that I have on my module.js page. If you could provide any guidance on where I'm going wrong I would greatly appreciate it!!!

(function(){
  var querystring=document.location.search;

  if (/dlta=mog/i.test(querystring)) {

$("#_fid_341").attr("readonly", "readonly");

var label, oldValue, newValue, log;

$("#_fid_339, #_fid_328, #_fid_203, #_fid_103, #_fid_318", #_fid_27, #_fid_170, #_fid_28).on("change", function() {

  log = [];
  _.each([339, 328, 203, 103, 318, 27,170, 28], function(fid) {
    oldValue = $("[name=_fid_oval_" + fid + "]").val();
    newValue = $("#_fid_" + fid).val();
    if (oldValue != newValue) {
      label = $("label[for=_fid_" + fid + "]").text();
      log.push(label + " was =" + oldValue + "; now =" + newValue);
    }
  });
  $("#_fid_341").html(log.join("\n"));

})();
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
You are testing if you are on the grid edit page:

if (/dlta=mog/i.test(querystring)) {

when I think you intend to test if you are on an edit record page:

if(/a=er/i.test(querystring)) {

Note this logic is used in parallel with the form definition that sets whether or not the [-] field is included on the form in the first place. You may or may not need to include the if statement if you otherwise prevent the [-] field from being on the form/report.
Photo of gmcroberts

gmcroberts

  • 180 Points 100 badge 2x thumb
Hi Dan,

I have implemented this code and it works great as far as tracking the edits but it's not showing the field label.  All i get in the log is    was =10:00 am; now =11am     without the label before the word "was".   Do you know what may be causing this?  The code I am using is below, thanks!:

$("#_fid_122").attr("readonly", "readonly");

var label, oldValue, newValue, log;

$("#_fid_8, #_fid_9").on("change", function() {

  log = [];
  _.each([8, 9], function(fid) {
    oldValue = $("[name=_fid_oval_" + fid + "]").val();
    newValue = $("#_fid_" + fid).val();
    if (oldValue != newValue) {
      label = $("label[for=_fid_" + fid + "]").text();
      log.push(label + " was =" + oldValue + "; now =" + newValue);
    }
  });
  $("#_fid_122").html(log.join("\n"));

});
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
View source and search for the strings "_fid_8" or "_fid_9" which should be in the page. Post the HTML fragment that you find which includes the <input> and associated <label> tags. If the label is coming back empty but evertyhing else works there is probably just a slight modification to the selector needed on this statement:

label = $("label[for=_fid_" + fid + "]").text();

Please note that this solution would not be possible without the generous support of the Image Onload Foundation.