adjust "now()" timing to capture truly current timestamp

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

I have a team creating time cards in quickbase. Right now I'm using "Now()" to populate the start time of the time card when they punch a button to clock in. The problem is that this appears to clock in at the time the page was last refreshed, not necessarily the current time. Is there a way to capture the current time when the button is clicked?

One thought would be for the button to refresh the page first when clicked, followed by creating the record with the now() function. ideally that would be seamless to the user so user would not have to click refresh and then the button. Not sure how to do that though.

Photo of mfitzg

mfitzg

  • 0 Points

Posted 5 years ago

  • 0
  • 1
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,516 Points 4k badge 2x thumb
This is because you're using a formula; which is dynamic. You need the formula to determine the Now() time; and then a separate field to transfer this value into when the record is saved. Create a new [Date/Time] field and write a form rule that when the record is saved, change [Date/Time] field to the value in the [Now Formula Field].

You may also need to write some rules around the fact that it should only populate once - if you have a Parent record related to the Time-Entry record; you might be able to better do this with a snapshot field instead. Create a Lookup [Date/Time] (now()) formula field into your time-card entry table; and snapshot this value when the record is saved. This snapshot value will never change unless you re-link the time card to another parent record.
Photo of Vilmondes

Vilmondes

  • 0 Points
Hello,

I need something similar. When a "Completed" checkbox is ticked, a Date/Time field is populated with the current timestamp. I created a form rule and it works, however, I have issues when working on grid edit view.

I don't have a parent record. What I have here is a report, then I click on "Grid Edit" and edit the records there.

Any suggestion?

Thanks in advance.
Here is a technique not using form rules.  Basically do a lookup of the now() value from another table and then snapshot it.

So new table called NOW and a field in the table with the formula date time of Now(). Create 1 record, it will be record id of 1. In the details table make a formula field of

If([checkbox]=true, 1)

 Make relationship where 1 now record is related to many detail records based in that formula field.  Then look up the Now() value calling it [Now lookup] and then make a snapshot of that now value lookup field.
Photo of Vilmondes

Vilmondes

  • 0 Points
Hi Mark,

I created a table called Now with a field called timestamp and created a record.

My details table have a field called "Date Completed" which makes the snapshot from the "Now - timestamp" field.

My question is: where am I going to add the formula "If([checkbox]=true, 1)"?

What does the "1" value mean there? I did not understand how the snapshot field will be populated.

Thanks!
Ok,
In the now table I assume that it's a formula date time field with the formula of
Now()


The record id of the only record in the Now table is 1.

In your details table create that formula field which will also have a 1 in it, but only once the status is Completed.  (ie that is the answer to your question.)

Make a relationship with that formula,field as the reference field on the right  side of the relationship.  Lookup the time stamp field from the now table down to the details table, and then make a snapshot of that lookup field.
Photo of Vilmondes

Vilmondes

  • 0 Points
I got it. It did work, but only if the checkbox is true the first time I save it. It's false when I save it, then I edit the record and tick the checkbox, the timestamp won't be generated.
That does not seem possible but I will test it tomorrow myself.
Photo of Vilmondes

Vilmondes

  • 0 Points
I appreciate it!
Photo of Vilmondes

Vilmondes

  • 0 Points
This might happen because a snapshot field doesn't allow the value to change, so if the field is empty when the record is saved for the first time, the snapshot field won't get another value when I edit the record and add 1 to the reference field...
Just guessing...
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
Not surprisingly the image onload technique provides a broader and more flexible solution. This tiny bit of script will intercepts click events on the Save button and allow you to run your own script and conditionally call the original click handler associated with QuickBase's Save button:

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


The script (1) saves the original click handler function, (2) removes the click handler from the Save button and (3) creates a new click handler which we control. In this case the new click handler stuffs the current time into the field with fid=6 when the Save button is clicked but the general arrangement of code could perform a (1) validation, (2) throw up a confirmation dialog or (3) perform some other function prior to proceeding to the normal Save functionality.

This method of gaining control of QuickBase's forms is preferred to more invasive techniques as it does not change the DOM or redefine QuickBase functions. In a very natural way this technique simply gives the user priority over handling a click on the Save button and then after some custom processing of the form conditionally calls QuickBase Save button if that action is warranted. After all, who should be in control of your form, you or others?
Photo of Vilmondes

Vilmondes

  • 0 Points
Hi Dan,

That sounds promising =). Just a question, though.

The page where I'll add the jQuery code is a "Code Page", right?

Thanks a lot for sharing that, BTW
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
Yes a code page. But that is just one step. Here is the full set of instructions:

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=294
I set up a test app, which I can invite you to if you like, and got this working.  I think that using a formula to connect the tables did not work for the snapshot, but if the choices come directly from that Now table as choosing Yes or No from two records, will trigger the snapshot properly.  So instead of clicking a checkbox to indicate the Project is complete, you would select from a Yes / No dropdown list from the relationship to the Now table.

My Now table had a Key field called Yes / No and I entered two records, one for Yes and One for No in that Key field.  Those will be the choices for the Project Completed field in the Project table.  I added a formula field to the Now table If([Yes / No]="Yes",Now()).

Then I made a relationship back to the Projects table so that the choices for the Project Completion will come from the Now table.

That worked.  In Grid Edit the snapshot field will alternately populate and blank out of the Yes / No field in the details table is changed.  Here is a link to the app https://yqc.quickbase.com/db/bi9r766n3?a=td  you can click though an ask for permission and I will give you admin permission.
Photo of Vilmondes

Vilmondes

  • 0 Points
If you could grant the permissions to the email vilmondes.queiroz@emoderation.com, I'd appreciate it!
I'm just on my iPhone now. Can you click through that link and ask for permission, it will be easier for me to grant you permission that way from my iPhone.
Photo of Vilmondes

Vilmondes

  • 0 Points
I tried that, but it's asking me to sign into yqc =/
Go arhat and register into that screen, then ask for permission when you get blocked.
Photo of Vilmondes

Vilmondes

  • 0 Points
That's great! It would be great if the timestamp is generated in the Date/Time field right after we select "yes" (on grid edit view), but I know this is not possible (only with the image onload technique which I haven't tested yet).

Thanks a lot for you time!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
only with the image onload technique ... can you do everything. In other words if you don't use the image onload technique you have to hunt around for bits and pieces of lesser solutions and cross your fingers.
Photo of Insight

Insight

  • 0 Points
I applied the IOL technique and its working with the Alert.
How do I get it to fill the Date Time field with seconds?

Tks