Trouble with automation for date column

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.

I cannot reproduce your issue.

As you can see, it works like a charm.

This is my automation rule:

I take the liberty of changing the title of your post because there is no bug. I also took the liberty of re-categorizing your post.

Lastly: As a general rule, please provide screenshots. Please follow good community practices as outlined here: Read before you post in User Talk

Alright, here we go. Screenshots:

As you can see, the yellow ones are within this year.

  1. now, second screenshot the rule:

And it does not work.

In contrast, If I change the formular column to be a real date column and set the dates manually, the exact same rule works.

Differences to your sollution:

  • German Date format

  • Empty cells in the column, as not every device needs that date.

From my pov, that is a bug, not a feature. :wink:

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.

I will in due course.

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.

Next time :wink:

Still cannot reproduce your issue.

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.

Try again with sending a notice to yourself. An action, that is not reflected inside the table.

Still cannot reproduce:

This was really the last test that I entertained.

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”),“”)

Check the logs for any errors.

This, btw, is also a piece of info you could have shared a bit earlier.

Mine is the same, only that it is not nested in an if function.

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…

Will try without if now…

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? :wink:

And finally sorry for wasting your time by not fully documenting the environment in the first place. :frowning:

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.

I added the IF-bit to the formula. And: I still cannot reproduce your issue.

Sorry, still not.

Which version do you use? I tested in SeaTable Cloud, that is SeaTable Server Enterprise Edition Version 3.3.7

Now I am interested. Any news? After investing so much effort, I’d like to get to the root cause of the problem.

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()?

if(not(isempty(“Letzte AP”)),dateAdd({Letzte AP},4,“years”),“”)

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.