Hello. I am grouping my view by a related row column (Epic ID), sorted A > Z. The related row column type is Number, and it’s a number with 1 decimal place. I want it to group my sheet in numerical order, but it is sorting 10 after 1 instead of after 9. I don’t see an option to sort numerically in the group control. Is there a way to correct this, other than adding leading zeros to my ID column?
Thanks,
Erica
Related - row field values are relationship labels derived from formatted cell values and therefore are text literal values i.e. “10.0” or “11.0” which are sorted as text. In case of multi-cardinality relationships these text values will be juxtaposed together like “10.0, 11.0” .
To work with related-row fields i.e. sort/filter/group/aggregate-calculations, request you to first define lookup fields over a relationship to pull a specific related-row field as it is i.e. number as number, currency as currency etc. Then define grouping/sorting/aggregates on this lookup field.
More details: Lookup Fields
@msiraj thanks so much for this response! I added the lookup field column, and that fixed the order of the grouping. I’m having a similar problem in another area of the spreadsheet, and was wondering if there was a fix? I have an ID column in my spreadsheet, which is a text column. The format for the IDs is 1.2.3 where 1 represents the main category, 2 represents the sub-category, and 3 represents the individual item. Is there any way to use a numbering system like this and have 10 sort after 9 instead of after 1? Here’s a screenshot that might help to explain.
One solution you can consider is defining a column-formula to convert each of the ID column text literals i.e. values like “1.2.3” into monotonically increasing number sequences that can then be sorted in the correct order.
That is convert text “3.2.3” by extracting the main category here i.e. 3 & multiply with a arbitrary large number say 100000 and then convert and add the left-over text (i.e. 2.3 ) to it. So 3.2.3 becomes 300002.3
Eg. LEFT(A,FIND(".",A)-1)*100000+RIGHT(A,LEN(A)-FIND(".",A))
Once setup, you can choose to hide this formula column and just leverage it for sorting sheet.
A better way to define this column formula by also accounting for any possible errors in text manipulation,
IFERROR(LEFT(A,FIND(".",A)-1)*100000+RIGHT(A,LEN(A)-FIND(".",A)),NULL)