Is ClientCodeNumber a field of type text?
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 of 3 people found this helpful
If leading zeros are important, it is not "number" in database term. You should use text field instead of number field.
1 of 1 people found this helpful
You can use COALESCE to get the Values as they're entered:
ExecuteSQL ( "SELECT COALESCE(clientCodeNumber,'') FROM Clients ORDER BY clientCodeNumber" ; "" ; "" )
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.
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. ;-)
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.