Help

Re: Rollup Array Concatenation

1869 14
cancel
Showing results for 
Search instead for 
Did you mean: 
Brian_Schuster
7 - App Architect
7 - App Architect

image

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?

16 Replies 16

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.

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.

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.

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.

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.

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.

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?

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.

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.

I’m not making an API-driven tool. FilterByFormula is not working with multiple inputs. That’s why I’m trying to get rollup filters to work with dynamic field inputs.

In your other thread, you opened with:

I am working on an app that uses the Airtable API to upload and download data

That to me is an API-driven tool. You’re writing code that uses the Airtable API to access Airtable’s data.

It’s a different company called Adalo. They handle the API coding and I insert the filterByFormula formulas.

Ahhh. Okay. That would have been helpful to know earlier in this discussion. I’m not familiar with Adalo, so I can’t offer any specific advice on how to approach things from their end, but Adalo has come up in a few other discussions in the forum. You might search for those threads and connect with other Adalo-Airtable users to see if they’ve got any ideas that will help.

Yeah, I’m definitely talking to the Adalo team and users in their forum, but this relates exclusively to Airtable for now.

It never hurts to have data filtering, especially for a software as promising as Airtable.

I agree 100%. There are lots of ways that Airtable can improve on that front. It’s just difficult to say how quickly those improvements will come along.

Soon, I hope! I really want to use Airtable to get my app off the ground.