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.

How to use filterByFormula with a date field

Topic Labels: API
Solved
Jump to Solution
4923 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Alessandro_Mari
4 - Data Explorer
4 - Data Explorer

Hi folks,
I’m using filterByFormula param to filter records of a table but I’m able to select for a specific date.

Below and example:

request:
https://api.airtable.com/v0/appFOOBAR/Spese?filterByFormula=AND({Importo}=111.22,{Data}='2022-04-01')

response:

{
	"records": []
}

while with the request below I got a result:

https://api.airtable.com/v0/appFOOBAR/Spese?filterByFormula=AND({Importo}=111.22,not({Data}='2022-04-01'))

response:

{
	"records": [
		{
			"id": "recXXXX",
			"createdTime": "2022-05-24T09:38:50.000Z",
			"fields": {
				"Tipologia": "Spesa",
				"Data": "2022-04-01",
				"Importo": 111.22,
				"Name": "Prova 1"
			}
		}
	]
}

so the example using not operator shows that there’s a record with Data=2022-04-01, I don’t understand why this record isn’t extracted using the first request.

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

Welcome to the Airtable community!

Is {data} a date field? If so, your formula is comparing a date with a text string, and that just doesn’t work in Airtable formulas. It doesn’t matter that the date looks like a text string in the API response, when the formula is evaluated, Airtable treats the date like an object, and not a string, and dates and objects cannot be equivalent. This is why the api returns the record when you ask for records that are not equivalent.

The easiest fix is to use a formula that turns the date field into a string.

DATESTR({Data})='2022-04-01'

You could also convert the string into a date and then use a date comparison function, but that is more complex.

See Solution in Thread

2 Replies 2
kuovonne
18 - Pluto
18 - Pluto

Welcome to the Airtable community!

Is {data} a date field? If so, your formula is comparing a date with a text string, and that just doesn’t work in Airtable formulas. It doesn’t matter that the date looks like a text string in the API response, when the formula is evaluated, Airtable treats the date like an object, and not a string, and dates and objects cannot be equivalent. This is why the api returns the record when you ask for records that are not equivalent.

The easiest fix is to use a formula that turns the date field into a string.

DATESTR({Data})='2022-04-01'

You could also convert the string into a date and then use a date comparison function, but that is more complex.

Thank you @kuovonne !
I was able to fix the formula by following your suggestion!