Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

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

Topic Labels: API
Solved
Jump to Solution
4265 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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

1 Solution

Accepted Solutions
Katherine_Duh
Airtable Alumni (Retired)

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

See Solution in Thread

3 Replies 3
Kasra_Kyanzade1
6 - Interface Innovator
6 - Interface Innovator

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 :slightly_smiling_face:

Katherine_Duh
Airtable Alumni (Retired)

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