Return multiple records for linked table

[Gavin_Thomas]

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

[Gavin_Thomas]

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

[Chester_McLaugh]

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.

[Gavin_Thomas]

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’…

[Chester_McLaugh]

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'))

[Chester_McLaugh]

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.

[Gavin_Thomas]

excellent.Thanks for the great response!

works a treat

[Casey_Jeppesen]

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.

[Chester_McLaugh]

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

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

console.log(myString)

document.write(myString)

[Gavin_Thomas]

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})
);
}