Help

How to use filterByFormula with a date field

Topic Labels: API
Solved
Jump to Solution
3186 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!