8 Replies Latest reply on Jan 1, 2014 7:12 PM by Gr8data

    Allowing a hash sign "#" in a decimal field

    Gr8data

      Title

      Allowing a hash sign "#" in a decimal field

      Post

           Good afternoon. 

           I have a requirement, where I want to import values in to a decimal field eg 100.00, the source data might be a text field "100" but when it comes into FM, I want the value to be 100.00, which it does quite nicely when the field has been set up as a decimal field.

           The issue is the source data also contains a # in the field which represent an invalid value, which we want to keep. Any ideas how we can keep the decimal field but allow either a decimal or a # ?

           Regards

           David

            

            

        • 1. Re: Allowing a hash sign "#" in a decimal field
          philmodjunk

               Import the data into a text field. But then use calculations to "split" the data into a text field for the # if it is present and a number field for the decimal formatting. These can be two calculation fields or a text and number field with auto-enter calculations. If you use auto-enter calculations, be careful to enable auto-enter options during import.

          • 2. Re: Allowing a hash sign "#" in a decimal field
            Gr8data

                 Thanks Phil

                 Looks like I'm nearly there.

                 The challenge I seem to have now, is that when I look at the "Number" field it looks like it is displayed correctly, eg 100.00

                 However, the source value is 100, and so when I run a calculation on the field to display it in a separate text field it brings in 100 without the decimals. But when I retype the 100 as 100.00 then the calculation works and brings it in as 100.00. So it looks like the number field stores the source, and and only displays as a decimal field.

                 What do you think I'm missing

                 Many Thanks
                 David

                  

                  

                  

            • 3. Re: Allowing a hash sign "#" in a decimal field
              philmodjunk

                   100 and 100.00 are the same value. The difference is a matter of formatting. Why is the difference between 100 and 100.00 significant?

              • 4. Re: Allowing a hash sign "#" in a decimal field
                Gr8data

                     The reason is because of the format we need to supply the client. I agree same value, but we do need the zero decimal places

                     David

                • 5. Re: Allowing a hash sign "#" in a decimal field
                  philmodjunk

                       Yes but what you describe IS supplying the needed decimal places. I'm trying to figure out specifically what problem is created by the fact that when you click in the field, you see the original value and you see the formatted value when you exit. This is expected behavior for FileMaker data formatting so I'm trying to understand the nature of the problem that this creates for your client.

                  • 6. Re: Allowing a hash sign "#" in a decimal field
                    Gr8data

                         Thanks for this.

                         The challenge I have is that (as an example below ) is that when I export I end up with the same value as I import if that makes sense?

                         Source data (Field1) can contain 100, # or Null The end result I'm trying to achieve is in a different text field should be the result 100.00 or # or Null

                         I can split Field1 into 2 fields the first a decimal/number field to convert 100 to 100.00 and the second to house the #.

                         Then using your earlier tip was to bring them back into a separate text fields to get 100.00 or # or Null

                         I understand there's no difference between 100 or 100.00 but unfortunately I do need to have the decimal places

                         Hope that makes sense.

                         David

                          

                          

                          

                    • 7. Re: Allowing a hash sign "#" in a decimal field
                      philmodjunk

                           That you need this for export, is the detail not given in your original posts. Text calculations can be setup that supply the needed trailing zeroes after the decimal, but I'd first try to use the "apply current layout's data formatting to exported data" option as that's much simpler.

                           Here's a calc that could be used in cases where this fails:

                           Let ([ x = YourNumberFieldHere ;
                                    I = Int ( x ) ;
                                    d = Mod ( x ; 1 )
                                   ];
                                     I & If ( d ;  Left ( d & "0" ; 3 ) ; ".00" )
                           )

                      • 8. Re: Allowing a hash sign "#" in a decimal field
                        Gr8data

                             Happy New Year

                             I will take a look shortly.

                             Thanks for your help

                             David