Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

API Get Values based on Date Range

Topic Labels: API
1494 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

3 Replies 3

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
}

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