API Get Values based on Date Range

[cameron_stansel]

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.

[Bill_French]

Welcome to the community Cameron!

cameron_stansell:

I about ripped my hair out trying to get this UBER simple process to work.

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?

[cameron_stansel]

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
}

[Bill_French]

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