What language are you using (or anticipate using) to access the API?
I’ve written a PHP API wrapper for Airtable that is highly customized and specific to my particular needs, however one concept that is worth passing along is how I fetch “related records”. Until Airtable releases a method for fetching related records “automatically” via the API, a person must manually retrieve related records by RECORD_ID().
This could result in many individual API calls and become excessively slow. By using the filterByFormula parameter with the OR() condition, it’s possible to get a bunch of records from TABLE1 and then in a single API call, get ALL related records from TABLE2.
Here’s the jist of the code I use—obviously there’s more going on behind the scenes, but this illustrates the concept:
<?php
$table = "Orders";
$filterByFormula = "{Order Date} > '2016-01-01'";
$params = array("filterByFormula" => $filterByFormula);
$orders = $airtable->getTable($table,$params);
$table = "Items";
$relatedTo = array($orders,"Items");
$params = array("relatedTo" => $relatedTo);
$items = $airtable->getTable($table,$params);
$orders = $airtable->joinRecords($orders,"Items",$items);
?>
As you may surmise, the getTable function looks for my custom $relatedTo parameter (which is NOT a part of the Airtable API) and creates something like this by looping through the Orders and snagging the RECORD_ID() from each ‘Items’ field:
<?php filterByFormula = "OR(RECORD_ID() = 'xxxxxxxxxxxxxxx',RECORD_ID() = 'xxxxxxxxxxxxxxx',...)";?>
Here’s another example, this time with more filtering:
<?php
$table = "Items";
$relatedTo = array($orders,"Items");
$filterByFormula = "{Item Cost} > 5";
$fields = array("Item Name", "Item Cost", "Inventory ID");
$params = array("relatedTo" => $relatedTo, "filterByFormula" => $filterByFormula, "fields" => $fields);
$items = $airtable->getTable($table,$params);
?>
And then the getTable method would produce something like:
<?php filterByFormula = "AND({Item Cost} > 5,OR(RECORD_ID() = 'xxxxxxxxxxxxxxx',RECORD_ID() = 'xxxxxxxxxxxxxxx',...))";?>
The joinRecords() method could do something like this:
<?php
function joinRecords($table1,$table1_related_field_to_table2,$table_2){
$table2_index = array();
foreach($table2 as $record){
$id = $record["id"];
$table2_index[$id] = $record;
}
foreach($table1 as &$record){
$related_ids = $record["fields"][$table1_related_field_to_table2];
$records = array();
foreach($related_ids as $id){
$records[] = $table2_index[$id];
}
$record["fields"][$table1_related_field_to_table2] = $records;
}
return $table1;
}
?>
That joinRecords function is precarious though, because:
- It’s possible that not all table2 records referenced inside table1 were retrieved due to the filterByFormula, so while there may have been 10 related record IDs, only 4 related records where joined.
• The sort order must either be the order in which the “related” IDs were sorted in Table1 or the order in which the records were retrieved from Table2. For example, if you sort your table2 (Items) results, but then simply loop through the record IDs inside the table1 field, you’ll loose that sorting.
So essentially, my point is that the Airtable API provides enough filtering, sorting, etc parameters to create quite complex and targeted “queries” or requests—and by creating a wrapper that meets your specific needs, you can quickly retrieve just about any data you want.
An example of this is that I don’t need paging. I only request records I want and I request them all at the same time. If I asked for more than Airtable’s default paging allows, then my wrapper automatically loops using the returned offset and makes a new request. When it has fetched all matching records, it returns them. This could be terrible and dangerous for some applications, but for me, it’s fine. This is one example of how/why creating your own wrapper for the API is a good way to go for now. Maybe someday someone will create a much more robust API wrapper for the language you’re using that comes with a variety of methods to fetch, sort, relate, and cache data.
