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
Formula Error with Rollup Input
+11Best answer by kuovonne
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.
+27Can you post screen shots of the fields and formulas? What is the field type that you are rolling up?
+11kuovonne wrote:
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.
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.
+27There 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.
+11kuovonne wrote:
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.
+17kuovonne wrote:
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.
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
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.