Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Formula Error with Rollup Input

Topic Labels: Formulas
Solved
Jump to Solution
679 5
cancel
Showing results for 
Search instead for 
Did you mean: 

I’d like to use a rollup field with an array as an input for a formula in a different field, but it gives me an error. It seems to not be treating the rollup as a text string.

1 Solution

Accepted Solutions

There are a few issues here.

If you are using only ARRAYUNIQUE as your rollup aggregation formula, then the result is an array, not a text string. You can use ARRAYJOIN in conjunction with ARRAYUNIQUE to get a text string:

ARRAYJOIN(ARRAYUNIQUE(values))

After you get the text string, you probably want to use REPLACE instead of SUBSTITUTE.
SUBSTITUTE replaces all occurrences of “old text” with “new text”. REPLACE will replace only the first instance.

I don’t know what your ultimate goal is in splitting out these fields. However, there might also be other ways to get to your end goal that don’t involve as much string manipulation.

See Solution in Thread

5 Replies 5

Can you post screen shots of the fields and formulas? What is the field type that you are rolling up?

I’m rolling up a text field using ARRAYUNIQUE(values).

Here’s the rollup field to be used as the input.
image

Here’s the formula to put index numbers in place of the commas so that the array items can be separated into individual cells more easily.
image

There are a few issues here.

If you are using only ARRAYUNIQUE as your rollup aggregation formula, then the result is an array, not a text string. You can use ARRAYJOIN in conjunction with ARRAYUNIQUE to get a text string:

ARRAYJOIN(ARRAYUNIQUE(values))

After you get the text string, you probably want to use REPLACE instead of SUBSTITUTE.
SUBSTITUTE replaces all occurrences of “old text” with “new text”. REPLACE will replace only the first instance.

I don’t know what your ultimate goal is in splitting out these fields. However, there might also be other ways to get to your end goal that don’t involve as much string manipulation.

This formula works in the rollup field, thanks @kuovonne!

ARRAYJOIN(ARRAYUNIQUE(values))

Either SUBSTITUTE() or REPLACE() work fine in the other field. It would be neat to see a new formula that allows us to iterate another formula across a set of values.

Honestly @kuovonne 

If you had a penny for everytime you had helped me on these forums, you would be VERY RICH!

Thank you so much.

Andrew