Help

Re: Return expanded Linked Table data of a field via API call

1366 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Andrew_Bredon
4 - Data Explorer
4 - Data Explorer

Hi,
Is it possible to return the Linked table children of a table from List Rest call? At the moment a list call seems to return an array of Id’s - is it possible to expand that to include the data from the linked table? The only solution I can see is to iterate through the array of ids and make a Retrieve a Record call for each id of the Linked Field…

e.g. in Ingredient below it returns the an array with Id:recEehttf17ZzJYwq… Could that expand to include the fields from the linked table Ingredient?

{
“id”: “recxTeOfNbAhcTPYS”,
“fields”: {
“Id”: 1,
“Ingredient”: [
“recEehttf17ZzJYwq”
],
“Price”: 14.5,
“Date”: “2017-04-01T11:44:00.000Z”
},
“createdTime”: “2017-04-01T10:40:34.932Z”
}

Thanks,
Andrew

8 Replies 8

A second request to the API is currently required to fetch linked records. Rather than iterating through all the associated IDs, use a single request with the “or” and “record_id” like this:

filterByFormula = "OR( RECORD_ID() = 'recXXXXXX', RECORD_ID() = 'recXXXXXX')"

Andrew_Bredon
4 - Data Explorer
4 - Data Explorer

amazing… thanks that looks like it will solve…

Any advice on how to do this in nodejs? I’m a beginner to web app development and can’t figure out how to take the existing advice for listing linked records and make it work in JavaScript… Also, do the actual record IDs need to be specified when making the second request to the API?

Any guidance would be greatly appreciated!!

Probably too late for the answer to be useful for you, but below is some boilerplate code (node) that might help others. Just pass the filterByFormula param in the airtable api call. The example is straight from the API docs.

var Airtable = require('airtable');
var base = new Airtable({apiKey: 'YOUR_API_KEY'}).base('appu6DGFWol46Ftnu');

base('Episodes').select({
    // Selecting the first 3 records in Grid view:
    maxRecords: 3,
    view: "Grid view",
    filterByFormula = filterString // Cf. previous post
}).eachPage(function page(records, fetchNextPage) {
    // This function (`page`) will get called for each page of records.

    records.forEach(function(record) {
        console.log('Retrieved', record.get('Name'));
    });

    // To fetch the next page of records, call `fetchNextPage`.
    // If there are more records, `page` will get called again.
    // If there are no more records, `done` will get called.
    fetchNextPage();

}, function done(err) {
    if (err) { console.error(err); return; }
});

Where the different recXXXXXXs are the respective ids of the records from your linked base.

Keep in mind that you will have to build the filter string yourself. Something like this should work :

let filterString = '';
myArrOfIds.forEach((recordId, index) => {
        filterString += (`RECORD_ID() = '${recordId}'`);
        if (index < (myArrOfIds.length - 1)) {          
          filterString += (', ');
        } else {          
          filterString += (')');
        }      
      });

Hope it helps.
Cheers.

Marc_Vilella
5 - Automation Enthusiast
5 - Automation Enthusiast

Let’s say the result is an array of entries, and each one contains reference to linked data. Something like this:

[
    {
        “id”: “recxTeOfNbAhcTPYS”,
        “fields”: {
            “Id”: 1,
            “Ingredient”: [
                “recEehttf17ZzJYwq”
            ],
            “Price”: 14.5,
            “Date”: “2017-04-01T11:44:00.000Z”
        },
        “createdTime”: “2017-04-01T10:40:34.932Z”
    },
    {
        “id”: “recxTeOfNbAhcTPYu”,
        “fields”: {
            “Id”: 2,
            “Ingredient”: [
                “recEehttf17ZzJYwq”,
                “recEehtt3hi7dJYwq”
            ],
            “Price”: 20.5,
            “Date”: “2017-04-01T11:44:00.000Z”
        },
        “createdTime”: “2017-04-01T10:40:34.932Z”
    },
    ...
]

Then having to query once per element to get the ingredients names will surely break the 5 queries per second limit. I think the option for expanded results is a must.

shellbags
4 - Data Explorer
4 - Data Explorer

Hey there, sorry to bump this old topic but I wanted to ask if this feature was on the roadmap? As @Marc_Vilella said, for complex databases it would be time and resource consuming to recursively pull all linked information.

Thanks in advance!

While we wait for a feature that might never come, here are two workarounds I found:

1. Create Lookup fields that display the values you need from the linked table.
Of course, this might not be feasible if you have too many fields.
I’ve also run into issues like the formatting of rich text fields getting lost on the Lookup field.

2. Filter you records in code and not via API calls
Instead of sending a request for every ID, just get all the records from the reference table, store them in an array and filter through them in your code. This will also be faster than sending hundreds of request.

Thanks for the suggestion @ROARK, I think I’ll go for your second solution then!