Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Return multiple records for linked table

Topic Labels: API
4298 9
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi, I’ve got 3 tables

i’d like to be able to return records from a linked table based on id’s.

i’ve been trialling the filterby formula function to retreive a record by ID but how do you do this by multiple id.s

eg. 1 record from a table looks like this

“id”: “recOW2bVrD0EuXF7M”,
“fields”: {
“country”: “Australia”,
“continent”: “Australia and New Zealand”,
“partner_universities”: [
“recS7EnXzspRowVfb”,
“recVpXFu93umekVij”,
“reckcPSPWYAAzl7Ld”,
“recUe3go5fHBqR1a8”,
“recwzDNlgGOcuRrRY”
],
“subject_university”: [
“rec4uL4PmXuO4UzMO”,
“recNGBHjUh4yVzXp3”,
“recc3ovVUYJ6b4qDD”,
“recKv6l77p95Gn3im”,
“rec5i1AgZwl5pdV4Y”,
“recEpoJL6G0hxWxeZ”,
“rec8KZDmdT8aotNdB”,
“recmxAJYdxIhpmBFR”,
“recx12pYZAcJLRCjv”,
“recX6TGusfr1EoJVC”,
“recnYZoCmrnWeLuG1”,
“rectMdcCx3wWvXaS2”,
“rec2XlS0dGmcdZxvx”,
“rece2IYb8aCbMiqQl”,
“recWeabmWidCnrvff”,
“recmHycR4LgyKrxl6”,
“rectGeLjBG9lIfJhG”,
“recsxDGN1LUn4Yt5K”,
“recvW3zithBqZqvRI”,
“recQVWTz8IdwIeCgf”,
“recMzzvoma0Mg2nKv”,
“recoEiEKLDXGZbKfm”,
“recp37vk0eogqnNDS”,
“recSmJNDEPKUVyZSP”,
“recBZXaN868adJFWZ”,
“recvDQLTpEzbMa1rZ”,
“rec2SLONiYnrDFioz”,
“rect2QWp5f04NmDra”,
“recO8W9vcWfjhwCa8”,
“recTSer5vqPuhsJwf”,
“recNnNgOArJW1Y05a”,
“recxpPZSFJxBkdpbQ”,
“recRWyPisPdA7ls5k”,
“recXC4chtGPKfT6ZF”,
“rec1Lp8ds9dOTP7vi”,
“recobTIHY9Z18mNwe”,
“recqc79rPJfaE7n37”,
“recsXy78pq9kOPizX”,
“recX2ANpxGlx77iAv”,
“recrn8yDUJXflJDxw”,
“recYislCiUEabZUzB”,
“recLW6IgIxKZvVcL9”,
“recXrTwO8PlerD5v0”,
“recIjL4LFhabuh1Jo”,
“recazTo8U9nq9oCrv”,
“recFn0KgnYpG0IwOM”,
“recs42G6dTGNImy7u”,
“recD9NJamBJsbSAVb”
]

then i want to use filterbyforumla to retrieve records from the subject_universities table based on that id

9 Replies 9

I suppose if i query using all those ID’s i might hit a character limit

I have hit a limit—I haven’t tested enough to know if it’s a timeout or a character limit. Either way, I’ve adjusted my scripts to “chunk” my requests into 250 record ids at a time and then join them into a single result set.

250 sounds loads. Is that in a single request through a URL?

e.g RECORD_ID()=‘xxx’,RECORD_ID()=‘xxx’,RECORD_ID()=‘xxx’,RECORD_ID()=‘xxx’…

Yup. Here is a sample request (non url encoded for readability). You can see how I cram a ton of record IDs into an OR statement and I wrap the OR inside an AND statement so I can include other conditions in the filterByFormula

Trips?sort[0][field]=Start Date&sort[0][direction]=asc&filterByFormula=AND(IS_BEFORE(TODAY(),{End Date}),NOT({Trip ID}=''),OR({Registration Status}='active',{Registration Status}='pending'),OR(RECORD_ID()='recdue1JThlUfZAxN', RECORD_ID()='recQonQJr8LSTvVRX', RECORD_ID()='rech8det0nyYKfjHr', RECORD_ID()='recmZsmS5dviRdjtD', RECORD_ID()='recMV0Od7LNtnHKKG', RECORD_ID()='rech4X512symMc5ju', RECORD_ID()='reco5OSWGT5kvl0Q5', RECORD_ID()='rec4pV2zFV5GDux9H', RECORD_ID()='recxCKkNpqoXQRZLz', ... 200+ more..., RECORD_ID()='recqlJ1B4AQfGdAs7'))

At some point I’ll try to do a more thorough test, but for now I just ran a request (in the same format as my previous reply) with 620 unique record IDs that weighed in at 20kb and it was fine. I also doubled it (sent each ID twice in the same request, so 1240 record IDs) and it was fine.

Also, I noted the time each request took to respond and it was all over the place between 1500 ms and 4600 ms.

excellent.Thanks for the great response!

works a treat

Hi Chester,
This is incredibly helpful.

QuickNubeQuestion: What is the best way to transform the linked table array (“subject_university”) into a use-able string (ie. RECORD_ID()=‘recdue1JThlUfZAxN’, RECORD_ID()=‘recQonQJr8LSTvVRX’,) ?

…without having to write all this in js like so:
let filterString = ‘’;
myArrOfIds.forEach((recordId, index) => {
filterString += (RECORD_ID() = '${recordId}');
if (index < (myArrOfIds.length - 1)) {
filterString += (’, ‘);
} else {
filterString += (’)’);
}
});

We’re just trying to sort & split linked fields in a table, with either Zapier or Integromat.
Any help would be much appreciated.

let myArray = ["id1","id2","id3"]

let myString = "OR( RECORD_ID() = '" + myArray.join("', RECORD_ID() = '") +"')";

console.log(myString)

document.write(myString)

BTW.,. I ended up writing a couple of functions to help me with this

// Builds URL querystring to be used in getLinkedTable function

function buildURLstring(recordArr) {
return recordArr
.map(record => {
return RECORD_ID()='${record}',;
})
.join(’’)
.slice(0, -1);
}
// Will return data from linked table - pass in table and array of ID’s you want to return against
export function getLinkedTable(table, recordArr) {
const filterForumularstr = buildURLstring(recordArr);
return axios.get(
${settings.API_URL}${settings.AIRTABLE_ID}/${table}?filterByFormula=OR(${filterForumularstr})
);
}