Help

Alphabetize Rollup Result

Topic Labels: Formulas
998 12
cancel
Showing results for 
Search instead for 
Did you mean: 

I am doing a rollup on a field that has text (2-letter US State):
ARRAYJOIN(ARRAYUNIQUE(values),"\n")

I am wondering if there is any way to alphabetize the result output?

12 Replies 12

If you sort the primary field alphabetically, will the US states be also alphabetized? If so, you can run the batch update block on the linked record field to sort the linked records alphabetically. Then the rollup values should be sorted the same as the linked records.

I am also looking for this feature

I don’t understand how to sort the primary field alphabetically or how to run the batch update block on the linked record field.

Here’s some background. I have a table called Guests and each record has a field called Last Name. But, the Last Name field is calculated with a formula.

In another table called Episodes, every record has a field called All Guests with a Rollup formula that collects all of the Last Names from the Guest records mentioned above.

Here’s the Rollup Formula: SUBSTITUTE(ARRAYJOIN(values), “,”, “-”).
Result: Yakov-Ben-Levy
Desired Result: Ben-Levy-Yakov

I want the 3 names to be in alphabetical order. Can the Batch Update App help with that? I assume it will not work on theLast Name field in the Guests table.

Formulas and rollups cannot sort. Batch Update will only sort linked records by the primary field. Since the last name is probably not the primary field Batch Update probably won’t help you. The order of values in rollups matches the order of linked record fields.

By the way, here is a simpler rollup formula. Does the same thing, but just a little simper.

ARRAYJOIN(values, "-")

Thanks for the simpler version.

You said:
The order of values in rollups matches the order of linked record fields.

How do I change the order of the linked record fields? I know how to sort in a table view, but this is something different, right?

Yes, this is different.

You can open the linked record field cell and drag-and-drop the linked records to rearrange the order.

You can use scripting to rearrange the order.

You can use Batch Update to re-order by primary field value.

I also write an app for the Marketplace for sorting linked records. However it is still in review at the moment.

Is your app available in beta? When do you think it will be released?

Also, how do I get started writing a script to re-arrange the order in the All Guests field?

  • Will the script automatically rearrange the order?
  • If I have to do it manually for each record, can I do it in bulk?

It depends on how much experience you have with writing code. If you have not written code before, start by learning to code. If you know JavaScript already, start with the Scripting Documentation

If you want a script to run automatically, you can design it to run as an automation script. If you want the script to process records in bulk, it can do that too. It is all in how you write the code. However, typically is is difficult to have an automation script run on records in bulk unless it is set to run on a schedule versus being triggered by an individual record.

I’ll send you a direct message with more info on trying out the pre-release version of my app.

Awesome! Thank you. :thumbs_up:

@Chris_Dukes, did you ever figure out how to script the arrayjoin(values) to return a list in alphabetical order? Thanks for the reply!

Labels