Hello,
I’m using the NOW() formula formula in a Data Column (column B). It is triggered on changes to column A :
Column A = "⚪ " if $Status == "Published" else "🟠" if $Date == None else "🟢" if ($Date >= TODAY(tz=None)) else "🔴"
Column B = NOW()
My problem is that NOW() in column B is not triggered because column A is a conditional formula and Grist doesn’t see when their is a change. Is there a workaround to trigger the NOW() formula when the output of the conditional formula in column A changes ?
Hi Fabien!
When I set this up in a table, I was able to get the Date column to trigger based on any changes to column B. Below is the table I set up. Status is a choice column of “Published”, “Not Published” and “None”. Column A is using the formula you provided. Column B is using a modified version that works when the Date column only uses Date (not time). Date column is of column type Date and uses a Trigger Formula that applies only when there are changes to column B. B2 is using another modified version of the formula you provided but this one works when the second Date column (of column type DateTime) uses Date and Time.
Starting with the Date column, be sure to make this a trigger formula and apply on changes to: B. The Column Type should be either Date or DateTime then select the format that works best for you. The difference between Date and DateTime is that Date only includes Date while DateTime also includes a timestamp. This can make a difference with which formula to use and I’ll talk about that again below.
The formula below is the one you provided;
"⚪ " if $Status == "Published" else "🟠" if $Date == None else "🟢" if ($Date >= TODAY(tz=None)) else "🔴"
I modified it a bit to get the following;
if $Status == "Published" and $Date == None:
return "⚪"
elif ($Date <= TODAY(tz=None)) and $Status =="Published":
return "🟢"
else:
return "🟠"
Note that the formula above includes $Date <= TODAY(tz=None)) - this only works if you are using the Date column type (not DateTime). I added column $B2 and $Date2 to show what to do if you’d like to include a time stamp in the date column. Below is the formula used there (again with some modifications)
if $Status == "Published" and $Date2 == None:
return "⚪"
elif ($Date2 <= NOW(tz=None)) and $Status =="Published":
return "🟢"
elif $Status == "None":
return "🟠"
else:
return "🔴"
When you use $Date2 <= NOW(tz=None)) , it’s reading the time as well as date so you won’t get an error.
With this second date column, I have it set to Apply on changes to B2 and column type is DateTime.
Keep in mind that editing the formula does not trigger “trigger formula” columns. If you update Status, it should trigger both Column A and Column B.
Hello,
Thanks for your help !
I tried but it still doesn’t trigger. I think in your example you changed the status that’s why it triggers. In my case, if I don’t change the status and if the “timer” turns red just because the Delivery Date becomes inferior to the current date. Since their is no manual update in this table, the timestamp doesn’t trigger… I feel like Grist needs a manual input in order to update the timestamp.
Timer column :
Delivery (manual date) column
Timer Change (auto Timestamp) column :
For example, if I update the Delivery date, then the timestamp is updated even though the NOW() formula doesn’t apply on changes to this field. What I would need would be the equivalent to a cron job. Let me know if I get it wrong.
Ah, thank you for those details. I see what you mean now.
Unfortunately, Grist doesn’t support this out of the box yet but that is a good feature request and I have shared it with our team. They had few suggestions for a workaround.
You could add a table with a single cell containing the current time, and then have everything read from that time , but you still need to trigger it to update.
Another option would be to use Zapier and create a zap that updates that one cell periodically - or a cron job that does the update. Some sites like https://cron-job.org/en/ or https://www.easycron.com/ will send a configured http request on a schedule, which might be preferable to a zap.
1 Like
