Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 01, 2020 08:19 AM
I need to add a filter where “Date Arrive is on or before 4/30/2020” AND “Date Arrive is not empty” AND “Departure Date is empty” OR “Departure Date is on or after 4/1/2020”
I can’t do filters with AND plus ORs… Can someone tell me another option?
For context, I have people storing stuff with me. I’m trying to build a view that shows what they have stored with me for the month of April. They don’t want to see stuff that is no longer in storage (denoted as “Departure Date”)
May 01, 2020 10:30 AM
Hi @RJ_Martino
You can create a formula field to filter on that includes any conditions you want to filter on combined in any way you want.
Let’s say you call this field “Filter Formula”, and give it a formula something like this:
IF(
AND(
{Date Arrive},
OR(
IS_SAME({Date Arrive}, DATETIME_PARSE('4/30/2020', 'M/D/YYYY')),
IS_BEFORE({Date Arrive}, DATETIME_PARSE('4/30/2020', 'M/D/YYYY'))
),
OR(
{Departure Date} = BLANK(),
OR(
IS_SAME({Departure Date}, DATETIME_PARSE('4/1/2020', 'M/D/YYYY')),
IS_AFTER({Departure Date}, DATETIME_PARSE('4/1/2020', 'M/D/YYYY'))
)
)
),
TRUE()
)
This will output a 1
in the field when your conditions are met (TRUE()
resolves to 1
) and will be blank if your conditions are not met. You can make the formula arbitrarily complex.
Then, you can just filter on this field – set the filter where “Filter Formula = 1”.
Hope that helps. (I did not test the formula, so ping back if it throws an error or doesn’t work for some reason)
May 01, 2020 02:47 PM
The AFTER/BEFORE part could be easier choosing just the previous/next day, isn’t it? :thinking: