Mar 12, 2022 05:46 AM
Hi everyone! May be a confusing title but here’s my issue:
I am using Airtable API to build in Appgyver. I am trying to use the filterByFormula to pull records with a specific linked ID. However, it was suggested to me in another forum that the reason it’s not working is that the ID is being returned as a list rather than an object (see below):
I thought the reason it was showing as a list was because I initially had it to be able to link to multiple records, but then I changed that to single record and it still show up as a list with the brackets.
IS the advice I’ve been given correct? And if so, how do I change it to show as an “object” so the filterByFormula can find it?
Mar 12, 2022 10:49 AM
Welcome to the Airtable community!
It looks like you are using the REST API. In the REST API, linked records are always an array of record ids. You cannot change this.
The confusion may stem from the fact that linked record fields are treated differently in different places. In scripting and custom apps, linked record fields are arrays of objects.
However, when using
filterByFormula with the REST API, in general, you do not use record IDs. You use a value that is a valid Airtable formula, which is going to be based on the primary field of the linked record.
Mar 16, 2022 04:40 PM
Thank you for the reply! The explanation around linked records makes sense.
Perhaps I can tell you my challenge and you can point me in the right direction?
I am trying to use the filterByFormula in Appgyver to return a set of records in a table that are all linked to the same record in another table. In other words, I have an ‘Albums’ table and an ‘Artist’ table, and I want to be able to return all the albums by a specific artist, but I need the ‘Artist’ value to be dynamic.
To do that, I have created page parameters that pass along the ArtistID and ArtistName. When I try to search by ArtistID it doesn’t work at all (hence my question above); when I filter by ArtistName it actually works as a static value but as soon as I try to bind the value to the page parameter it doesn’t work.
In other words, this works: filterByFormula: ArtistName=‘The Beatles’. It returns all the Beatles albums.
But this does not work: filterByFormula: ArtistName=params.ArtistName. This returns nothing.
Is there something wrong with my syntax? I try the same with artist ID but neither work and both page parameters are active. I’m pulling my hair out over this so any help you can offer would be greatly appreciated!
Mar 16, 2022 04:47 PM
Notice that in your example with the static value, you have quotes around the name. Your formula is
ArtistName='The Beatles' with quotes around the actual artist name.
However, when you use a variable for the artist name, you do not have quotes around the name. The final formula is something like
ArtistName=The Beatles, without the quotes, which probably isn’t a valid formula.
Try adding the quotes. If you have quotes in the artist name, you’ll also need to escape those quotes (or use an alternative quote style).
Mar 16, 2022 09:03 PM
Unfortunately adding the quotes does not change the results for me. Is there standard syntax for the filterByFormula when using dynamic data?
Mar 16, 2022 09:51 PM
When using filterByFormula with dynamic data, you need to manipulate the data so that the final string sent to filterByFormula is a valid Airtable formula. Typically this means that the actual final formula looks like it has hardcode values.
Are you able to see the actual final string that is being passed to filterByFormula, both before and after url encoding? Can you include screen captures?