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 gmcroberts

gmcroberts

  • 180 Points 100 badge 2x thumb
THanks, this is what I find for "_fid_8":

<td id="tdl_19" class="label lc" >
<label class="fieldLabel " for='_fid_8'><b ></b></label>

     <div id="tdf_19" class="cell cc nowrap" ><input name=_fid_oval_8 type=hidden value='10:00 am'>
     <input type=text size=16 name='_fid_8' id='_fid_8' onBlur='OnBF(this)' onFocus='OnFF(this)' value='10:00 am' >
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
For some reason the HTML <label> generated by QuickBase for the label has no text. Since you only have two fids in your loop you could either remove the loop and inline all the statements or keep the loop and insert logic that will supply a hardcoded label value:

//label = $("label[for=_fid_" + fid + "]").text();
if (fid == 8) {
  label = "foo";
} else if (label == 9) {
   label ="bar";
} else {
   label = "missing!";
}
Photo of Tara

Tara

  • 10 Points
I want this to work so bad, but it's still not working for me. Here is a break down of what I"m doing. If anyone can help, I would REALLY appreciate it.

1.) I created a new text page called module.js with the code below

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

  if(/a=er/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"));

});

2.) I created two variables...

The first variable is named "iol" and contains the following:

<img qbu='module' src='/i/clear2x2.gif' onload="javascript:if(typeof QBU=='undefined'){QBU={};$.getScript(gReqAppDBID+'?a=dbpage&pagename=

The second variable is named "/iol" and contains the following:

&rand='+new Date().getTime())};">

3.) In my projects table, I have created a multi line text field called [Change Log]. The field id for this field is 341. Input type is "User Input". I have the box checked for "Allow some HTML tags to be inserted in the field". I have checked "Log the edits to this field, and show them on forms".

4.) In my projects table I have created a formula text field called [-]. The field id for this field is 377. I have checked the box to allow some HTML. The formula for this field is below:

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

Tara

  • 10 Points
I also should add that both the [Change Log] and [-] fields are on my form for the projects table. For the [-] field I have checked the box to use an alternate text label and inserted just a single space in the area where you are supposed to type the alternate text label.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
You are missing a set of parenthesis on the very last line of your code:

})();
Photo of Tara

Tara

  • 10 Points
Thank you! I added that, but it's still not logging edits to the records. Any other suggestions?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
You should just use straightforward debugging techniques. Put an alert("got this far") or console.log("got this far") in the code to insure you code is even running in the first place. Also expose the Console (press F12) to see if there are any JavaScript error messages.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
You have SEVERAL additional errors in your code - I apparently just reported the first one I saw. Corrected here:

http://pastie.org/9493321

You can minimize errors like this (everyone makes them including experienced developers) by using a variety of tools.

JSBeautifier will format you code:
JSBeautifier
http://jsbeautifier.org/

JSfiddle has a "linting" tool built into it which will identify syntax errors:

JSfiddle with JSHint
http://jsfiddle.net/rnp058hc/

The text editor Notepad++ has syntax highlighting and matching brackets highlighting which will help you find unbalanced brackets:

Notepad++
http://notepad-plus-plus.org/
There are thousands of similar tools available so pick those that you are comfortable with.
Photo of Tara

Tara

  • 10 Points
Yay! I got it to work! There is only one problem...it doesn't log edits to look up fields. I have a field [Assigned To] that is a drop down pulling it's options from a users table. When I change who the record is assigned to it doesn't log that. It is logging edits to all of the fields that are not look up fields though. Any suggestions on how I can get it to log edits to the look up fields? Thank you so much for your help. I really appreciate it!
Photo of Tara

Tara

  • 10 Points
Also, for some reason instead of each record edit being on it's own line, it's all running together like this...

-- [AUG-22-14  2:17 PM  Tara Haack] --------------Other Main Contact was =Jen Feller; now =Tara Haack Status was =Not Started; now =In Progress
Photo of Tara

Tara

  • 10 Points
I figured out the text running together issue and fixed it. It was because I had the box checked to allow some HTML for the text field that is the change log. I still have the issue with not being able to log edits to lookup fields though. Thanks!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
>I still have the issue with not being able to log edits to lookup fields though.
A lookup field can only change when the [Related Parent] field changes. To get the old and new values of the lookup filed you have to perform two AJAX requests to the parent table using (1) values of the [Related Parent] stored in the hidden input associated with the [Related Parent] <input> as seen here for fid=7:
<input name=_fid_oval_7 type=hidden value='1'>

and (2) values of the [Related Parent] associated with the selected option of the [Related Parent] <select> as seen here for fid=7:
<SELECT size=1 name='_fid_7' id='_fid_7' ...> <option value="" style="color:#888888;"> Make a Selection... </option> <option value="1" >Record #1 - foo - 123</option> <option value="2" selected>Record #2 - bar - 456</option> <option value="" style="color:gray;" disabled>--------</option> <option value="_other">&lt;Add a new record...&gt;</option> </SELECT>
The value of the lookup field from the AJAX call in step (1) will be the old value, while the value lookup field from the AJAX call in step (2) will be the new value. Note that even if a new parent record is selected it may be the case that the lookup field does not change value even though the related parent record does change - you may or may not want to note such a situation in your log.
Photo of Tara

Tara

  • 10 Points
Thank you! I will experiment with this.
Photo of John

John

  • 0 Points
Hello, Dan!
I have a little issue. The code works fine, but the test in the "Log" file automatically typing everything in one line. So, it is a little messy. In your example: https://haversineconsulting.quickbase.com/db/bi5q4vzmc?a=dr&r=b
- everything is clear and readable. How did you make the data typing in a new line?
Thank you!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
The array log is joined with a newline character to form a multiline string with the statement:

$("#_fid_11").html(log.join("\n"));

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

John

  • 0 Points
Dan, may you check the code:
(function(){
  var querystring=document.location.search;

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

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

var label, oldValue, newValue, log;

$("#_fid_242,#_fid_138").on("change", function() {

  log = [];
  _.each([242,138], 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_797").html(log.join("\n"));
      });
    }
})();


Thank you!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Inconsistent indentation. Fixed here:

http://pastie.org/9607867

What is not working?
Photo of John

John

  • 0 Points
Nop...Still typing in the same line...
May be you did change the properties for the [Log] field?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
[Log] is a multi-line text field.
Photo of John

John

  • 0 Points
You did it! Thank you!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Celebrate - run through your office singing the Banana Boat Song. Here are the lyrics:

http://www.azlyrics.com/lyrics/harrybelafonte/dayothebananaboatsong.html
Photo of John

John

  • 0 Points
Thank you, Dan!
Have a wonderful day too!
Photo of Jonny

Jonny

  • 0 Points
Hello, Dan!
Everything runs great! Only one problem: the [Log] field takes too much space, a lot of changes in the Project. Than more changes then more longer field. The question is how to organize it?
The ideal would be to see only a few latest changes and other just hide, something similar to messages like 1,2,3 pages... Or may be to create a separate table for tracking...
Thank you!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
> ...more changes then more longer field.

But this is the natural result of logging all the changes - it is going to take up more space if there are numerous changes. Beyond the general idea of using script to log changes to fields, you could track just the last n changes or perhaps just the changes made during the last 30 days. And where you store the changes is another dimension you can explore.

Also I should mention that the code I provided works for simple text and numeric fields. If you start logging checkboxes or lookup fields additional logic is needed. Date fields create another complexity. Date fields changed by direct user input  trigger a change event but dates entered through the popup calendar are made through JavaScript an do not generate a change event (you have to use periodic polling to detect date changes). These issues have come up (and have been solved) with some clients exploring other logging scenarios. This is the type of solution which will evolve as users try it out.
Photo of Jonny

Jonny

  • 0 Points
Ok. That`s interesting to store date just n-days. Would you please clarify how to do it.
For example, it would be meaningful to store the changes for 7 days.