Best method for a time stamp field with a checkbox?

  • 1
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I'm working on an approval workflow for my app, and wondering if this is the best formula to capture a timestamp for approvals? I want a formula that is not based on the form rules.

I know the now() function updates to the current time, not the time the checkbox was selected/changed in the record. Thanks!


If([Ready for Upload?] = not null, ToDate([Date Modified]))
Photo of Ryan

Ryan

  • 0 Points

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
The best formula will be one where all changes to a status field are done in a field with "Log Changes enabled.  Then you have three formula fields to parse out the components for the log field.



This will parse out the date and time for use in a date/time formula field.

var text DateAndTime = Left(Right([append only with date /time],"["),18);


var date TheDate = ToDate(Left($DateAndTime," "));
var timeofday TheTime = ToTimeOfDay(NotLeft($DateAndTime,9));


ToTimestamp ($TheDate, $TheTime)


This  formula will parse out the actual update, such as changing a status to "Approved"

Trim(Right([my update field]),"]"))




This formula will parse out who did the approval

Trim(NotLeft(Left(Right([my update field],"["),"]"),18)




You will need to be sure that the Log Changes field is set to log the time as well as the date and that the new entries are at the bottom.
Photo of Ryan

Ryan

  • 0 Points
Thanks, Mark!

Just so I understand, I would have a field that I want to track ("my update field") with logging enabled, and then per your recommendation I would have 3 formula fields to summarize information on the [my update field]: Date & Time, What the update was (e.g., "approved") and Who did it. Is this correct?

And would log changes be active for each of these 3 formula fields too?

Thanks again!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
yes, the three formula fields would extract out the separate values for the three components using a formula.  Then you still have access to a clean field for the Approval Status.
Photo of Ryan

Ryan

  • 0 Points
Perfect--and do you see any issues if I duplicate this for multiple steps in the approval process?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
whoa .....
 you mean to track like the last 3 approvals???? That is a much tougher formula. If you needed that data, then i would take a whole different approach with a child tale of status changes.  This is getting more complicated.
Photo of Ryan

Ryan

  • 0 Points
No, my lack of clarity. There are three different approvers in the sequence of each transaction. I assume that three fields for each approver (entry, manager, director) would suffice.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
ah, sure, three log fields each have as many as three fields each to parse out the components.
Photo of Ryan

Ryan

  • 0 Points
One last help question; i'm using this URL Formula to create an approval button, but it's giving me a semi-colon syntax error. Can you see where I'm off? (I'm using your URL Basics app, BTW).

var text URLONE = URLRoot() & "db/" & Dbid () & "?act=API_EditRecord&rid=" & [Record ID#] & "&_fid_17="Approved";
var text URLTWO = URLRoot() & "db/" & Dbid () & "?a=dr&rid=" & [Record ID#];

$URLONE
& "&rdr=" & URLEncode($URLTWO)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
var text URLONE = URLRoot() & "db/" & Dbid () & "?act=API_EditRecord&rid=" & [Record ID#] & "&_fid_17=Approved";
var text URLTWO = URLRoot() & "db/" & Dbid () & "?a=dr&rid=" & [Record ID#];

$URLONE
& "&rdr=" & URLEncode($URLTWO)



There was an extra quote before Approved.
Photo of Ryan

Ryan

  • 0 Points
Thanks!
Photo of Ryan

Ryan

  • 0 Points
I'm thinking an approval table would be best to set up, as a child table to my data. How would I set up that URL button to update a different table?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
Photo of Ryan

Ryan

  • 0 Points
Awesome, thanks!
Photo of Jan-Willem

Jan-Willem

  • 560 Points 500 badge 2x thumb

Hi, I think this is the sollutin I'm looking for in my project. Though being relative new and unexperrenced I don't get it working.

Would there by any change be as example databse project where this is used so I can analyse that example, learn it and than copy it into my own project?


Thanks



PS
I'm getting an error on this piece of code in the first VAR declaratin "[append only with date /time]". Not sure with what to replace this section
(Edited)
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
The application in the exchange called
Magic Buttons - Having fun with formula URLs
has a lot of examples on how to change status fields and add child records.

I also detailed out some options for making this status change time stamp here 
https://community.quickbase.com/quickbase/topics/how-do-you-setup-a-date-time-snapshot-based-on-a-st...

Take a look at a few of those and let us know if you get stuck.