9 Replies Latest reply on Mar 11, 2015 1:05 PM by beverly

    missing zero


      I've just uploaded data in to FM and it appears that some of my numbers have been truncated. The numbers that have been changed are all starting in zero. So a number like 0.001 is now in FM as .001  Does anyone know how to write a script as to put the zero back in front of the decimal point.

        • 1. Re: missing zero

          In layout mode click on your number field , go to the inspector under data formatting then change format under data formatting to Decimal and see if that fixes your issue.

          • 2. Re: missing zero

            The problem is where you are importing them from.  You may say zeros in front of them, but what actually imports may not be zeros.  FileMaker does something that many databases do not, it stores the number as you type it as well as what the exact decimal value is.  It then allows you in layouts to use formatting to view the numbers with different formats. 


            As Schamblee points out, you can use Layout formatting to change the appears on a given layout using the Decimal formatting because it defaults to a leading zero. 


            If you want the As Entered value to be 0.001, you then you can do a loop that checks for each number to see if the number begins with a period and add a zero to the beginning. 


            I would solve the problem with layout formatting though. 

            • 3. Re: missing zero

              Hello all,  Thanks for the reply.  It appears to be all good now.  However...... numbers that were down as <0.001 now show as just 0.001.  If i click on the cell I can see the < sign.  Is there a way to show the < sign all the time?

              • 4. Re: missing zero

                If you set the field display to As Entered or General (in the Data Inspector) the < should show.

                • 5. Re: missing zero

                  Ye I tried that. The >signs becomes visible but the zeros on the front of the number disappear again.

                  • 6. Re: missing zero

                    Hmm! Can't have it both ways, eh? I assume from what you describe that the leading zero was there in the data uploaded, but was dropped by FM during import. Assuming it really matters, you might need to set up a looping script to re-enter the offending numbers—i.e. put back the leading zero—as per Taylor's suggestion.

                    • 7. Re: missing zero

                      Technically there is no number such as "<0.001".  That is a character field if anything.  When you tell FileMaker to treat a field as a number field and there are characters in it, it just ignores the characters.  So to FileMaker number field, "<0.001" really is "0.001" or "ABC123XYX" really is "123".  Yes, you can tell FileMaker to display it as entered, but when you do math or sorts on it, it will completely ignore the non-numerical characters (except the minus sign) and only evaluate the numbers. 


                      If you need to have less thanks, you really need to have a separate field to identify when a number is less than and not have that in the number field because it is not a number. 

                      • 8. Re: missing zero

                        Spot on.

                        • 9. Re: missing zero

                          and there is an "evaluate()" function that may allow you to have a TEXT field with formula-type values. Then just Evaluate(myFormulaField) into a variable or number field.