Need help creating a timestamp that doesn't change once entered like now() does

I have a check box column and then I have a column next to it where I want the timestamp to be entered on when the box was checked. I was able to get it to work with now() in the formula, but that date and time is not maintained, but instead stays current which I guess is the point of the now() formula. I have also attempted modified() in the formula, but then it puts a date/time in every row of the column not just the ones that have been checked. How do I get it so when the box is checked the current date and time is entered in the second column, but remains static and the rows where the box is not checked remains blank? I was wondering if maybe it would be possible to use the now() formula to get the current date/time and then have it converted to text so it doesn’t change?

Any help would be appreciated.

Hello @Jennifer_Biggs!
Seems like the perfect use for a button! You can create a button column that checks your checkbox column and at the same time writes the timestamp.
I leave a Coda with an example:

5 Likes

@Saul_Garcia , very nice usage of the button logic with the icons in it and

ModifyRows(thisRow, Tasks.Done, thisRow.Done.Not(), Tasks.Timestamp, If(thisRow.Done,"",Now()))

works perfect for the Now() ´function is inserted as plain text, so won’t change anymore. This is one of the things I really like about buttons, the output is always plain text.

1 Like

And once you want to reuse the timestamp data again, it is easily done since the data is already structured properly by adding the ToDate() function and if you want to substract from this day Today() you can write it as below

`today() - thisRow.Timestamp.ToDate()

The data I often get in as text is structured in a EU format. If that is the case I have to bring it back to the ‘source format’ which is month,day, year (mm,dd,yyyy) and I use a formula like the one below:

Concatenate(thisRow.[Created at].Split("-").Nth(2),"/",thisRow.[Created at].Split("-").Nth(1),"/",thisRow.[Created at].Split("-").nth(3)).Todate()

The reordering of data takes place via the Nth() Via Split() I turn the text string into a list and each position in a list can be pointed to via Nth()

3 Likes

Thank you so much for your help. I really appreciate it. I was able to get this to work in my document. I do have a question on how you got the checked box and the clear box into the label of the button? I was able to copy and paste what you had, but I wasn’t able to figure out how to get any other image in the label field. I tried copying and pasting another image, but it didn’t work. I even tried copying the icons from within Coda, but that didn’t seem to work either. So can you please explain how you did that?

Thanks

1 Like

Hello @Jennifer_Biggs ,
The easiest way to do that is:

  • make sure the cursor is somewhere on the canvas
  • go to the top menu of coda and click on the emoticon icon
  • find the icon you want to use and click on it
  • now it sits on the desktop
  • copy it, edit your button properties and paste it in the label field
    That is all there is to it. You can paste more than one icon.
2 Likes

Thanks for the help. That is just what I needed.

1 Like

How do you insert the current date/time into a field?

Say with the creation of a new row into a start date field, just to make it tricky, and then perhaps it wouldn’t need a button? But then it would need a done button to put the end date. Is this possible?

Hey @dr_john_Pollard
you can set a “Value for new Rows” in the Field options. If you put a formula in there, it is executed when a new row is created. But only the result as a value is put into the cell - not the formula itself. So you can still edit the date/time later.

Bildschirmfoto 2021-03-22 um 08.41.58

You can use Now() or Created().

If you don’t want to edit it, you can also take advantage of the “created on”-property column. You can either select it from the column type menu or create any date/datetime column and enter the formula =thisrow.created(). This could also be helpful when you already have a table and want to add the start at a later point.

Bildschirmfoto 2021-03-22 um 08.44.50

In this example are both next to each other

Why would you send me an example that was view only? I can’t even test it. Much less copy and use it.

Also I saw that solution in another post and it only puts now() into the field and says it can’t convert value to specified format.

Dear @dr_john_Pollard
the example works in the embedded version, which is set to “Play mode”. You can interact with it and look into the setup, directly inside the community here. If you click on “Open in Coda” in the top right corner, you are in view mode anonymiously and can’t interact. But if you sign in in the top right courner, you should be able to Copy the doc, again with a button in the top right corner. But let me know if this is not the case.

You might need to enter “=now()” in the instead of “now()” in the “Value for new rows” field, so it is executed as a formula, not as a value. In the screenshot in my last post, you can see the “F” button on the right side of the input field is highlighted dark. This shows that the content of the input field is a formula (with an “=” in front) and not text.

Formatting can be done with the “Date Format” and “Time Format” drop down fields above the “Value for new rows”.

To which community post to you refer? I’m pretty sure this or something similar is explained at least once in the community already, anyway I wanted to give you a quick answer here where you posted your question.

1 Like
 --To which community post to you refer? 

It was one I found with a youtube search.

 -- But if you sign in in the top right corner, you should be able to Copy the doc, again with a button in the top right corner. But let me know if this is not the case.

Yes that did work, thank you.

–You might need to enter “=now()” in the instead of “now()” in the “Value for new rows” field, so it is executed as a formula, not as a value.

I tried both those earlier with the same result. Possibly missing the “=”

So I pasted the formula and it worked for the new record. I have no idea why or how. which is awesome.

Okay, I got there finally. I had to click in the formula where the " " were hard coded. Which blocked the other way, which it looks like you explained.

I tried to copy the button in the source doc, to paste, and couldn’t see how. I’m sure you can copy a button somehow. You’d think it would be there with duplicate.

Anyway, thank you. Now I just have to tab 5 times to make a change.

Gee, NEW PROBLEM

I guess I can’t edit the start time because it’s a formula now. You mentioned
–you can set a “Value for new Rows” in the Field options. I can see it in the col but I can’t change it.

Also, what’s with the literal impossibility of editing the minutes? I put something in there and it changes to something wonky. You can only have 15 min increments? What’s with that?

Now it seems like what I truly want for the start date field is a copy of the prior records end time/date, then pasted into the new record start date. And to be able to edit it.

This is really not ready for prime time.

How do I stop the calendar from displaying to edit the time?

Thank you for this. You have no idea how much time you just saved me!

1 Like