When using filterByFormula via the API, records on which the given formula produces an error are returned NO MATTER WHAT—even when a different record ID is supplied.
For example, I have many records with a {Start Date} field containing a date and one record with an empty {Start Date} field.
IF I use the API to ask for records with filterByFormula=“RECORD_ID()=‘rec5pBFqsn3Bva8mz’”, I will of course, only get that one record. As Expected.
And if I use the API to ask for multiple records using filterByFormula=“OR(RECORD_ID()=‘rec5pBFqsn3Bva8mz’,RECORD_ID()=‘n3Bva8mzrec5pBFqs’”), I will get only the records I specified. As Expected.
However, if my formula references the {Start Date} field, then any records on which that formula produces an error will be returned, even though its record ID was not among those specified. So using DATETIME_DIFF on the {Start Date} column in a filterByFormula means I’ll ALWAYS have the records with empty {Start Date} included in the results—even if I try to include filters requiring it to not be false, empty, or an error.
This is a fairly urgent problem! Here’s further examples.
This returns a single record, as expected
RECORD_ID()=‘rec5pBFqsn3Bva8mz’
This returns a single record, as expected
AND( RECORD_ID()=‘rec5pBFqsn3Bva8mz’ )
This returns multiple records: the one specified as well as any records with no value in the {Start Date} field
AND(DATETIME_DIFF({Start Date}, TODAY(), ‘days’) <= 365, RECORD_ID()=‘rec5pBFqsn3Bva8mz’ )
These workarounds do not help at all. Same results as above
AND(DATETIME_DIFF({Start Date}, TODAY(), ‘days’) <= 365, RECORD_ID()=‘rec5pBFqsn3Bva8mz’, NOT({Start Date}=’’) )
AND(DATETIME_DIFF({Start Date}, TODAY(), ‘days’) <= 365, RECORD_ID()=‘rec5pBFqsn3Bva8mz’, NOT({Start Date}=FALSE()) )
AND(DATETIME_DIFF({Start Date}, TODAY(), ‘days’) <= 365, RECORD_ID()=‘rec5pBFqsn3Bva8mz’, NOT(ISERROR({Start Date})) )
AND(DATETIME_DIFF({Start Date}, TODAY(), ‘days’) <= 365, RECORD_ID()=‘rec5pBFqsn3Bva8mz’, NOT(ISERROR(DATETIME_DIFF({Start Date}, TODAY(), ‘days’))) )
