API Templates and Examples

I’m working on a web service tool for AirTable.

It will allow people to define tasks in the form of a simple custom scripting language.

One of the features is an export to XML function. Combined with XSLT this can provide pretty convincing reports.

At the moment it’s all pre-Alpha, so there’s not a lot to see, but plans are slowly evolving to find partners in the community.

Long term, possibly early next year, I’m planning to open source it.

That sounds really cool! Will the web service be managed by a front-end interface or solely back end? (Excuse me if I’m using these terms incorrectly, but what I’m trying to ask is will there be a GUI or not?). If there is a front-end aspect, I would be happy to volunteer effort on the user interface design if it helps speed it up.

Thanks for the offer! But…

The only user interface right now is one webpage with debug info for myself. :slightly_smiling_face:

The way I’ve set this up is by using AirTables. Every client creates a separate base which stores the tasks and base (API / APP) info for their data bases. My master base stores the keys for all client task bases.

So right now there’s no real need for additional UI. In the future there might be an option for clients to develop and test their scripts in a more user friendly way (custom webpage) though. But right now I do not think that will be anything terribly sophisticated.

I’ll keep you in mind though. Cheers!

I just found some really good examples of using AirTable’s API, created by Tim Dietrich. http://timdietrich.me/airtable/api-resources.php

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.

<?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);
?>

<?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);
?>

<?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;
}

?>

I’m working hard on learning more languages and becoming better at the technical aspects, but I’m self taught in HTML, CSS, JS, PHP.

For this situation, I figured that if PHP didn’t do the trick, I would try to learn whatever language most people recommended.

Thank you so much for all those details and examples, it is very helpful!