Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Sort Roll-up List of Simple Values

Topic Labels: Base design
1024 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Matt_Kennedy1
7 - App Architect
7 - App Architect

This is a basic hack to sort a simple list of pre-determined roll-up values (e.g. 12 months) without a script or 3rd party automation.

In the example below, month values from the Sub-items table are rolled up in Items table / Unsorted column.  Columns 'M1', 'M2', 'M3' use a REGEX_MATCH formula to check 'Unsorted' for the corresponding values.  The column 'Sorted' uses a series of IF statements to to join these together in the right order.

Screenshot 2024-03-15 at 5.18.01 PM.png

 

 

3 Replies 3

What formulas are you using?

Screenshot 2024-03-16 at 3.46.55 PM.png

Screenshot 2024-03-16 at 3.46.38 PM.png

  

Cool, thanks for posting the formulas!

Just an FYI that while REGEX works in this example, you wouldn't want to use REGEX if you're searching for words that have special characters in them (such as ?, +, *, etc.). That's because certain characters would be interpreted as REGEX instructions.

Since you're just performing a normal search, you can use FIND() or SEARCH().

For example:

IF(
FIND( "Jan", {Rollup Field} & "" ),
"Jan")

 

And then, if you're so inclined, you can reduce the multiple formulas down to just one unified formula like this:

IF(
FIND( "Jan", {Rollup Field} & "" ),
"Jan, ")

&

IF(
FIND( "Feb", {Rollup Field} & "" ),
"Feb, ")


&

IF(
FIND( "Mar", {Rollup Field} & "" ),
"Mar ")

As you mentioned, this trick would only work with a predefined list of values, and then you may want to create another formula to take care of the lingering comma at the end if the final value doesn't exist.