Trying to output linked table data in a nested list


#1

Using PHP and Curl (mainly because I don’t know any better…) I have a page that lists all the “Names” in one table and am attempting to do a nested list of “Orders#” under the “Name” however if I echo the “Orders” field it just outputs the word Array…

I’m assuming I need to make a GET call to the Linked Table I’m just not sure how to do that within the same foreach loop I’m using.

Any help is greatly appreciated!

J Fuller


#2

There’s two ways to do this, depending on how many records you’re really expecting.

  1. Fetch related records one at a time
  2. Build a filterByFormula parameter that uses OR() to get multiple records.

Option 1 would be extremely inefficient. And with option 2 you can request multiple records per call—either all the records associated with one related record, or (as I do in this example) all the records associated with an array of related records.

So, as you’re looping through the records from a table (we’ll call it “People”), you should build an array of the IDs you need to fetch from the related table (we’ll call it “Orders”). For the purposes of this example, I’m going to assume the each record from table People has only several related records in Orders (as opposed to hundreds).

The following code should provide a rough idea of how to do what you’re trying to do with only two requests to the Airtable API. One to fetch all the people, and one to fetch all the orders.

There are two functions below that I don’t define—they’ll need to reference whatever code you use to actually connect to airtable and retreive records.

getSomeRecordsFromAirtable()
getSomeFilteredRecordsFromAirtable()

<?php

    $people = getSomeRecordsFromAirtable();

    $order_ids = array();
    foreach($people as $record){
    	$order_ids = array_merge($order_ids,$record["fields"]["Orders"]);
    }

    $orders_index = getOrdersByID($order_ids);

    foreach($people as $record){
    	$n = $record["fields"];
    	echo $n["Name"];

    	echo "<ul>";
    	foreach($n["Orders"] as $order_id){
    		echo "<li>".$orders_index[$order_id]["fields"]["Column from Orders Table"]."</li>";
    	}
    	echo "</ul>";
    }


    function getOrdersByID($order_ids){
    	$filterByFormula = "OR(RECORD_ID() = '".implode("', RECORD_ID() = '", $order_ids)."')";
    	$orders = getSomeFilteredRecordsFromAirtable("Orders",$filterByFormula);

    	$orders_indexed_by_id = array();
    	foreach($orders as $record){
    		$id = $record["id"];
    		$orders_indexed_by_id[$id] = $record;
    	}
    	return $orders_indexed_by_id;
    }

    ?>

Let me know how it goes!


#3

I would like to ask if it’s possible to post a text value to the airtable record, which will turn on into a link to a table. When I try to post it via urlfetch it returns me an error (something like wrong value…). I saw that this text into link conversion works with Zapier, so I think it’s possible to make.
Any idea how to implement this functionality.


#4

Yes. The text value you use is the Record ID of the record to which you’re “linking”. However, even if you have NOT checked “Allow Linking to Multiple Records”, you must still provide the Record ID in an array.

See the examples for your specific base at http://airtable.com/api

Here’s the basics using curl where both Contacts and Projects fields are linked fields—not the square brackets around the record IDs for those fields, indicating an array:

curl -v -XPOST https://api.airtable.com/v0/appXXXXXXXXXXXX/Tablename \
-H "Authorization: Bearer YOUR_API_KEY" \
-H "Content-type: application/json" \
 -d '{
  "fields": {
    "Name": "Example Name",
    "Contacts": [
      "recXXXXXXXXXXXXX0"
    ],
    "Projects": [
      "recXXXXXXXXXXXXX1",
      "recXXXXXXXXXXXXX2"
    ],
  }
}'