Building Formulas

pyAirtable lets you construct formulas at runtime using Python syntax, and will convert those formula objects into the appropriate strings when sending them to the Airtable API.

Basics

In cases where you want to find records with fields matching a computed value, this library provides the match() function, which returns a formula that can be passed to methods like Table.all:

pyairtable.formulas.match(field_values, *, match_any=False)[source]

Create one or more equality expressions for each provided value, treating keys as field names and values as values (not formula expressions).

If more than one assertion is included, the expressions are grouped together into using AND() (all values must match). If match_any=True, expressions are grouped with OR().

>>> match({"First Name": "John", "Age": 21})
AND(EQ(Field('First Name'), 'John'),
    EQ(Field('Age'), 21))

>>> match({"First Name": "John", "Age": 21}, match_any=True)
OR(EQ(Field('First Name'), 'John'),
   EQ(Field('Age'), 21))

To use comparisons other than equality, use a 2-tuple of (operator, value) as the value for a particular field. For example:

>>> match({"First Name": "John", "Age": (">=", 21)})
AND(EQ(Field('First Name'), 'John'),
    GTE(Field('Age'), 21))

If you need more advanced matching you can build formula expressions using lower level primitives.

Parameters

  • field_values (Dict[str, Any]) – mapping of column names to values (or to 2-tuples of the format (operator, value)).

  • match_any (bool, default: False) – If True, matches if any of the provided values match. Otherwise, all values must match.

Return type

Formula

Compound conditions

Formulas and conditions can be chained together if you need to create more complex criteria:

>>> from datetime import date
>>> from pyairtable.formulas import AND, GTE, Field, match
>>> formula = AND(
...     match("Customer", 'Alice'),
...     GTE(Field("Delivery Date"), date.today())
... )
>>> formula
AND(EQ(Field('Customer'), 'Alice'),
    GTE(Field('Delivery Date'), datetime.date(2023, 12, 10)))
>>> str(formula)
"AND({Customer}='Alice', {Delivery Date}>=DATETIME_PARSE('2023-12-10'))"

pyAirtable has support for the following comparisons:

These are also implemented as convenience methods on all instances of Formula, so that the following are equivalent:

>>> EQ(Field("Customer"), "Alice")
>>> match({"Customer": "Alice"})
>>> Field("Customer").eq("Alice")

pyAirtable exports AND, OR, NOT, and XOR for chaining conditions. You can also use Python operators to modify and combine formulas:

>>> from pyairtable.formulas import match
>>> match({"Customer": "Bob"}) & ~match({"Product": "TEST"})
AND(EQ(Field('Customer'), 'Bob'),
    NOT(EQ(Field('Product'), 'TEST')))

Python operator

Airtable expression

lval & rval

AND(lval, rval)

lval | rval

OR(lval, rval)

~rval

NOT(rval)

lval ^ rval

XOR(lval, rval)

Calling functions

pyAirtable also exports functions that act as placeholders for calling Airtable formula functions:

>>> from pyairtable.formulas import Field, GTE, DATETIME_DIFF, TODAY
>>> formula = GTE(DATETIME_DIFF(TODAY(), Field("Purchase Date"), "days"), 7)
>>> str(formula)
"DATETIME_DIFF(TODAY(), {Purchase Date}, 'days')>=7"

All supported functions are listed in the pyairtable.formulas API reference.