Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jul 25, 2016 08:38 PM
I’m an amateur web designer / novice web developer. I’m self-taught and I really want to get inspired to learn more. I love AirTable, but I find it’s lack of reports capability rather limiting.
I really enjoy looking at examples of what people have done to get inspiration. Also, if people have some templates of things they have done with the API, that would significantly jump start my learning.
Thanks!
Jul 27, 2016 11:58 AM
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.
Jul 27, 2016 01:19 PM
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.
Jul 27, 2016 11:45 PM
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!
Aug 06, 2016 08:28 PM
I just found some really good examples of using AirTable’s API, created by Tim Dietrich. http://timdietrich.me/airtable/api-resources.php
Aug 18, 2016 11:39 AM
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:
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.
Aug 20, 2016 01:51 PM
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!