8 Replies Latest reply on Jun 12, 2015 8:10 AM by SteveMartino

    Sorting issues



      Sorting issues


      Hi, I am writing to ask for any ways to perform sorting under the below conditions:

      I want to use a button/script to perform the sorting according to the codes of all records where the codes are combining both English characters and numbers and i wish to sort by the numbers instead of the Alphabets.

      for example, I have the codes J150401,J150402,P150401,P150402, if i use the sorting function according to "codes", the system will sort them according to the alphabet then numbers, so the result sequence would be J150401,J150402,P150401, J150402. how about if i want to sort it according to number first? i wish to have the result like: J150401,P150401,J150402,P150402...

      Do i need a script or just a simple button can solve it? or any possible alternatives? thx

        • 1. Re: Sorting issues

          Either a standalone script or a button which calls that script would work depending on your situation.

          If you want to use a button to call a script...

          Create the script to do the sorting you want and then in the Button Setup, you can call the script.

          Hope this helps.

          - m

          • 2. Re: Sorting issues

            In addition to the advice from Mork, you can also create 2 additional fields from the same table that don't have to be on layout.  They are both auto enter fields, that will parse out the id number.  Assuming your id field is named YourIdField:

            1.  IdNumber, type-number, with an auto enter calc, uncheck "Do not replace..." with the following calc:

            Middle ( YourIdField ; 2 ; 6 )

            2.  IdLetter, type-text, with an auto enter calc, uncheck "Do not replace..." with the following calc:

            Left ( YourIdField;1 )

            Then in your sort script, you sort by: IdNumber (ascending) and IdLetter (ascending)

            • 3. Re: Sorting issues

              thanks both of you! 

              Mork, could you please suggest the script for my case? i want to sort the code by numbers instead of alphabets. thx


              • 4. Re: Sorting issues

                If the number part doesn't start with a zero, you could create a new calculated field with calc: GetAsNumber( Code ) and then sort by that new field. Try this by hand and, if the result is acceptable, create the script.

                • 5. Re: Sorting issues

                  raybaudi's suggestion above looks like the way to go. It's simple (which is good) and it works...see screenshot below.

                  All you need to do is create the calculated field and then sort by that field. Your script would really only need one line in it.


                  You could also get this sort in a SQL statement like the one below (assuming you always have one leading character and five digits to the right):

                  ExecuteSQL ( "select  RIGHT(code, 5) as sortedCode, code from testDB order by sortedcode" ; "     " ; "¶" )

                  50401     J150401
                  50401     P150401
                  50402     J150402
                  50402     P150402


                  If your codes were more complicated, like a variable, but understandable, number of leading characters, you could still do basically whatever you want by using the text functions like "Position" and others to extract just the parts you need in a more complicated script. Copy those pieces to another field for each record in a script, and, again, sort on that column.

                  Hope this helps.

                  - m






                  • 6. Re: Sorting issues

                    Edit: If I did Raybaudi's method correctly, it didn't work for me

                    GetAsNumber ( Code ( YourIdFieldHere ) )

                    Consider the records:








                    Sort ascending, by using the Code calculation, puts them in that exact order


                    • 7. Re: Sorting issues

                      Steve, my calc was simply:

                      GetAsNumber( Code )

                      where Code is the field name.

                      • 8. Re: Sorting issues

                        Tried that too.  Sorts the number fields correctly, but not the id field. 

                        Un sorted order:               Sorted by GetAsNumber(IdField):

                        P150402                                          P12346

                        J150402                                          B123456

                        J150401                                          A123456

                        P150401                                         X123456

                        P123456                                         J123456

                        B123456                                         J150401

                        A123456                                         P150401

                        X123456                                         P150402

                        J123456                                         J150402

                        But the part I forgot, which does work, is if you sort by Raybaudi's calc field, THEN sort by the id field, now the sort works fine, and only uses 1 extra field.  It also works in my first example with just the extra IdNumber field.  But keep in mind Raybaudi's method is better (if first number is not zero) and if the id is any number of digits.