Skip to main content

Using Python library to get records based on date formula

  • June 23, 2021
  • 1 reply
  • 68 views
D

Hello Everyone,

I’m using a library called “Airtable Python Wrapper” which can be found here: Airtable Python Wrapper Documentation — Airtable Python Wrapper documentation

It has a method, “get_all” that will take an Airtable formula to filter records and return the ones that make it through the formula.

filterByFormula = 'IS_AFTER(LAST_MODIFIED_TIME()+\\"2021-01-25T14:23:41.000Z\\")'


def get_date_changes():
    query = AT.get_all(formula=filterByFormula)

However, that formula returns an empty list. I know that there are records in that table with modification dates later than that as new records are added almost every day. Can anyone tell me what might be wrong with that formula?

Thanks!

-Dan

1 reply

Justin_Barrett
Forum|alt.badge.img+21
  • Justin_Barrett
  • Inspiring
  • 4647 replies
  • June 27, 2021

The formula that you’re using isn’t valid for several reasons:

  • Dates written in strings are still strings, not dates. The DATETIME_PARSE() function is required to take a date string and turn it into an actual datetime (the data type used internally by Airtable for date comparisons and calculations)
  • Even if strings could become dates without that function, you’re currently taking the last modified time and trying to add it to another date, which isn’t a valid date operation.
  • The IS_AFTER() function requires two arguments, but you’re only passing one (the invalid date sum mentioned previously)
  • This isn’t technically a problem, but just an inefficiency: because you used single quotes to surround your formula string, you shouldn’t need to escape the double quotes inside it.

My gut says that you want to compare the record’s last modified time against that static date in the string. First let’s turn that date string into an actual datetime:

DATETIME_PARSE("2021-01-25T14:23:41.000Z")

In certain situations it’s not necessary to pass a formatting string to the DATETIME_PARSE() function, and this is one of those. It automatically converts to the correct time.

Next, the correct way to use IS_AFTER() to compare two dates is by passing each date as a separate argument:

IS_AFTER([date1], [date2])

Inserting the LAST_MODIFIED_TIME() function and our converted date string into this structure gives us this:

IS_AFTER(LAST_MODIFIED_TIME(), DATETIME_PARSE("2021-01-25T14:23:41.000Z"))

Putting all of that into your Python variable would then look like this:

filterByFormula = 'IS_AFTER(LAST_MODIFIED_TIME(), DATETIME_PARSE("2021-01-25T14:23:41.000Z"))'


Terms & ConditionsAccessibility statement