Sort group headings by displayed value instead of reference ID

[dbaum]

I noticed today that, in a deck view, group headings based on a ref column inappropriately sort by the literal reference value (e.g., an ID) and not by the displayed value, which is appropriately from the parent table's column that is designated as the label. I never noticed this before, and wonder whether:

• something has changed
• by chance my data has usually had ID values that sort in the same order as the label values and so I never noticed
• I'm missing something that I need to do to base the sorting on the display value

Anyway, if it's not a bug, can anyone provide guidance regarding how to achieve sorting by the displayed values instead of the underlying ID values?

Group headings in the app's UI - notice that "List 4" precedes "List 3" in the display

Source data - notice that the ID for "List 4" alphabetically precedes the ID for "List 3"

"Group by" settings for the view - notice that the List ID field is set to sort in ascending order

[WillowMobileSys · Accepted solution]

This has been an issue from day one.  As you know, Ref columns store the row key.  For display, AppSheet will show the column marked as "Label" in the referenced table.  However, any functions will operate on the actual value in the column - including sorting.  I believe there are a couple of Feature Ideas recommending to use the Label for Grouping. Feel free to add your comments to those.

As a workaround, create column in the table you are Grouping to pull in the Label value from the Ref column.

Note: Another pain point with Grouping is that there is no way to elegantly specify a custom Group sort order.  If you want the Groups in a non-alphabetical order (or non-numerical order) but rather in an order you specify,  you would need to add something to the displayed value to force the order you wish.  E.g.  { 1-List 2,  2-List 3, 3-List 1, 4-List 4 }... or maybe {a-List 2, b-List 3, c-List 1, d-List 4}

[dbaum]

Thanks. Good to know there's not some step I'd misunderstood. Creating a redundant column is a logical workaround.

[dbaum]

Thanks again, @WillowMobileSys .

I have various places where I plan to apply the workaround. I assume it makes most sense to use virtual columns to "create column in the table you are Grouping to pull in the Label value from the Ref column". Before I go too far, do you have any guidance regarding pitfalls or benefits of using virtual columns vs. adding actual columns to the data source?

[TeeSee1]

This is interesting..

I did a little testing and I get a result where sorting is based on the label values and not id values like here. Wonder what I could be doing different.

Product table: product_id as key, product_name as label

Deck view of a table (ref_prod) with a reference to product table

Col definition of table ref_prd

Deck view definition of ref_prod.

[WillowMobileSys]

That is interesting.  I do notice your view is a Deck View.  Does it do the same on a Table View?

[TeeSee1]

The same results with a table view...

[Norry_Depts]

I've found the same as TeeSee1.  My sort is based on the label and not the ref key.  I need the reference key in my second group.  Table is Signs, UX view is table. 

First group, ref key is StreetID, where StreetID and Label are basically the same. 

Second group, ref key is TravelingID, 

StreetID	TravelingID
Hanover	Even-14_Sixth_Saw Mill
Hanover	Even-15_Saw Mill_Seventh
Hanover	Odd-03_Seventh_Saw Mill
Hanover	Odd-04_Saw Mill_Sixth
Hanover	Odd-05_Sixth_Pond

The goal is to be able to inventory street signs as you drive down the street, turning around at the end and driving back, only inventorying the side you are traveling. (Safety issue).  

Sort on app  Sawmill's, Seventh's, Sixth's. 

I too would like to know what's different so I can correct it.  I've tried various scenarios and can't get out of this sort. 

[WillowMobileSys]

Hi @Norry_Depts !  Your issue seems to be in ordering line items - not Groups.  Is that correct?  That is a different hurdle but is more easily solved.   

If I understand correctly, you need a custom sort of the line items to list the streets in the order they are encountered when driving?  To do that, you will need a dedicated column for the order.  A number column will do.  Simply assign the desired order to the street rows and then apply a Sort to the view using the Order column.