1 of 1 people found this helpful
If I use a text field for the sortOrder field (a calculation field with a text result would also work) and the custom values value list that you specify, it worked for me. This screen shot shows the result of sorting using the value list to specify the sort order:
But I'd consider using a calculation field that replaced "B" with -1, Roof with a very large number such as 9999999999999999 with a number result type and then used a normal ascending sort on that field. This special sort field need not be visible on any layout in order for it to be used for sorting purposes.
1 of 1 people found this helpful
Probably obvious, but when you sort, you have 3 parameters specified.
Try sorting just by value list. If that works, add the second parameter. Try it both ways, sorting on the value list first, then the other parameter & sorting on other parameter, then the value list.
Same thing for sorting by all 3-move the parameters around.
as SteveMartino suggested I went down to one sort parameter.
If I make the Level field a text, it wants to sort the levels like this: 1,10,11,12,13,14,15,16,17,18,19,2,20,21,3,4,5... There are certainly instances where there is no B/ROOF and it would need to sort 1,2,3,4...
I did create your substitution with another field, and it certainly works- thank you for that!
However, I only had the idea to sort by a custom value list, because it's right there in the menu! And feel like that feature is more user friendly to my team in the long run-- only because there are exceptions. Sometimes the B (for basement) may be replaced with G (garage) or LL (lower Level) or M (mezzanine) depending on the client. We use this field in a script to sort by level of the building and I'm trying to make it so it's logical when they look at it. Knowing to sort by "Level C" vs "Level with the text replaced with numbers so it sorts correctly" but.... after all that I see the opportunity for me make it work--
I know I just must be missing something.
Would you mind sending me the FMP ( see it was advanced, I only have Pro - 14.0.6 .. not sure if that is an issue -- and of course assuming you still have it) of the screen shot you took so I can snoop around at all my field options and see if I am just missing something? It would help with other values that don't have any ascending or descending values that would make sense.
I'll head to lunch and get a fresh perspective when I return.
Numbers in text sort different than numbers in numeric. You are trying to miss match. philmfdjunk told you the best way to handle it.
I am attaching the file that I used. What is a key detail is that I am using the value list to sort the values, which is neither text nor number based sorting. The order that you see matches the order of the values in the value list. You might note the icon in the table view that indicates a custom sort order based on a value list in my previous post.
ValueListBasedSort.fmp12.zip 66.2 K
So after a lot of frustration and side help, it was discovered by someone much smarter than I --> @philmodjunk that I some how had a leading space making my value lists not add up.
I was unable to remedy that problem tho he was, and but after getting some more information I went back to his idea on substituting the Levels as numerical values.
In doing so, I am able to get the results and account for additional variables in the future.