Sorting an alpha-numeric list

[Jake_Naude]

Hi,

I'm having difficulty sorting text column into a descending order list.

I'm getting this result:

1A , 1B , 1C , 23A , 24A , 29A , 2A , 2B , 2C , 31A , 3A , 3B , 3C , 81A , 81C

The result I'm after is:

1A , 1B , 1C , 2A , 2B , 2C , 3A , 3B , 3C , 23A , 24A , 29A , 31A , 81A , 81C

I'm using this select:

UNIQUE(
SORT(
SELECT(
assets[node_asset],
AND(
[floorplan_id] = [_THISROW].[id],
ISNOTBLANK([node_asset])
)
)
)
)

Thanks in advance... 😉

[T_I_Phong]

AppSheet compares characters from left to right based on their ASCII values. You can create two virtual columns: one to extract the numeric part and another to extract the text part. Then, use the ORDERBY() function with these two virtual columns to achieve the desired sorting.

[Jake_Naude]

Thanks @T_I_Phong... Your explanation makes sense and I can see why my expression was never going to work... I'll do as suggested in a test to see how that hangs together... Thanks for the advice... 👍

[Suvrutt_Gurjar · Accepted solution]

Yes, AppSheet sorts in the way @T_I_Phong mentioned. To get the sort order you desire, I believe it will call for some longish configuration.

I tried and even posted an expression but I realized that it is also failing..

If it is must , we could give it a try to come up with a configuration. However you may want to evaluate if the need will justify a longish configuration. We may need to add a few extra columns to achieve it.

BTW could you update where exactly this expression is used?

Edit:

Please try an expression something like 

UNIQUE(
SORT(
SELECT(
assets[node_asset],
AND(
LEN([node_asset])=2,
[floorplan_id] = [_THISROW].[id]

)
)
),FALSE
) +

UNIQUE(
SORT(
SELECT(
assets[node_asset],
AND(
LEN([node_asset])=3,
[floorplan_id] = [_THISROW].[id]

)
)
),FALSE
)

[Jake_Naude]

@Suvrutt_Gurjar your suggestion makes sense and it's simplicity and elegance is a beautiful thing to see in action... Many thanks as always... Hole in one... You are a Rockstar sir...!!!

UNIQUE(
  SORT(
    SELECT(assets[node_asset],
      AND(
        LEN([node_asset]) = 2,
        [floorplan_id] = [_THISROW].[id]
      )
    ), FALSE
  )
)
+ UNIQUE(
  SORT(
    SELECT(assets[node_asset],
      AND(
        LEN([node_asset]) = 3,
        [floorplan_id] = [_THISROW].[id]
      )
    ), FALSE
  )
)

 Result: 1A , 1B , 1C , 2A , 2B , 2C , 3A , 3B , 3C , 23A , 24A , 29A , 31A , 81A , 81C

🏆

[Suvrutt_Gurjar]

You are welcome @Jake_Naude . Thank you for the update. Nice to know it works as desired.👍