7 Replies Latest reply on Feb 14, 2017 7:02 AM by jakebutt14

    ExecuteSQL - Number with leading zeros


      Hi everyone -


      I'm using ExecuteSQL to retrieve data from a number field which has the possibility of leading zeros (non-decimal). I'm getting the expected list of values, except those values with leading zeros are having the zeros stripped off. Here is my function, it's pretty simple:


      ExecuteSQL ( "SELECT clientCodeNumber FROM Clients ORDER BY clientCodeNumber" ; "" ; "" )


      Anything I can throw in there to make sure these numbers don't get the zeros stripped, or am I SOL with ExecuteSQL? (near pun intended). I have found some other discussions regarding this happening with leading zeros with decimals (i.e., 0.99 turning into .99), but all of my values are whole numbers.


      Thanks in advance.

        • 1. Re: ExecuteSQL - Number with leading zeros

          Is ClientCodeNumber a field of type text?

          • 2. Re: ExecuteSQL - Number with leading zeros

            Out of curiosity, is it sorting as a text field or as a number field?


            While a bit annoying, it wouldn't bother me too much.  When I store numbers back or otherwise use them in variables, I can always add the leading zeros back in with the calculation such as Right ( "000000" & clientCodeNumber ;  6 ) if I wanted whole numbers padded with zeros up to 999,999. 


            One thing I do wish FM would do in the Number formatting in the layout formatting Inspector is to have more formatting choices including adding leading zeros to numbers. 

            • 3. Re: ExecuteSQL - Number with leading zeros

              If leading zeros are important, it is not "number" in database term. You should use text field instead of number field.

              3 of 3 people found this helpful
              • 4. Re: ExecuteSQL - Number with leading zeros

                You can use COALESCE to get the Values as they're entered:


                ExecuteSQL ( "SELECT COALESCE(clientCodeNumber,'') FROM Clients ORDER BY clientCodeNumber" ; "" ; "" )


                best regards


                1 of 1 people found this helpful
                • 5. Re: ExecuteSQL - Number with leading zeros

                  As usual, multiple ways to accomplish the same thing.


                  user19752 : You're absolutely right, hate when it comes to something simple like this, but this was the simplest answer. I don't have any real reason to need the client code to actually be a number field, and changing it to text immediately made my existing SQL function display the numbers with leading zeros (as entered). Something I should have thought of, but thanks for pointing it out. I'll keep this in mind in the future with numbers I know I'll need leading zeros.


                  okramis : This also worked and is a technique I need to keep in mind. Thanks for expanding my SQL knowledge!


                  Thanks to everyone for the help, great community on here.

                  • 6. Re: ExecuteSQL - Number with leading zeros

                    Keep in mind that most databases will simply NOT allow non numeric values in a number field.  Filemaker is a bit loosey goosey this way. You can force STRICT Numbers in FM by using the validation options in the field setup. Leading zeroes are not numbers.  You figured that out which is great, but this can bite you in the butt other places if you are not careful.  ;-) 

                    • 7. Re: ExecuteSQL - Number with leading zeros

                      Appreciate the insight, and point taken. Luckily, this field will never need any type of numerical calculations (sums, averages, etc.) performed on it because it is, more or less, just an information field like most other text fields. But you're right, certainly something to keep in mind.