Passing a date in from an API call to select rows based on created date/time

[rayporter]

I tried posting this question yesterday but now I can't find the post so it looks like it didn't actually get created so I'm trying again.

I've been tasked with creating an interface to our AirTable data using an API developed by Brightvine (Brightvine Data Link).  I can get back all the data using the API interface but I need to be able to pass in the last date the process ran on our end and get only those rows created since the last run date/time.  The BVDL gives me a place to enter URL parameters and I've tried using the "filterByFormula" option but I keep getting an invalid formula error.  The BVDL gives me access to the last run date/time in a couple of different formats when creating a parameter.

Any suggestions will be greatly appreciated.

Thanks,

Ray Porter

[kuovonne · Accepted solution]

Try creating your formula in an Airtable formula field first to get the format of the formula right. You will also need a {Created time} field. Then after you have the formula right, move it to filterByFormula.

You will probably want a formula that looks something like this. In your code you will replace the date string with your last run date.

IS_AFTER({Created time}, DATETIME_PARSE("2024-01-01", "YYYY-MM-DD"))

A few notes:

- You can include a time in the formula in addition to the date if you need that precision. Lookup the DATETIME_PARSE() function in the formula field reference for more info. 

- Airtable formulas process date/time fields as GMT, so submit your date/time as GMT not your local time.

- Make sure you url encode the parameters.

- The white space in the formula is not strictly necessary. I just like it to make it more human readable.

[pisowifi]

The author is developing an API for filtering database rows based on created_date or created_time, seeking best
 10.0.0.0.1 – 10.0.0.1
practices for date formatting, time zone handling, and efficient query parameters.

[rayporter]

Thanks.  After some trial and error, this solution is what I needed.  The Brightvine interface makes our lastrun date/time available to me in a couple of different formats.  Here's the formula that worked:

IS_AFTER({Created Date/Time}, DATETIME_PARSE("[$UTC_LASTRUNTIME(u)]", "YYYY-MM-DD"))