Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 08, 2020 10:44 AM
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.
Solved! Go to Solution.
Oct 08, 2020 02:05 PM
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.
Oct 08, 2020 01:42 PM
Can you post screen shots of the fields and formulas? What is the field type that you are rolling up?
Oct 08, 2020 01:50 PM
I’m rolling up a text field using ARRAYUNIQUE(values).
Here’s the rollup field to be used as the input.
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.
Oct 08, 2020 02:05 PM
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.
Oct 09, 2020 08:27 AM
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.
Jan 31, 2023 06:58 AM
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