Forum Discussion

JasonPonce's avatar
JasonPonce
Qrew Trainee
11 months ago

Formula Field is Updating Outside of Record Interaction

Hi everyone,

I have a formula field (see code below) that gets the current date and time. Then formats it to a certain way. The issue I'm having is that this formula field updates the content of the field outside of Edit>Save Record operations. 

Ex.
1) If I go a table and view all the records. The formula field updates everytime I refresh the page.
2) If I go to edit a record, then discard any changes. The formula field updates.

PS. If there is a better way to format the date, please let me know!

//Intended Time Format 12/21/2021 17:00:19

//Get time
var number numHour = Hour(ToTimeOfDay(Now()));
var number numMin = Minute(ToTimeOfDay(Now()));
var number numSec = Second(ToTimeOfDay(Now()));
//convert to String
var text getHour = If(ToNumber($numHour) < 10, ToText("0"&$numHour), ToText($numHour));
var text getMin = If(ToNumber($numMin) < 10, ToText("0"&$numMin), ToText($numMin));
var text getSec = If(ToNumber($numSec) < 10, ToText("0"&$numSec), ToText($numSec));
var text getTime = $getHour & ":" & $getMin & ":" & $getSec;

//Get Date format
var text getDateFormat = SearchAndReplace(ToText(Today()),"-","/");
//Put it all together
var text setTime = ToFormattedText(Now(),"HHMMSS");

var text dateTimeFormat = $getDateFormat & " " & $getTime;
var text setDateTime = If([Enabled for Execution]=false, $dateTimeFormat);

$setDateTime


------------------------------
Jason Ponce
------------------------------

5 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    11 months ago

    When you use Now() in a formula it's always the current time.

    As they say, "Time waits for no man".

    What "Anchor Date" do you Want to uSe instead.  If you base the formula on the built in field [Date Created], then it would stable based on the date the record was first saved.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • joevandervest's avatar
      joevandervest
      Qrew Cadet
      11 months ago

      If it helps, when it's important to have a baseline date/time, etc. I'll create a pipeline based on the record create/update and write the date/time to a field that won't change when NOW() changes on each refresh or record load. It's a way of time stamping something like a "baseline".  they are usually quick and easy pipelines to do - just don't forget if you use the current system time in jinja you need to adjust it for timezone. 



      ------------------------------
      joe vandervest
      ------------------------------
    • JasonPonce's avatar
      JasonPonce
      Qrew Trainee
      11 months ago

      Hi Mark, 

      Thanks for the help!

      The intention is that the record being update has a [Enabled For Execution](On/Off) state. That a user can come in and turn off and on at their leisure. 

      What I'm trying to do is create a field that captures the last time someone goes into the record, unchecks [Enabled For Execution] and update the field with the timestamp when the user does this. We also use a text field with the log user entries as well. We also use a custom date time format Month/Day/Year Hour:Min:Sec (24hr).

      Date Created/Modified doesn't work for us. Since the user is not always creating the record. For Date Modified, we are using the QB RESTFul API. So the backend generally accounts for 99.9% of last modified. 

       

       



      ------------------------------
      Jason Ponce
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        11 months ago

        You can have a scalar (data entry) field.  Then use a Form Rule to populate that field when certain conditions are true or when the record is saved and certain conditions are true.  The "source" of the value in the form rule can be your formula field that you have now.



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------