Formula using {Start Date} field always returns records with empty {Start Date}


#1

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


API Update: filtering records, limiting fields, better sorting
#2

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


#3

Thanks! I’m eagerly awaiting a fix :slight_smile:


#4

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


#5