How do you setup a date/time snapshot based on a status field

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

I have looked at different quickbase community forms but have not been able to figure out how to create a snapshot of the date and time. 

I have a form that is used for tracking escalations when contacted by the field. 

I want to be able to capture the date/time  one of our representative opens the escalation form (to capture the average time it takes from Create to First Response)

and when the escalation is closed out (To capture average time it takes to resolve an escalation)

The problem is the time keeps is based on the status of the case and can change depending on where they are with the resolution or for example providing additional details that were forgotten after the record has been closed out. 

Can you help with this?

Photo of Carol


  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
Official Response
Here are 2 ways to handle this: (There are others, but we'll start here)

I'm going to make a few assumptions as I don't know your process exactly, for example purposes lets assume you have a [Status Field] that has 3 status options. 1-New, 2-Process, 3-Complete.

Option 1

Create 3 status change date / time fields;
Status 1 Time Stamp
Status 2 Time Stamp
Status 3 Time Stamp 

Then you can set up a few 'dynamic form rules' that change those field to 'today's date and time' when the status field is changed.

If you wanted to know who changed the status, you could also add 3 "User" fields and have the those field be changed to the 'current user' based on the same status changes.

Note: this only works in edit mode, and will not work during grid edit.

Option 2 (My preferred option)

You will still need the Time Stamp  fields and the user field, and I usually add a checkbox for each of the milestones (ie. checkbox called "Completed") which makes reporting easier

Then for each status milestone I'd make a formula URL field.  These formula URL fields will all behave similar, but the values will changeI.

Call the field something like: [Mark Complete] with the link text to say "Mark Complete"

var text URL= URLRoot() & "db/" & [_DBID_<i>Your DB Name</i>] & "?a=API_EditRecord<br>&rid="&[Record ID#]<br>&"&apptoken=<i>yourapplicationtoken</i><br>&_fid_34=1<br>&_fid_35="&URLEncode(3-Complete)&"<br>&_fid_36="&URLEncode(Now())&"<br>&_fid_37="&URLEncode(User()) <br>&nbsp;; "javascript:" & "$.get('" & $URL & "',function(){" & "location.reload(true);" & "});" & "void(0);" <br><br>Make these changes to match your new fields;
34 -> the fid of your 'complete' checkbox
35 -> the fid of your status field
36 -> the fid of your Time Stamp field
37 -> the fid of your "Marked Complete By" user field
Don't forget to update the DBID name, and include your app token.

This might be a lot to do, especially if you have a lot of status changes, but will make the user experience much better.

Let me know if you get stuck, or need some help.