Following situation:
I have a column with dates and another one, where I add 4 years to that date via dateadd().
Now i have used the format settings feature and the column with the added 4 years is correctly recognized as being date.
Now, if I use the values of this column in a automation rule i.e. to check, if the calculated date is within this year, the check never works.
In fact, none of the given checks for rules seems to work, if the column is calculated and not a “native” date column.
Also works with German date format and empty cells:
I guess this rule is the issue:
The rule only checks once a month. How often do you expect the automation to trigger?
Second, the column “Abstrahlprüfungstermin vereinbart für” is part of the condition, but it is not shown in the first screenshot. So it’s possible that there is the problem. Maybe the cells are not empty?!
I know it works only one a month. Yet, there is the “run now” button for testing, which fires correctly and gives the notifications, if I change the “AP fällig” column to be a native date type, but not if it is a calculated date.
Also, i did reproduce your example and it works with the calculated date.
Actions that reflect immediately after change of records inside table are working.
Seems, it stops working, if you leave the table to do something else, like sending notice or, it has to do with the sequence trigger.
I dont know, if it is the “run now” button or the sequence trigger or some handling issue, if you do actions that dont reflect in the table itself, like sending a notice.
But there IS something fishy around the event handling there.
Maybe you could reproduce the issue, if you would really reproduce the action I tried to establish.
It would have helped me if you had posted a screenshot from your rule in your first post. So I could have ruled out the date format aspect right from the start. Also I would have known that you use the event “Run periodically on records meet conditions”.
Indeed. Screenshots would have helped from the start. At least in post 2 there are there. I stumbled over my own asumption, that the data type triggered the issue, not something connected to event handling in conjunction with data type.
I use the event “Run periodically on records meet conditions” (like you) and I monitor a calculated column (like you). The triggered action is “Lock row”. When testing the automation by hitting “Run now”, the correct rows get locked.
Please understand that I will not test further unless you can provide a case that I can reproduce reliably.
Damn. Could it be it has to do with the fact, that it is a self hosted instance?
Will have to try to delete the table and resetup the whole thing then.
Btw, this is the formula I used for the “AP fällig” column:
if(not(isempty(“Letzte AP”)),dateAdd({Letzte AP},4,“years”),“”)
I worked around the problem now, by adding another column and calculating year(now()) - year({Letzte AP})
And if that gives >= 4 it will fire the notice.
Interestingly, if I change that formula to be year({AP lällig}) - year({Letzte AP}), it still gives the correct integer in the column, but the notice gets not fired.
Sooo, I assume you are right, it must be the if() part, that I introduced to get rid of those ugly !VALUE entries…
Yepp it is the if part. Actions outside table get only fired, if the calculation of a date is not encapsulated by another funtion, like if().
Now finally, the root of the problem.
Yet, how can I get rid of the #VALUE! then?
Oh and… is it a bug now?
And finally sorry for wasting your time by not fully documenting the environment in the first place.
To give you further insight:
I did the if(), to prevent cells without a date to calculate from producing #VALUE! entries in the calculated date column.
Then I sorted the table to have the dates on top, in order to have the automatted format check, to give me date instead of string.
Result was: inside table, calculations and checks against the calculated date did work, as we have seen.
Actions that trigger notifications or mail sent events, did not work, not even if they were based on another column, that just represented results from further calculations with the calculated date column.
As soon as the if() part around the date function was removed, the event handling worked again for all types of events.
We have the 3.3.7 installed. So it is beyond me, why it is kind of broken here but not reproduceable on your system. have you also tried not only with the if() but also with the not()?