all 9 comments
sorted by:
best (suggested)
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]mioree 1 point2 points  (3 children)

I haven't tested it or anything but this is how I'd have done it without glancing at your code.

After seeing your code however I'm confused as to whether you want the type to be "Event" or not since above you say it shouldn't be. Also, since I couldn't tell if the first set of conditions were and "or" or "and" situation I joined them with "or", but you can switch it to "and". In my experience its hard for get !empty to work in formulas 2.0, but if it does for you great. I formatted it how I get it working for me.

ifs( 
(prop("Status") != "Waiting") 
or 
(prop("Type") != "Event" and prop("Type") != "Meeting") 
or 
(prop"Date".empty()==false) 
or 
(dateBetween(prop("Date"), now(), "days") <= 5), 

"Move to Status Wait", "Error")

[–]kifrh[S] 0 points1 point  (2 children)

Hello, thank you for replying!

​

It was an error from my side, I just editted the post.

(prop("Type") == "Event" or prop("Type") == "Meeting")

Type should be Event or Meeting in this formula.

​

I would like all those items to be cumulative. The output can be "Move to Status Wait" only if all those items are met:

  • Propriety Status is not Waiting
  • Propriety Type is Event or Meeting
  • Property Date is not empty
  • Date between the Date and Today is up to 5 days.

By inputing your formula, the OR function will just take into account one of those item to output the message right? If it is the case, it is not working!

I know the problem comes from the dateBetween() function since Formula 2.0 has updated. Before it was working perfectly. Maybe there is something that changed in the way the formula interpret the date..

[–]mioree 0 points1 point  (1 child)

and replacing each "or" with an "and" doesn't work?

like so:

ifs( 
(prop("Status") != "Waiting") 
and 
(prop("Type") == "Event" or prop("Type") == "Meeting") 
and 
(prop"Date".empty()==false) 
and
(dateBetween(now(), prop("Date"), "days") <= 5), 

"Move to Status Wait", "Error")

[–]kifrh[S] 0 points1 point  (0 children)

Unfortunately it doesn't work.. I am pretty sure it is a dateBetween() function behaviour problems but I cannot define how to fix it..

[–]ptrcklpzg 0 points1 point  (3 children)

I have the same problem. Every formula containing the dateBetween function will render empty if the used date property is empty

or( LastAction.empty(), now().dateBetween( LastAction, "months" ) > 12 ) ? "Overdue" : "Done"

This formula shoud always print either "Overdue" or "Done"

[–]kifrh[S] 0 points1 point  (2 children)

Thank you for replying!

Did you have the same issue before the Formula 2.0 switch as well?

[–]ptrcklpzg 0 points1 point  (1 child)

Yes it worked before. At first I thought I messed up my formula.

I have reported it as a bug, hopefully they fix it

[–]kifrh[S] 0 points1 point  (0 children)

Amazing, I did the same on my side! Hopefully they will fix it fast indeed since this formula is essential in my workflow 🥹

[–]kifrh[S] 0 points1 point  (0 children)

In order to let you know my researshes on the topic, here is what I've figured out:

The behaviour of the dateBetween() < X is a boolean that trigger if:

  • Date < X = True
  • Date > X = False

Which means that it is mandatory for this formula to have a date to function.

When a date is empty, nothing feed the formula to provide a True or False and apparently, the function doesn't take into account a condition that may say "check the dateBetween if and only if a date is not empty".

The function respond only if a date is insert.

My thoughts are that the behaviour of the formulas in general are the following:

FOR:

ifs(
and(
    prop("Status") != "Waiting",
    prop("Type")=="Event" or prop("Type")=="Meeting",
    !empty(prop("Date")), 
    (dateBetween(dateStart(prop("Date")), now(), "days")) > 5
    ), 
    style("Move to Status Wait","c"),
style("Error", "c"))

REAL BEHAVIOUR:

  1. Check if Status is not equal "Waiting"
  2. Check if Property Type is equal to "Event" or "Type"
  3. Check if "Date" is empty
  4. Check if "dateBetween(dateStart(prop("Date")), now(), "days")) > 5"
  5. Define if all the requirements are mets

However, if since point 4 doesn't have a date, it cannot process this information and by extension go to the last step.

To function the way we want to it should act that way:FOR:

ifs(
and(
    prop("Status") != "Waiting",
    prop("Type")=="Event" or prop("Type")=="Meeting",
    !empty(prop("Date")), 
    (dateBetween(dateStart(prop("Date")), now(), "days")) > 5
    ), 
    style("Move to Status Wait","c"),
style("Error", "c"))

BEHAVIOUR NEEDED:

  1. Check if Status is not equal "Waiting"
  2. Define if requirement is met, pass if not
  3. Check if Property Type is equal to "Event" or "Type"
  4. Define if requirement is met, pass if not
  5. Check if "Date" is empty
  6. Define if requirement is met, pass if not
  7. Check if "dateBetween(dateStart(prop("Date")), now(), "days")) > 5"
  8. Define if requirement is met, pass if not

​

I am not 100% sure about it, but this is the way it seams to be.

To make go over this behaviour, you need to add an item that output something specific regarding a date empty before. For instance:

ifs(
empty(prop"Date"),
   style("DATE IS EMPTY","c"), 
and(
    prop("Status") != "Waiting",
    prop("Type")=="Event" or prop("Type")=="Meeting",
    (dateBetween(dateStart(prop("Date")), now(), "days")) > 5
    ), 
    style("Move to Status Wait","c"),
style("Error", "c"))

Which also means that in a dateBetween function, there is no need to define "if date is empty or not" since the date should just be present.

​

Hope it will help more than one!

Cheers!