0
# Formula System
1

2
Comprehensive formula building and expression system supporting all Airtable formula functions, logical operators, comparisons, and field references. Enables complex queries and data filtering using Airtable's formula language.
3

4
## Capabilities
5

6
### Formula Construction
7

8
Core classes for building formula expressions with proper escaping, type conversion, and operator support.
9

10
```python { .api }
11
class Formula:
12
    def __init__(self, value: str):
13
        """
14
        Create formula from string expression.
15
        
16
        Parameters:
17
        - value: Formula string (e.g., "{Name} = 'John'")
18
        """
19
    
20
    def flatten(self) -> Formula:
21
        """
22
        Flatten nested boolean statements for optimization.
23
        
24
        Returns:
25
        Optimized Formula instance
26
        """
27
    
28
    def eq(self, value) -> Formula:
29
        """Build equality comparison."""
30
    
31
    def ne(self, value) -> Formula:
32
        """Build not-equal comparison."""
33
    
34
    def gt(self, value) -> Formula:
35
        """Build greater-than comparison."""
36
    
37
    def lt(self, value) -> Formula:
38
        """Build less-than comparison."""
39
    
40
    def gte(self, value) -> Formula:
41
        """Build greater-than-or-equal comparison."""
42
    
43
    def lte(self, value) -> Formula:
44
        """Build less-than-or-equal comparison."""
45

46
class Field(Formula):
47
    def __init__(self, name: str):
48
        """
49
        Create field reference.
50
        
51
        Parameters:
52
        - name: Field name to reference
53
        """
54

55
def to_formula(value) -> Formula:
56
    """
57
    Convert value to Formula object.
58
    
59
    Parameters:
60
    - value: Python value (str, int, bool, datetime, Formula, etc.)
61
    
62
    Returns:
63
    Formula object with proper escaping and type conversion
64
    """
65

66
def to_formula_str(value) -> str:
67
    """
68
    Convert value to formula string.
69
    
70
    Parameters:
71
    - value: Python value to convert
72
    
73
    Returns:
74
    String representation suitable for Airtable formulas
75
    """
76
```
77

78
### Logical Operations
79

80
Boolean logic functions for combining multiple conditions with AND, OR, and NOT operations.
81

82
```python { .api }
83
def AND(*components, **fields) -> Formula:
84
    """
85
    Create AND condition combining multiple expressions.
86
    
87
    Parameters:
88
    - components: Formula objects or values to combine
89
    - fields: Keyword arguments as field=value equality conditions
90
    
91
    Returns:
92
    Formula representing AND condition
93
    """
94

95
def OR(*components, **fields) -> Formula:
96
    """
97
    Create OR condition for alternative expressions.
98
    
99
    Parameters:
100
    - components: Formula objects or values to combine
101
    - fields: Keyword arguments as field=value equality conditions
102
    
103
    Returns:
104
    Formula representing OR condition
105
    """
106

107
def NOT(component=None, **fields) -> Formula:
108
    """
109
    Create NOT condition to negate expression.
110
    
111
    Parameters:
112
    - component: Formula object to negate
113
    - fields: Single field=value as keyword argument
114
    
115
    Returns:
116
    Formula representing negated condition
117
    """
118

119
def match(field_values: dict, *, match_any: bool = False) -> Formula:
120
    """
121
    Create equality conditions for multiple fields.
122
    
123
    Parameters:
124
    - field_values: Dict mapping field names to values or (operator, value) tuples
125
    - match_any: If True, use OR logic. If False, use AND logic
126
    
127
    Returns:
128
    Formula with equality or comparison conditions
129
    """
130
```
131

132
### Mathematical Functions
133

134
Comprehensive set of mathematical operations including arithmetic, statistical, and utility functions.
135

136
```python { .api }
137
# Arithmetic functions
138
def ABS(value) -> Formula:
139
    """Absolute value of number."""
140

141
def SUM(number, *numbers) -> Formula:
142
    """Sum of all numbers."""
143

144
def AVERAGE(number, *numbers) -> Formula:
145
    """Average of all numbers."""
146

147
def MAX(number, *numbers) -> Formula:
148
    """Maximum value from numbers."""
149

150
def MIN(number, *numbers) -> Formula:
151
    """Minimum value from numbers."""
152

153
def COUNT(number, *numbers) -> Formula:
154
    """Count of numeric values."""
155

156
def COUNTA(value, *values) -> Formula:
157
    """Count of non-empty values."""
158

159
# Rounding functions
160
def ROUND(value, precision) -> Formula:
161
    """Round to specified decimal places."""
162

163
def ROUNDUP(value, precision) -> Formula:
164
    """Round up to specified decimal places."""
165

166
def ROUNDDOWN(value, precision) -> Formula:
167
    """Round down to specified decimal places."""
168

169
def CEILING(value, significance=None) -> Formula:
170
    """Round up to nearest multiple of significance."""
171

172
def FLOOR(value, significance=None) -> Formula:
173
    """Round down to nearest multiple of significance."""
174

175
def INT(value) -> Formula:
176
    """Greatest integer less than or equal to value."""
177

178
# Advanced math
179
def MOD(value, divisor) -> Formula:
180
    """Remainder after division."""
181

182
def POWER(base, power) -> Formula:
183
    """Base raised to power."""
184

185
def SQRT(value) -> Formula:
186
    """Square root of value."""
187

188
def EXP(power) -> Formula:
189
    """e raised to power."""
190

191
def LOG(number, base=None) -> Formula:
192
    """Logarithm with optional base (default 10)."""
193

194
def EVEN(value) -> Formula:
195
    """Smallest even integer >= value."""
196

197
def ODD(value) -> Formula:
198
    """Smallest odd integer >= value."""
199
```
200

201
### Text Functions
202

203
String manipulation and text processing functions for working with text fields.
204

205
```python { .api }
206
def CONCATENATE(text, *texts) -> Formula:
207
    """Join text strings together."""
208

209
def LEFT(string, how_many) -> Formula:
210
    """Extract characters from start of string."""
211

212
def RIGHT(string, how_many) -> Formula:
213
    """Extract characters from end of string."""
214

215
def MID(string, where_to_start, count) -> Formula:
216
    """Extract substring from middle of string."""
217

218
def FIND(string_to_find, where_to_search, start_from_position=None) -> Formula:
219
    """Find position of substring (case-sensitive)."""
220

221
def SEARCH(string_to_find, where_to_search, start_from_position=None) -> Formula:
222
    """Find position of substring (case-insensitive)."""
223

224
def LEN(string) -> Formula:
225
    """Length of string."""
226

227
def TRIM(string) -> Formula:
228
    """Remove leading and trailing whitespace."""
229

230
def UPPER(string) -> Formula:
231
    """Convert to uppercase."""
232

233
def LOWER(string) -> Formula:
234
    """Convert to lowercase."""
235

236
def SUBSTITUTE(string, old_text, new_text, index=None) -> Formula:
237
    """Replace occurrences of old_text with new_text."""
238

239
def REPLACE(string, start_character, number_of_characters, replacement) -> Formula:
240
    """Replace portion of string."""
241

242
def REPT(string, number) -> Formula:
243
    """Repeat string specified number of times."""
244

245
def REGEX_MATCH(string, regex) -> Formula:
246
    """Test if string matches regular expression."""
247

248
def REGEX_EXTRACT(string, regex) -> Formula:
249
    """Extract first substring matching regex."""
250

251
def REGEX_REPLACE(string, regex, replacement) -> Formula:
252
    """Replace all regex matches with replacement."""
253

254
def ENCODE_URL_COMPONENT(component_string) -> Formula:
255
    """URL-encode string for use in URLs."""
256
```
257

258
### Date and Time Functions
259

260
Comprehensive date/time manipulation including formatting, parsing, and arithmetic operations.
261

262
```python { .api }
263
def NOW() -> Formula:
264
    """Current date and time."""
265

266
def TODAY() -> Formula:
267
    """Current date (time set to midnight)."""
268

269
def DATEADD(date, number, units) -> Formula:
270
    """Add time units to date."""
271

272
def DATETIME_DIFF(date1, date2, units) -> Formula:
273
    """Difference between dates in specified units."""
274

275
def DATETIME_FORMAT(date, output_format=None) -> Formula:
276
    """Format date as string."""
277

278
def DATETIME_PARSE(date, input_format=None, locale=None) -> Formula:
279
    """Parse string as date."""
280

281
def DATESTR(date) -> Formula:
282
    """Format date as YYYY-MM-DD string."""
283

284
def TIMESTR(timestamp) -> Formula:
285
    """Format time as HH:mm:ss string."""
286

287
def SET_TIMEZONE(date, tz_identifier) -> Formula:
288
    """Set timezone for date (use with DATETIME_FORMAT)."""
289

290
def SET_LOCALE(date, locale_modifier) -> Formula:
291
    """Set locale for date (use with DATETIME_FORMAT)."""
292

293
# Date component extraction
294
def YEAR(date) -> Formula:
295
    """Extract year from date."""
296

297
def MONTH(date) -> Formula:
298
    """Extract month from date (1-12)."""
299

300
def DAY(date) -> Formula:
301
    """Extract day from date (1-31)."""
302

303
def WEEKDAY(date, start_day_of_week=None) -> Formula:
304
    """Day of week (0-6, Sunday=0)."""
305

306
def WEEKNUM(date, start_day_of_week=None) -> Formula:
307
    """Week number in year."""
308

309
def HOUR(datetime) -> Formula:
310
    """Extract hour from datetime (0-23)."""
311

312
def MINUTE(datetime) -> Formula:
313
    """Extract minute from datetime (0-59)."""
314

315
def SECOND(datetime) -> Formula:
316
    """Extract second from datetime (0-59)."""
317

318
# Date comparisons
319
def IS_BEFORE(date1, date2) -> Formula:
320
    """Check if date1 is before date2."""
321

322
def IS_AFTER(date1, date2) -> Formula:
323
    """Check if date1 is after date2."""
324

325
def IS_SAME(date1, date2, unit) -> Formula:
326
    """Check if dates are same up to time unit."""
327

328
# Relative dates
329
def FROMNOW(date) -> Formula:
330
    """Days from current date to specified date."""
331

332
def TONOW(date) -> Formula:
333
    """Days from specified date to current date."""
334

335
# Business date functions
336
def WORKDAY(start_date, num_days, holidays=None) -> Formula:
337
    """Add working days to date, excluding weekends and holidays."""
338

339
def WORKDAY_DIFF(start_date, end_date, holidays=None) -> Formula:
340
    """Count working days between dates."""
341
```
342

343
### Conditional and Utility Functions
344

345
Control flow, data type handling, and utility functions for complex formula logic.
346

347
```python { .api }
348
def IF(expression, if_true, if_false) -> Formula:
349
    """Conditional expression - return if_true if expression is true."""
350

351
def SWITCH(expression, pattern, result, *pattern_results) -> Formula:
352
    """Switch statement with pattern matching."""
353

354
def ISERROR(expr) -> Formula:
355
    """Check if expression causes an error."""
356

357
def BLANK() -> Formula:
358
    """Return blank/empty value."""
359

360
def ERROR() -> Formula:
361
    """Return error value."""
362

363
def TRUE() -> Formula:
364
    """Boolean true value."""
365

366
def FALSE() -> Formula:
367
    """Boolean false value."""
368

369
def T(value) -> Formula:
370
    """Return value if it's text, blank otherwise."""
371

372
def VALUE(text) -> Formula:
373
    """Convert text to number."""
374

375
def RECORD_ID() -> Formula:
376
    """ID of current record."""
377

378
def CREATED_TIME() -> Formula:
379
    """Creation time of current record."""
380

381
def LAST_MODIFIED_TIME(*fields) -> Formula:
382
    """Last modification time of record or specific fields."""
383
```
384

385
### Usage Examples
386

387
#### Basic Formula Construction
388

389
```python
390
from pyairtable import Api
391
from pyairtable.formulas import Formula, Field, match, AND, OR
392

393
api = Api('your_token')
394
table = api.table('base_id', 'table_name')
395

396
# Simple string formula
397
formula = Formula("{Status} = 'Active'")
398
records = table.all(formula=formula)
399

400
# Using Field objects
401
name_field = Field('Name')
402
status_field = Field('Status')
403
formula = AND(
404
    name_field.eq('John'),
405
    status_field.ne('Inactive')
406
)
407

408
# Using match() helper
409
formula = match({
410
    'Department': 'Engineering',
411
    'Level': ('>=', 5),  # Comparison tuple
412
    'Status': 'Active'
413
})
414
```
415

416
#### Complex Logical Expressions
417

418
```python
419
from pyairtable.formulas import AND, OR, NOT, match
420

421
# Complex conditions
422
engineering_seniors = AND(
423
    match({'Department': 'Engineering'}),
424
    OR(
425
        match({'Level': ('>=', 5)}),
426
        match({'Title': 'Senior Engineer'})
427
    )
428
)
429

430
# Exclusion patterns
431
not_interns = NOT(match({'Title': 'Intern'}))
432

433
# Multiple department filter
434
tech_departments = OR(
435
    match({'Department': 'Engineering'}),
436
    match({'Department': 'Data Science'}),
437
    match({'Department': 'DevOps'})
438
)
439

440
records = table.all(formula=AND(engineering_seniors, not_interns))
441
```
442

443
#### Mathematical Formulas
444

445
```python
446
from pyairtable.formulas import SUM, AVERAGE, IF, ROUND
447

448
# Calculate totals and averages
449
total_formula = SUM(Field('Q1_Sales'), Field('Q2_Sales'), Field('Q3_Sales'), Field('Q4_Sales'))
450

451
# Conditional calculations
452
bonus_formula = IF(
453
    Field('Sales_Total').gt(100000),
454
    Field('Sales_Total') * 0.1,  # 10% bonus
455
    0
456
)
457

458
# Rounded averages
459
avg_score = ROUND(AVERAGE(Field('Score1'), Field('Score2'), Field('Score3')), 2)
460
```
461

462
#### Date and Time Operations
463

464
```python
465
from pyairtable.formulas import DATEADD, DATETIME_DIFF, DATETIME_FORMAT, IS_AFTER
466
import datetime
467

468
# Date arithmetic
469
deadline_formula = DATEADD(Field('Start_Date'), 30, 'days')
470

471
# Date comparisons
472
overdue_formula = IS_AFTER(Formula('TODAY()'), Field('Due_Date'))
473

474
# Date formatting
475
formatted_date = DATETIME_FORMAT(Field('Created_Date'), 'MM/DD/YYYY')
476

477
# Time calculations
478
project_duration = DATETIME_DIFF(Field('End_Date'), Field('Start_Date'), 'days')
479
```
480

481
#### Text Processing
482

483
```python
484
from pyairtable.formulas import CONCATENATE, UPPER, LEFT, FIND
485

486
# String concatenation
487
full_name = CONCATENATE(Field('First_Name'), ' ', Field('Last_Name'))
488

489
# Text formatting
490
formatted_code = CONCATENATE('ID-', UPPER(LEFT(Field('Name'), 3)))
491

492
# Text search and validation
493
has_email = FIND('@', Field('Email')).gt(0)
494
```
495

496
#### Advanced Pattern Matching
497

498
```python
499
# Using match() with multiple operators
500
advanced_filter = match({
501
    'Salary': ('>', 75000),
502
    'Experience': ('>=', 3),
503
    'Department': 'Engineering',
504
    'Status': 'Active'
505
}, match_any=False)  # All conditions must match
506

507
# Alternative matching (any condition)
508
priority_candidates = match({
509
    'Referral': True,
510
    'Experience': ('>', 10),
511
    'Education': 'PhD'
512
}, match_any=True)  # Any condition matches
513

514
# Combining with other logic
515
final_filter = AND(
516
    advanced_filter,
517
    OR(priority_candidates, match({'Urgency': 'High'}))
518
)
519
```
520

521
#### Field Reference Utilities
522

523
```python
524
# Utility functions for field names and escaping
525
from pyairtable.formulas import field_name, quoted
526

527
# Handle field names with special characters
528
special_field = field_name("Field with spaces & symbols")
529
# Results in: {Field with spaces & symbols}
530

531
# Properly quote string values
532
search_value = quoted("Text with 'quotes' and \\ backslashes")
533
# Results in: 'Text with \'quotes\' and \\\\ backslashes'
534

535
# Convert Python values to formula strings  
536
from pyairtable.formulas import to_formula_str
537

538
formula_string = to_formula_str(datetime.date(2023, 12, 25))
539
# Results in: DATETIME_PARSE('2023-12-25')
540
```