Help returning lookup field value in URL parameter

[Tim_Bruce]

To minimise duplication of data across my table I’ve converted a large number of Single Line Text fields to Lookup fields linking from a central sheet. Unfortunately the main use case for this data was access via URL parameter filterbyformula method from the branched sheets (this is important as there are some unique values on these sheets too).

I was previously using:
filterByFormula=Find("Tuesday", Day)

The associated values in the to Name, Description, Price fields were being returned to populate components in an interface design tool I use called Framer.

Unfortunately I’ve just learned that because they are now Lookup fields (eg calculated fields) it’s not that simple. The values are no longer populating. Testing shows its the Lookup fields causing the issue and a bit of research revealed that I may be able to to use an array function to bridge the gap. I’ve tried versions of the following formula but to no avail:

filterByFormula=Find("Tuesday", ARRAYJOIN(Day))

Do I need to reference the linked field somewhere?

The only other solution I can come up with is to change all my Lookup fields into Single Line Text fields which would undo all the work I have just done. Will teach me for not testing it upfront!

Any help would be greatly appreciated!

[Justin_Barrett]

Tim_Bruce:

Testing shows its the Lookup fields causing the issue and a bit of research revealed that I may be able to to use an array function to bridge the gap.

Using ARRAYJOIN() should work because it outputs a string. You can also convert a lookup’s output to a string by concatenating it with an empty string. Give this a shot:

filterByFormula=Find("Tuesday", Day & "")

[Tim_Bruce]

Thanks for the help.
I tried it but I couldn’t get it to work

To be clear (in case I’m doing something obvious wrong which I think I must be) here is a simplified example of ‘master table’:

Here is a simplified example of ‘table’ that links to the master table. It has a linked field, some lookup fields and some unique fields of its own. I’m wanting to access all these values to populate my components.

Am I doing something wildly wrong with this setup? Should I just keep all the records on a master table and pull the records I need into the linked tables?

If it helps here’s a creator link to the examples above: https://airtable.com/invite/l?inviteId=invJdE7GEzkYE8Koa&inviteToken=265ed1c546b7d2d0e92654dadca1cc5...

Heres also which fields are/aren’t populating some demo components:

Heres the respective URLs (that work as above):
https://api.airtable.com/v0/appsWS5M27Wy3zvEj/master_table?filterByFormula=Find("Tuesday"%2C+day)&vi...
https://api.airtable.com/v0/appsWS5M27Wy3zvEj/table?filterByFormula=Find("Tuesday"%2C+day)&view=Grid...

Heres the respective URLs decoded (that work as above):
https://api.airtable.com/v0/appsWS5M27Wy3zvEj/master_table?filterByFormula=Find(“Tuesday”,+day)&view... view&api_key=keyXXXXXXX
https://api.airtable.com/v0/appsWS5M27Wy3zvEj/table?filterByFormula=Find(“Tuesday”,+day)&view=Grid view&api_key=keyXXXXXXX

Again any help would be appreciated!

[Justin_Barrett]

When using filterByFormula, you have to pass a valid Airtable formula. Your current formula (extracted from your URL) is:

Find("Tuesday",+day)

There are two things wrong here:

1. Airtable uses the ampersand operator (&) for string concatenation, whereas many other scripting/programming languages use the same plus (+) operator that’s used for numerical calculations.
2. The empty quotes—representing the string which you’re trying to concatenate with the {day} field—are missing. The tool you’re using to build the URL just sees “” as a literal empty string, not a set of actual quote characters to pass to Airtable via the URL.You’ll need to escape them, most likely using a backslash; e.g. \"\", not just “”

With those items fixed, the non-encoded filter formula should be:

Find("Tuesday",\"\"&day)