Orderby usersetting

Hi

I have a table, of which I would like to arrange the rows according to the first letter of the column [Name].
Now I want the user to be able to select which letter the list starts with:
for example: in user settings the user indicates that he wants to start the list with D -> the list would then start with all the names starting with D, then E,… then Z, A -> C

Do you think it’s possible?
Bram

No idea if this will work, and it’s a bit of work to set up. But maybe give this a shot:

Create a list of {“A”,“B”,…-“Z”}, and FIND the position (X) of the user’s selected value in that list.

Then take the RIGHT X characters of A-Z, and CONCAT with the LEFT 26-X.

THEN, use that list and find each row’s position and store that number in a virtual column to sort on.

I think this should work, but haven’t tested it. It’s similar to @GreenFlux’s idea.

Create a virtual column with the app formula below, the configure your table view to sort first by this column and second by the Name column.

MOD(
  (
    FIND(LEFT([Name], 1), "A...Z")
    - FIND(USERSETTINGS("Names Start"), "A...Z")
    + LEN("A...Z")
  ),
  LEN("A...Z")
)
  1. LEFT([Name], 1) gets the first character (presumably a letter) from [Name].

  2. FIND(LEFT(...), "A...Z") gets the place in the alphabet of the letter from (1). Note that A...Z everywhere in this expression should actually be ABCDEFGHIJKLMNOPQRSTUVWXYZ (i.e., the entire alphabet you’re using). I abbreviated just for clarity.

  3. USERSETTINGS("Names Start") gets the user’s preferred starting letter. Replace this with any expression that will provide that piece of information.

  4. FIND(USERSETTINGS(...), "A...Z") gets the place in the alphabet of the user’s preferred starting letter from (3). Remember to replace A...Z with your entire alphabet exactly as used in (2).

  5. LEN("A...Z") computes the number of letters in your alphabet. Again, replace A...Z. You can also just replace LEN("A...Z") with the number.

3 Likes

It’s just miraculous!
Perfect!
Nice!

Thx a lot … I can show off now!

Bram

1 Like