Skip to main content

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

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)

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)


The AFTER/BEFORE part could be easier choosing just the previous/next day, isn’t it? 🤔

Reply


Terms & Conditions