Skip to main content
Solved

OR, filterByFormula, and Date Field don't work together

C

I have a field called {Expires}. I would like to use the filterByFormula parameter via the API to retrieve records that have no expiration date or that have not yet expired.


This successfully retrieves records that have no expiration:

{Expires}=’’


This successfully retrieves records that have not yet expired:

IS_BEFORE(TODAY(), {Expires})


Putting the two statements into an “OR” condition seems like it should work, however it only returns records that have not yet expired—omitting records that are empty/blank/have no expiration:

OR({Expires}=’’, IS_BEFORE(TODAY(), {Expires}))


I have also tried testing for ISERROR rather than =’’, but the behavior is the same. This works as expected (returns records with an empty/blank {Expires} field):

ISERROR(IS_BEFORE(TODAY(), {Expires}))


This omits records with empty {Expires}:

OR(ISERROR(IS_BEFORE(TODAY(), {Expires})), IS_BEFORE(TODAY(), {Expires}))

Best answer by Emmett_Nicholas

This is happening because IS_BEFORE returns #ERROR! if one of its arguments is empty, and OR returns #ERROR! if one of its arguments is an error. Both variants of your “OR” formula use IS_BEFORE(TODAY(), {Expires}) as the second arg, which returns an error when Expires is empty.


We’ll take another look at whether the current behavior is as sane and expected and possible, but in the meantime this should do the trick:


IF(Expires = '', 1, IS_BEFORE(TODAY(), Expires))
View original
    Did this topic help you find an answer to your question?

    2 replies

    E

    This is happening because IS_BEFORE returns #ERROR! if one of its arguments is empty, and OR returns #ERROR! if one of its arguments is an error. Both variants of your “OR” formula use IS_BEFORE(TODAY(), {Expires}) as the second arg, which returns an error when Expires is empty.


    We’ll take another look at whether the current behavior is as sane and expected and possible, but in the meantime this should do the trick:


    IF(Expires = '', 1, IS_BEFORE(TODAY(), Expires))
    C
    Emmett_Nicholas wrote:

    This is happening because IS_BEFORE returns #ERROR! if one of its arguments is empty, and OR returns #ERROR! if one of its arguments is an error. Both variants of your “OR” formula use IS_BEFORE(TODAY(), {Expires}) as the second arg, which returns an error when Expires is empty.


    We’ll take another look at whether the current behavior is as sane and expected and possible, but in the meantime this should do the trick:


    IF(Expires = '', 1, IS_BEFORE(TODAY(), Expires))

    Ah! Thanks for the clear explanation.


    In OR conditionals that I’m used to using, if the first condition is satisfied the rest are ignored/not checked.


    Which is why I put the ISERROR() or =’’ first, hoping that the records that would generate an error would match first and the IS_BEFORE() wouldn’t be run.


    And while I’m here… why ISERROR and not IS_ERROR? Ok, now I’m just being ungrateful.


    Thanks for the working code! Problem fixed and client happy 🙂

    Reply


    Most helpful members this week

    Terms & Conditions