How to get linked record data using the REST API?

[Dev_Local]

I have a table/sheet that has a field that links to records in another table (parent/child).

When I use the REST API and do a HTTP GET (url, params, headers), and look at the response, the only thing I see in the field with the link is the AT record identifier like:

[ 'recPKTGm6EPiDoS6g' ]

No data from the linked record is present.

I have searched all over the response, and I do not see that there is enough information to use this AT record id to get the data of the record it refers to.

------------------------------------------------------------------------------------------------------------------------

In the UI, if I click on the field in the sheet, I get a popup that shows the full record (all data fields), and clicking on the down arrow next to the key at the top, I can see an option: “Copy Record URL”, and it looks like this:

https://airtable.com/tblPlziY7QNosBG3N/recPKTGm6EPiDoS6g

And so I see there is a uniform URL format for referencing a record with that URL.
https://airtable.com/<at_table_identifier>/<at_record_identifier>

------------------------------------------------------------------------------------------------------------------------

With the REST API, I want to have access to the AT table identifier in the response, and along with the record identifier that I get in the same response, I want to do another HTTP GET, and get that record (data), in JSON format to parse and use.

Even with the table identifier, (If I manually copied it, made a note of it, and put it in my code), I’m not seeing, that along with a record identifier, I can do a GET with the REST API, and get the data.

There is nothing in the Airtable REST API documentation that indicates that linked Record data can be obtained.

------------------------------------------------------------------------------------------------------------------------

Any help, actual working code would be appreciated.

I realize there may be a way to do this with the Node SDK, and I will give this a try next, but I am exploring for the moment how to HTTP GET the data, as we have a specific use case for constructing a URL that links to specific a record, and having that for our use case.

------------------------------------------------------------------------------------------------------------------------

If I copy the URL I get from the record, and paste into another browser tab, the data/pop up is there.

So therefore there must be a way to obtain this through GET, but I do not know what the headers or request would look like to do this, as the URL does not include: https://api.airtable.com, but rather just:

https://airtable.com/<at_table_identifier>/<at_record_identifier>

------------------------------------------------------------------------------------------------------------------------

How, with a first request, do I get the <at_table_identifier> in the first response, and use that to do another GET after constructing this URL?

[JonathanBowen]

Hi @Dev_Local - you don’t need the table identifier to get the linked record data. I’ve made a base with People and Departments tables:

The Department field is a linked record. Here’s a Python script that gets the People records and, for each ID in the department field, gets the matching Department record. You can then merge to two objects to get a combined object:

import json
import requests

get_url = 'https://api.airtable.com/v0/YOUR_BASE_ID/People'
get_headers = {
    'Authorization': 'Bearer YOUR_API_KEY'
    }

response = requests.get(get_url, headers=get_headers)
data = response.json()

# for each record in the people table
for i in data['records']:
    # get the department record ID
    dept_id = i['fields']['Department'][0]
    # now get the deprtment record
    dept_url = 'https://api.airtable.com/v0/YOUR_BASE_ID/Departments/' + dept_id
    dept_response = requests.get(dept_url, headers=get_headers).json()
    people = i['fields']
    dept = dept_response['fields']
    # make a department name key as the Name key conflicts with people['Name']
    dept['Department Name'] = dept.pop('Name')
    # merge the two objects
    people.update(dept)
    print(people)

You could implement a similar process in Node or other programming language.

JB

[Krishangi_Goswa]

Hi Jonathon, I am new to Airtable and this really helped. Thank you so much!

Addressing to anyone who might be able to help :
However, this fetches only the first 100 records and am reading a lot about offset and pagination but I am unable to figure how to incorporate in into say your existing code. Could you help?

get_url = ‘https://api.airtable.com/v0/BASE_ID/TABLE_NAME’
get_headers = {
‘Authorization’: ‘Bearer API_KEY’ }

Response = requests.get(get_url, headers=get_headers)
Response_Table = Response.json()

Thank you so much in advance!

[Krishangi_Goswa]

Update :
I am trying this way -

>get_url = 'https://api.airtable.com/v0/appXXXXXXXXXX/TABLE_NAME'
get_headers = {
    'Authorization': 'Bearer keyXXXXXXXXX' ,
    'Offset' : 'itrXXXXXXX/recXXXXXXX'

}

try :
    Response= requests.get(get_url, headers=get_headers})
    Response_Table = Response.json()
    #print(Response_Table)
    
except error as e:
    print(e)

And I get the error
{'error': {'type': 'INVALID_OFFSET_VALUE', 'message': 'The value of offset ‘itrXXXXXX/recXXXXXX’ is invalid'}}

I would appreciate any leads. Thank you!

[Mike_Pennisi]

Hi @Krishangi_Goswami

When asking for help, be sure to share the code exactly as you have executed it. The example you shared has some syntax errors, so it could not have produced the error you’ve reported. This makes it difficult for us to give advice because we can’t be sure exactly what is going wrong.

Having said that, I can try to clear a few things up for you.

The offset is a value provided by the Airtable service when there are more records than can fit in a single response. You use it to make additional queries and retrieve the records that couldn’t fit. Because of this, your very first request will never include an offset.

Also, offset should be specified as a query string parameter in the request’s URL (not as a request header value).

Here’s an example of how it could work in Python:

import json
import requests

get_url = 'https://api.airtable.com/v0/appXXXXXXXXXX/TABLE_NAME'
get_headers = {
    'Authorization': 'Bearer keyXXXXXXXXX'
}

# The very first time we make a request, `params` will be an empty object.
# That's because we don't need any query string parameters for the first
# request.
params = {}

try:
    while True:
        response = requests.get(get_url, params=params, headers=get_headers)

        response_data = response.json()

        print(json.dumps(response_data, indent=2))

        # If the response does not have an `offset` property, then there are no
        # more records. We can stop making requests by using the `break`
        # statement to exit the `while` loop.
        if 'offset' not in response_data:
            break

        # If the response *does* have an `offset` property, then there are
        # still more records in the table. We'll set the `params` variable and
        # let the `while` loop run again. This time, because `params` is an
        # object with a property named `offset`, the new offset value will be
        # included as a query string parameter of the request's URL
        params = {'offset': response_data['offset']}
except Exception as e:
    print(e)

[Artur_Mkrtchya1]

Hi @JonathanBowen thanks for the example.

It works as you know the referencing table name (in your case Departments) and you construct it correctly. We have cases where we know about main table name but not the referencing ones hence it feels almost impossible to retrieve referencing data.

Any recommendations how to proceed ?

[JonathanBowen]

@Artur_Mkrtchyan - this post might help you:

Accessing the metadata API API

Hi everyone, As part of our ongoing effort to make Airtable more and more extensible, we’re beginning to expand access to the Metadata API. The metadata API gives you read-only access to a list of a user’s bases, as well as the specific schema of each base - things like what tables exist, what fields exist on those tables, etc. Usage of this API currently requires a unique developer token, in addition to the user’s API key for whom you are looking to pull the metadata. You can view the metada…

Airtable is now making the base schema available via the Metadata API, so you might be able to use this to better understand the structure of the base you’re querying.

[Artur_Mkrtchya1]

Thanks @JonathanBowen did check that too :slightly_smiling_face: still pretty limited, hope the api will be evolving overtime.

[Eric_Goldman1]

Hey @Artur_Mkrtchyan,

We hit these exact pain points - so we built a product so you can just query Airtable with SQL:

syncinc.so

Sync Inc

Query Airtable with all the power of SQL.

It should make running the queries you are looking for pretty simple. Would love to hear what you think.

Eric