5 Replies Latest reply on Jun 8, 2009 11:13 AM by comment_1

    Filemaker 10 Sort with Number Fields

    LarsVandegraaf

      Summary

      Filemaker 10 Sort with Number Fields

      Description of the issue

      I have a field that is a serial number field, I had to make it a number field instead of a text field so it avoided sorting 1, 10, 100 etc.    Here's a neat feature:  Some of the instances in the field are all numbers, some are combinations SS-number, some are T2-number.  If you sort by serial number, the SS's go to the top, all numbers in the center, T2's to the bottom (thats kind of freaky but hold on it gets really freaky).  If you change SS to SS1 so it will move those records down to the bottom of the sort with the T2's you better use enough leading 0's in the number portion of the serial number because Filemaker thinks record SS1-1 is a duplicate record of 11 and SS1-2 a duplicate record of 12 and so on. Please don't make comments like you should design a better serial number scheme as this is a conversion project for a customer thats want to keep the serial numbers the way they are and they don't want to see another serial numbering system with a new field. 

        • 1. Re: Filemaker 10 Sort with Number Fields
          LarsVandegraaf
             Mac OS X 10.5.6
          • 2. Re: Filemaker 10 Sort with Number Fields
            mrvodka
              

            This is not a bug. It is expected FileMaker behavior.

             

            As a number here are some of the different scenarios pan out.

            "T2-123-45-6789"    21423456789

            "TT-123-45-6789"    -123456789

            "SS1-1"                  11

            "ASDF1-AS YYYY1"   11   

             

             

            That is why when you save just SS-######### it will sort to the top.

            • 3. Re: Filemaker 10 Sort with Number Fields
              LarsVandegraaf
                

              Thanks for the quick answer. 

               

              Well that's nice that the expected behavior is based off Social Security number use.  Pretty much an arbitrary decision.  I would suggest strongly that a click box to turn off this feature be added to the next release.  Unless of course Filemaker was made just for Social Security Administration.  You can see by my example how that is not user friendly. I wonder how many more hidden gems like this haunt Filemaker. Does Filemaker sell different versions to different countries based on Government Policies?  Smells like boutique software. Do you understand what I am saying?  It is an arbitrary decision that could just as easily been handled by each application engineer.  IT'S AN EXCEPTION TO THE RULE!  Which means it is not intuitive or user friendly.   I don't know how Filemaker's Usability Group let that one through.  BTW, SM-0001 numbers also migrated to the top, I guess its keying off the 1st character being S.

               

              By your example there is no simple way to sort a combination of alphanumeric fields, if you make it text you get 1, 10, 100.  If you make a number then you get the SS1-1  11 feature. 

               

              Peace, 

                

               

               

              • 4. Re: Filemaker 10 Sort with Number Fields
                mrvodka
                  

                I think you have missed the point here...

                 

                FileMaker is forgiving for the most part of what is entered into a text field or number field, whereas other DBs are strict with thte data types.

                 

                So, if your field is a number field, upon an evaluation it will ignore the alpha characters...However the dash is special since it can indicate a negative number...

                 Therefore if a dash is IN FRONT of a number, then it will treat it as a negative number. However, if the dash comes AFTER a number, it will assume a possitive? Does this clear things up?

                • 5. Re: Filemaker 10 Sort with Number Fields
                  comment_1
                    

                  John (Mr. Vodka)  is correct: this is not a bug, but a result of entering text into a Number field. You should store your serial "numbers" in a Text field, and define a calculation field for sorting purposes (since you obviously don't want the alphabetical sorting applied to Text).

                   

                  The exact calculation depends on how you actually want your data to be ordered - something you neglected to mention.