Rollup Array Concatenation

[Brian_Schuster]

I’m setting up a rollup field to concatenate record IDs for general items that match a set of filters. There is a form input box to accept plain text, but it does not accept column names or cells as inputs. Is there a way to make it do that similar to a formula?

[Justin_Barrett]

Not directly. That field is where you specify some condition you want to check for (e.g. contains) related to the value of that field (e.g. {Name}) in the linked record. It must be a literal value. If you want to use multiple fields from the target table as part of the condition, you could add formula fields on that target table for those deeper tests, and then have the conditional setup check the formula field’s output.

[Brian_Schuster]

The problem with having the formulas within the target table is that they need to pull inputs from the table doing the lookup. So either way I do it, I would probably need some way to filter dynamically with cell values.

I was thinking of using formulas to parse an array, but I do not know of a way to iterate or repeat a formula across many records in a text array.

[Justin_Barrett]

Brian_Schuster:

I was thinking of using formulas to parse an array, but I do not know of a way to iterate or repeat a formula across many records in a text array.

Sadly, that’s not possible. Airtable’s functions don’t offer array iteration support. If you need to get that deep into the data, you’ll need to consider scripting, which can pull data from any part of your base to do the job. If you’d like any help in that regard, just holler.

[Brian_Schuster]

Yes, I would like to figure this out as soon as possible. Do you have a template for that type of script in the scripting block? I have 2-3 filter parameters that I want to apply, and if I could parse the text array, I considered applying them with the URL model where the lookup field would read like this: rec00000000&Topping?=‘ketchup’&Food?=Burger . The question is how to parse that when there are up to 300 of those in one comma-separated array.

[Justin_Barrett]

Unfortunately I don’t have a template for something like that, as I haven’t built that type of setup before. Based on your description, it sounds like this script would need to be pretty closely tailored to your specific setup, so I’m not sure that a template would even be helpful. I’ll send you a DM shortly to discuss some possible options.

[Brian_Schuster]

It’s not custom, I was just providing an example for visualization. This is a feature that would have broad benefits for people looking to get more power out of Airtable’s filters.

[Justin_Barrett]

Understood. I still can’t put together a template without a more specific example, though. Can you break down what you’re looking to do in more detail?

[Brian_Schuster]

Of course, I’ll show you a simple example to try to clarify.

When I use the term ‘relational’, I’m referring to linked fields connected to another base.

1. Create a General Item table with columns: Category (text), Food (text), Topping (text), Filter Stage1 (formula). Filter Stage1 is formulated by concatenating all previous fields into one string with this form: rec00000000&Category?=‘Meat’&Food?=‘Burger’&Topping?=‘ketchup’.
2. Create a Filter table with columns: User (relational), General Item (relational), All Items (lookup Filter Stage1 and apply arrayjoin(values)), Filter Stage2 (formula), Filter Parameter #1 (text e.g. Topping), Filter Value #1 (e.g. ketchup). Filter Parameter and Filter Value should be repeated up to #3.
3. The Filter Stage2 formula is where you’ll probably apply the scripting block. That is where record IDs are spliced together into their own array from the All Items lookup if a Filter Value is contained in the Filter Stage1 string.
4. Create a User table with columns: Username (text), Filters (relational).
5. Enter a few rows of example data in each table. Put all General Item records into the Filter table in the General Item relational field. I used transpose and concatenate in Excel to build the array to copy and paste back into Airtable.

Let me know if there are any key details missing.

[Justin_Barrett]

I’ve read through this description several times. While some parts are clicking, others aren’t, and I think the main part that’s not quite clicking for me is the end goal of this setup. After reading in your other thread that you’re building an API-driven tool, I’m not sure why you’re trying to create a filtering system inside of Airtable itself when the API code would allow you to do the filtering you need much more easily.

Circling back to the script option that I was proposing, that’s also not going to work with an API-driven tool. Scripts in the Scripting block must be run manually from the Airtable UI. They can’t be triggered by the API. While Airtable’s automations can do things without the UI, there’s a delay in the activation of their scripts. And again, if you’re making an API-driven tool, then you’ll have far greater flexibility to do the kind of filtering that you want in the code itself compared to trying to wrangle Airtable’s features to achieve the output you seek.