5 Replies Latest reply on Apr 16, 2017 8:51 AM by philmodjunk

    Is it safe to rely on text data in a number field?

    smith7180

      I have a number field in a price database that contains the price of the product.  It has to be a number field as I rely on the properties corresponding properties of a number field.  My issue:  I would like to store the currency in this field as well, and so I'm exploring whether there are any known issues with doing this. 

       

      When a user inputs data into this currency field, a script trigger does a few things:

      • Looks to see if 1 of 5 accepted currency symbols prefaces the input ($, £, etc…).  Makes note if this is the case.  If not, the value is considered to be in the default currency.
      • Next it does typical validation stuff like stripping out non-numeric characters.
      • Finally it formats the currency according to the conventions for the selected (or default) currency.

       

      My question is this: are there any known pitfalls to relying on text stored in a number field like this?  For example, if I add two of these fields, the output will not have the currency symbol  This is desired behavior, and I simply format the output with other scripts.  My concern is that some other action down the road (like importing) will strip out the text characters.

       

      In short, is it problematic to work with and rely upon text data in a number field?

        • 1. Re: Is it safe to rely on text data in a number field?
          philmodjunk

          It's not an option I would choose. I put numbers in number fields and text in text fields. A calculation field can be used to display the formatted value with specified currency symbol while a number field referenced in the calculation can be edited to manage the numeric value.

          • 2. Re: Is it safe to rely on text data in a number field?
            user19752

            I can't imagine another reason why number field can have text value, that is user can enter currency symbol. But I think this is most bad decision on first time FM is developed.

            • 3. Re: Is it safe to rely on text data in a number field?
              beverly

              There IS a quirk (that can be leveraged, should you so desire). IF you have a number field and allow text, the sorting is different than if the field is text.

               

              • empty

              • numbers (numerical order, leading 0's ignored)

              • text (case and alpha ignored, uses creation order)

               

              This may be advantageous when you need a numerical sort (and do not have fixed width characters):

              001

              2

              03

              6

              17

              101

              1000

              (text - alpha - sorting):

              001

              03

              1000

              101

              17

              2

              6

              Just FYI, make no assumptions, do what you will with the information.

               

              Check numbers, postal codes - I typically have as text but there may be a need for numerical sorting. (clients ask for all kinds of things!)

              beverly

              1 of 1 people found this helpful
              • 4. Re: Is it safe to rely on text data in a number field?
                BruceHerbach

                I would not put the symbol in the number field.  Instead either use a text field with the symbol or use the field set up in the inspector display the symbol.

                 

                Or a combination of the two by using the hide field when option in the inspector data field.  Stack a set of the number fields on top of each other set up for each symbol and use a Hide formula of PatternCount (  symbolefield; "desired symbol here" ) = 0.  This should show the correct version.

                 

                HTH

                • 5. Re: Is it safe to rely on text data in a number field?
                  philmodjunk

                  Note that no one has said that it is unsafe to put text into a number field.

                   

                  FileMaker's "loose" data typing can lead to confusing text, numeric and other data types when sorting or setting up a calculation. That's the main danger to mixing text and numbers in the same field.

                   

                  Including a currency symbol with a number seems pretty low risk.