11 Replies Latest reply on Apr 18, 2013 8:35 PM by BruceHerbach

    Sorting Numbers is Ascending Order


      I have attached a report our software developer created for us in FM Pro Advanced 11. This report, along with everything else we do in FM is sorting by the first number only. ie: 1,10, 100, 2, 20, 200, 3, 30, 300... rather than sorting the numbers lowest to highest. ie: 1, 2, 3, 4.....and so on. Our developers says this is the only way we can report these fields however this blows my mind that a system wouldnt have the smarts to sort numbers correctly. I have no IDEA have to create software in this database so I need some VERY Elementary help in this area.



        • 1. Re: Sorting Numbers is Ascending Order

          It looks like the Sheet # field is defined as text. Change the field to a number field; this should correct the sort.





          • 2. Re: Sorting Numbers is Ascending Order

            If these fields are changed to number fields will I still be able to enter letters if needed?

            • 3. Re: Sorting Numbers is Ascending Order

              You can enter them, but FileMaker will ignore them.


              If you need to enter letters, but want the field to sort as if it were a number, then enter leading zeroes. Instead of this:


              1, 2, 3 ... 10, 11, 12


              do this:


              01, 02, 03 ... 10, 11, 12.


              That will cause FileMaker to sort the field correctly.



              1 of 1 people found this helpful
              • 4. Re: Sorting Numbers is Ascending Order

                Thank you so much for the help and quick response! I will talk to the developer about this when i see her next.

                • 5. Re: Sorting Numbers is Ascending Order

                  Just for fun, you can have the system automatically enter leading zeroes for you. Go into the field definitions (File / Manage / Database) and set the field to have an auto-enter calculation:


                  Case ( Filter ( SheetNo ; "123456789" ) = SheetNo and Length ( SheetNo = 1 ) ; "0" & SheetNo ; SheetNo )


                  Set the calculation to replace the existing value, and FileMaker will automatically put a leading zero on any value that's exactly 1 character long and in the range 1 through 9.



                  • 6. Re: Sorting Numbers is Ascending Order

                    Another option would be to define a second field that is a calculation of the first.


                    Use the function GetAsNumber(SheetNo).


                    This field would then be the one used to sort the report. Because the result is a number it will sort correctly numerically. In the case of mixed text and number value entries in the first field, the sort would interpret the number and place the sheetNo record accordingly.


                    Note: the second field does not have to display on your report. It is just used to define the sort order.


                    Sample screen shot attached.





                    • 7. Re: Sorting Numbers is Ascending Order

                      Hi Mike,


                      I know it's been nearly a year since you wrote this but I was having the same issue with leading zeroes (we're importing a lot of data from Excel and, unfortunately, FM is stripping out the leading zeroes because they're being imported into a text field.) I did what you suggested above and it seemed to work but then I noticed it was also adding leading zeroes to double-digit numbers (well, anything 11 and above. For some reason, it doesn't do it to tens.) I'll paste what I've litereally written below. Any ideas on fixing this?


                      Thanks much!




                      Case ( Filter ( Chapter ; "123456789" ) = Chapter and Length ( Chapter = 1 ); "0" & Chapter ; Chapter )

                      • 8. Re: Sorting Numbers is Ascending Order

                        Wow. I wrote a bad calculation a year ago.   


                        Try this instead:


                        Right ( "0" & Chapter ; 2 )



                        • 9. Re: Sorting Numbers is Ascending Order

                          Hi Mike,


                          Thanks for your response. Do you mean that's the entire, new, calculation script or that I should be replacing part of the earlier script with what you've  suggested above? Sorry to be so dense! I'm unable to play "developer" at the moment so I can't just test it out.





                          • 10. Re: Sorting Numbers is Ascending Order



                            FMP does NOT strip out data when importing. IMHO, you are mistaken in your understanding of the problem.


                            Excel does not recognize leading zero's in a number cell. So the value being supplied by Excel does not include leading zero's. The same thing happens when you export a number value with leading zero's in a text field from FMP and open it in Excel. Excel recognizes the value as a Number and removes the leading zero's.


                            The question is how long is the value supposed to be? Is the vale a Zip code, 5 characters, Social Security, 9 characters, Serial Number, unknown length, Etc.? Hopefully all the values in this field are the same length.


                            To get leading zero's means adding zero's before the value and truncating to the desired number of characters.


                            So we need to keep everything from the left edge and back up. For example if the maximum number of characters is 5:


                            Left ("00000" & field ; 5 )



                            However, what should happen if the field is blank? If it should remain blank, no zero's at all, then we need to test for that too.

                            What if the value is already over 5 characters?


                            Case (

                            IsEmpty ( field ) ;

                            "" ;


                            Length ( field ) < 5 ;

                            Left ("00000" & field ; 5 ) ;




                            • 11. Re: Sorting Numbers is Ascending Order

                              If you want the value to be 5 characters and include the field,  shouldn't  the formula be:

                              right("00000" & field; 5)


                              or to handle an empty field:


                              if(isempty(field); ""; right("00000" & field;5))