Skip to main content

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

Thanks for reporting this, and sorry for the delay in responding. We’re looking at this now, and hope to have it fixed soon.


Thanks for reporting this, and sorry for the delay in responding. We’re looking at this now, and hope to have it fixed soon.


Thanks! I’m eagerly awaiting a fix 🙂


Hi! This bug has been fixed. Thanks again for reporting it :thumbs_up:


Reply