6 Replies Latest reply on Jul 7, 2017 4:43 PM by lindseym

    Sort Records, Sort Order, Custom Order based on Value List-Not giving desired results

    lindseym

      In a script, I have a sort order.

       

      I have broken out the sort order as it's own script while I test it out.

       

      What I want:

       

      The "Level" column to appear in the order: B,1-21,ROOF

      Screen Shot 2017-07-03 at 9.46.48 AM.png

       

      What I get:

       

      The "Level" column appears in the order: 1-21,B,R

       

      Screen Shot 2017-07-03 at 9.47.03 AM.png

       

       

      The level is set up as a calculated field with the outcome being a number. I have tried it as text with the same issue, and if I am using a custom value sort order I'm not sure why it would matter if it was text or number.

       

       

       

       

      Screen Shot 2017-07-03 at 9.54.01 AM.png

       

      I'm stumped.

       

      Cheers,

      -L

        • 1. Re: Sort Records, Sort Order, Custom Order based on Value List-Not giving desired results
          philmodjunk

          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:

          ValueListBasedSortOrder.png

           

          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
          • 2. Re: Sort Records, Sort Order, Custom Order based on Value List-Not giving desired results
            SteveMartino

            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.

            1 of 1 people found this helpful
            • 3. Re: Sort Records, Sort Order, Custom Order based on Value List-Not giving desired results
              lindseym

              philmodjunk

               

              as SteveMartino suggested I went down to one sort parameter.

               

              I still can't get the sort order to work based off the custom values. Screen Shot 2017-07-03 at 11.31.40 AM.png

               

              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!

              Screen Shot 2017-07-03 at 11.29.23 AM.png

               

              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.

               

              Thank you!

              -L

              • 4. Re: Sort Records, Sort Order, Custom Order based on Value List-Not giving desired results
                patricia

                Numbers in text sort different than numbers in numeric. You are trying to miss match. philmfdjunk told you the best way to handle it.

                patricia

                • 5. Re: Sort Records, Sort Order, Custom Order based on Value List-Not giving desired results
                  philmodjunk

                  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.

                  1 of 1 people found this helpful
                  • 6. Re: Sort Records, Sort Order, Custom Order based on Value List-Not giving desired results
                    lindseym

                    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.

                     

                    Screen Shot 2017-07-07 at 4.32.42 PM.png