Create a timestamp on select list update but do not remove when changing again

Hi all! New to Coda here so bear with me.

I’m trying to make a simple status select list that updates a timestamp for “Started On” and “Completed On” columns.

So when the “Status” select list is changed to “In Progress” the timestamp updates “Started On” and when “Done” is selected it updates “Completed On”.

I’ve been able to get it to update with: if(thisRow.Status=“In Progress”,thisRow.Status.Modified(),“”)

But it removes the date when the status is changed to done. Any way to ignore if the field already has a date? I’ve tried using IsBlank() but for some reason I keep getting syntax errors on this:
If(thisRowthisRow.Status=“In Progress”,If(IsBlank(),thisRowthisRow.Status.Modified() ,“” ) ,“” )

Thanks for any help!

@Peter_Monterubio, welcome to this helpful and supportive community.

the reason the date changes is because it is being set by a column formula. so it gets recalculated.

to set a value that does not get recalculated, you need to use an ACTION formula.

an action formula occurs either in a BUTTON that gets pressed, or an AUTOMATION rule that is triggered.

lets begin using a BUTTON to set the Status to ‘DONE’ and set the CompletionDate to Today() as an example.

add a column to your table, lets call it ‘Completed’ and give it the following formula for its action…

thisRow.ModifyRows(
    Status, "DONE",
    CompletionDate, Today()
)

the ModifyRows() function sets values when the action runs and they dont get recalculated otherwise.

you can only modify columns in the table that dont have their own formulas, so you will need to remove the existing formulas (edit, select-all, delete-key).

hope this example sets you on the right path.

action formulas are one of the biggest things that set Coda apart from all the other tools out there.

max

3 Likes