Help

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

Solved
Jump to Solution
796 0
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
10 - Mercury
10 - Mercury

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
10 - Mercury
10 - Mercury

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