Skip to main content

I am fairly new to Airtable and was having trouble finding information on the filterByFormula option in the API. I was trying to get rows back based on a DATE field using a date range, so any date that fell between my startDate and endDate. I couldn’t find any specific examples, so I am posting this to hopefully help someone else that tries to do this. Below is some javascript code using airtable.js and this is what I used to be able to get results back within the range:


let data = await base(‘Timelogs’).select({

filterByFormula: IF(AND(IS_AFTER({DATE}, '${startDate}'), IS_BEFORE({DATE}, '${endDate}')), 1, 0)

}).all()


This may have been elementary but I about ripped my hair out trying to get this UBER simple process to work.


Edit: There are backticks (quotes would work too of course) around the IF() portion. This editor doesn’t seem to print them. startDate and endDate are variables in the code and I am using template literals to pull the values in.

Welcome to the community Cameron!



Ha ha, yes - there is not a single API out there that doesn’t encourage developers to overthink a working solution, however simple it may seem. Of course, in looking at it, it’s quite elegant and simple now that you’ve done it, eh?


I rarely use the javascript SDK, but I’m curious - does the .all() method automatically paginate so that your request gets all records even when the table has more than 100 records?

Welcome to the community Cameron!



Ha ha, yes - there is not a single API out there that doesn’t encourage developers to overthink a working solution, however simple it may seem. Of course, in looking at it, it’s quite elegant and simple now that you’ve done it, eh?


I rarely use the javascript SDK, but I’m curious - does the .all() method automatically paginate so that your request gets all records even when the table has more than 100 records?


Oh, definitely! I think it was mainly my lack of understanding of how the formulas worked within the UI since I’ve only ever started using airtable as a database. As with anything in programming, sometimes you have to look at it from a different perspective right?


Anyhow, to answer your question, I just duplicated a ton of rows to test this for you. I read somewhere from someone at Airtable that it does, and it appears to work. I am running the below code with 240 rows on the table, and I get 240 logged to the console.


const getAirtableTimelogs = async () => {

let data = await base(‘Timelogs’).select().all()

console.log(data.length)

return data

}

Oh, definitely! I think it was mainly my lack of understanding of how the formulas worked within the UI since I’ve only ever started using airtable as a database. As with anything in programming, sometimes you have to look at it from a different perspective right?


Anyhow, to answer your question, I just duplicated a ton of rows to test this for you. I read somewhere from someone at Airtable that it does, and it appears to work. I am running the below code with 240 rows on the table, and I get 240 logged to the console.


const getAirtableTimelogs = async () => {

let data = await base(‘Timelogs’).select().all()

console.log(data.length)

return data

}


I may just have to use the SDK. :winking_face:

Reply


Terms & Conditions