How to sort by multiple fields in a formula

Get Help
1

Is it currently possible to sort by multiple fields in a formula, like it is in the UI?

The scenario is just the common situation of sorting semver strings (1.23.4-rc2) correctly. There’s trickiness in sorting the number portions correctly but I think I can figure out a way around that. However, if we’re also wanting to sort the suffix single-selects (-dev, -alpha, -beta, -rc#) by an internal value so they show up in the right order, the easiest thing to do is just to sort by multiple fields.

I can see on the fibery formula reference (. | Fibery) that the syntax for the sort function apparently is able to receive an array Sort([field, isAscending])? Maybe the presence of the brackets around both parameters is a typo, because when I attempt to do this in the web UI formula editor, it errors out because it doesn’t recognize the array syntax. You also can’t daisy chain multiple .Sort().Sort().Sort() statements…

Anybody have any ideas?

2

In this instance, the brackets indicate that the parameters are optional, not that you should pass an array.

Correct. Multiple levels of sorting in formulas are not supported.
Note: Sort() in formulas has no effect on the UI - it is only usable when followed by First() or Last()

What is your ultimate goal with the formula?

3

Ok, I thought that might also be the case. So, not a typo :heavy_check_mark:

To correctly sort Versions in a list, and to be able to obtain the latest Version of a Product. I have Products that have multiple Versions. Versions have number fields [Major Version Number], [Minor Version Number], [Patch Version Number], and a single-select dropdown of “Version Type” which is “Development”, “Alpha”, “Beta”, “Release Candidate”, and “Production”. Each of those Version Type options has an internal integer that can be used to sort them so that e.g. “Development” is less than “Production”.

Sorting alphabetically on a formula string or automation-generated string field that concatenates these fields (e.g. “2.10.3-beta”) probably doesn’t give the desired results because alphabetical sort functions usually consider “2.10.0” greater than “2.2.0” because ASCII “0” is greater than the ASCII “.”

4

@carson
You could create a text Formula field that defines the “Sort Text” of each entity, and sort on that field.

Maybe a Formula like:

Right("00000000" + ToText([Major Version Number]), 8) + "." +
Right("00000000" + ToText([Minor Version Number]), 8) + "." +
Right("00000000" + ToText([Patch Version Number]), 8) + "."
1 Like