Skip to main content
Solved

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

  • November 19, 2024
  • 6 replies
  • 5 views
R
Forum|alt.badge.img+4

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

 

Best answer by kuovonne

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.

 

View original
    Did this topic help you find an answer to your question?

    6 replies

    kuovonne
    Forum|alt.badge.img+27
    • kuovonne
    • Brainy
    • 6002 replies
    • Answer
    • November 19, 2024

    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.

     

    R
    1 person likes this
    • R
      rayporter
    P
    Forum|alt.badge.img+2
    • pisowifi
    • New Participant
    • 1 reply
    • November 20, 2024

    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.

    R
    Forum|alt.badge.img+4
    • rayporter
    • Author
    • New Participant
    • 1 reply
    • November 20, 2024
    kuovonne wrote:

    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.

     


    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"))

    T
    Forum|alt.badge.img+1
    • teraboxmodapk
    • New Participant
    • 1 reply
    • January 10, 2025

    To select rows based on a created date/time from an API call, you can pass the date as a query parameter The API can then filter rows where the  column matches or is greater than the specified date/time in the query. Make sure the date format is compatible with the database or service you're querying.

    L
    Forum|alt.badge.img+2
    • lmc84
    • New Participant
    • 1 reply
    • January 20, 2025

    You can pass the date from the API call as a query parameter LMC 8.4 and filter rows based on the or  column using a SQL  clause or equivalent filtering logic in your backend code.

    C
    Forum|alt.badge.img+2
    • cinemahd564
    • New Participant
    • 1 reply
    • January 27, 2025

    To download the CinemaHD APK, visit a trusted third-party website, ensure it's a secure source, and download the APK file. Enable "Install from Unknown Sources" in your device settings, then open the APK file to install the app. Be cautious of potential security risks from unverified sources.

    Reply


    Most helpful members this week

    Terms & Conditions