Update column with todays date when changed value in another column

[M_G]

I know that this can be solved with automation, but I was wondering if this is possible to implement with formulas only as for some accounts automations are limited:

If I change “Status” column to “Started” it changes “Started” column to today’s date.
But if I change “Status” column to “Finished” it does put today’s date into “Finished” column but date from “Started” column disappears.

Is there any way to modify the formulas to prevent this from happenning?

And another quick question for CODA GREAT FORMULA GURUS:

I used the formula in the screenshot above to populate “Task #” column with today’s date and row #. But the problem is with this formula that today the column regenerated with today’s date and not yesterday’s date when the rows were created.

How should this formula be modified to avoid that?

Thank you all.

[Pablo_DV]

@M_G

Is there any way to modify the formulas to prevent this from happenning?

No, Today() is being reevaluated constantly, so the formulas based on it change every day.

The only way to prevent that is manually converting the whole column into editable values, but that’s not what you want either.

Since the number of automation runs is a concern, I would suggest an automation triggered once per day instead of by ‘row changed’:

• Started and Finished should be Date columns with no formulas
• Create a ‘time-based’ automation that runs at midnight with the following formula

[your table].filter(
  Status="Started" AND Started.isBlank()
).ModifyRows(Started, Today()-1)

With this filter() you get all the tasks that are started but where Started date has not yet been added, that means that they were started during the day.

With ModifyRows() you change the Started value to yesterday, since the automation is run at midnight which is already the new day.

In order to do the same with the Finished date, we can use a very similar formula and run one after the other with RunActions().

RunActions(
  [your table].filter(
    Status="Started" AND Started.isBlank()
  ).ModifyRows(Started, Today()-1),
  [your table].filter(
    Status="Finished" AND Finished.isBlank()
  ).ModifyRows(Finished, Today()-1)
)

With this method all your items will get updated with a single automation run per day, instead of one run per item.

@M_G

How should this formula be modified to avoid that?

The issue is the same as before. To solve the issue, instead of Today(), refer to Started or Finished, which are now fixed values.

Please let me know if this helped you,

Pablo

[M_G]

Thank you @Pablo_DV I will try that.
What are the other ways to convert today’s date into a fixed value in the formula?
As it’s not ideal to refer to those columns for task # as they might be populated days after the row is created.

[Pablo_DV]

This converts the whole column into editable values, in Coda you cannot have some editable values and some calculated ones in the same column.

@M_G

As it’s not ideal to refer to those columns for task # as they might be populated days after the row is created.

I don’t understand what you mean here

[M_G]

What I meant is populating “Task #” column values based on “started” or “finished” columns won’t work as those dates will be always after the task creation date.

Somehow I need to find a way to populate “Task #” value with today’s date but without it being changed ever day. I tried to convert it to text but it didn’t work. It still changes:

Concatenate(totext(Today().Year()),“-”,totext(Today().Month()),“-”,totext(Today().Day()),“/”,RowId(thisRow))

[Piet_Strydom]

Have you considered using a button to update the status, and at the same time insert the relevant date into the start or finish columns respectively?

In this doc I give some examples of how to manage statuses and achieve side-effects simultaneously.

[M_G]

I’m having an issue with the first column “Task #” with today’s date being updated every day

[Piet_Strydom]

In the document I shared above there is a page called Managing Statuses. In there I give some examples of managing statuses. My suggestion is to use a button with a runaction() to update your status and the date. In this way you do not have a column with a today() as a formula. The date is posted when the button is pushed, and only then

[Melanie_Teh]

@M_G

for the Task #, is this what you had in mind?

if yes, you’ll need to change your Task # column to editable values (like what @Pablo_DV advised above), and then add your formula in the “Value for new rows” section.

Hope this helps!

Cheers!
Mel

[M_G]

Piet, thanks for your advice. But ideally I wanted to to have task# column populated automatically as soon as new row is created. Yes, it can be populated with automation or with a click of a button but that’s an extra friction )

[M_G]

@Pablo_DV

s manually converting the whole column into editable values, b

@Pablo_DV your suggestion and @Melanie_Teh your solution worked perfectly.

This was exactly what I needed. Convert that column to “editable values” and use my formula for new rows. Perfect solution, no need to use automation for this.

I have another problem with buttons resetting filters:

When I use this formula in the screenshot above to reset the filter for the column, for some reason it does not include “blank” value. Or sometimes it does return blank along with other values and if clicked again, it omits blank.

What exactly formula should be used in a button to reset canvas filters?

[M_G]

Just a quick update on reset button. It’s very easy to do - just use “Reset control value” instead of “Set control value”