Text fields for numeric data?

[LaRetta]

Here's another easy one for you all but I would like some clarification. The first thing I've noticed that I dislike about numeric fields, is FM doesn't like to find * or =

Secondly, I will be performing exports to tab-separated text files for electronic billing and my dollars are currently numeric. But I have to use NumToText and formulas to force the .## on export. That's okay, I guess but it brings up my question:

When is it required for a number to be numeric to function, i.e. summaries, etc. and when can I take advantage of using a text field instead. I understand zip codes & phone numbers should be text but I'm not always clear on the distinctions. I see frequent posts telling users they should have used text instead of numeric, etc. Any input would be really appreciated!

[CobaltSky]

Hi LaRetta,

The * and = operators in find mode are text operators, and number fields and text fields are indexed, sorted and searched differently in such a way that number fields don't require them.

Firstly, number fields always search literal (=) so if you search on 6 in a number field, it will only return exact values, whereas if you search on 6 in a text field it will return any or all of the following:

6, 61, 62, 667, 69561 etc etc

which is not terribly helpful when you are dealing with numbers.

Since all number searches are already exact (=), the only time when you need to use the "=" operator in a number field is on its own to search for an empty/null field - and it works in that case.

Similarly, the * wild card character is not generally needed in a number field because instead you would generally be better off searching by 'range'. If you enter 690...699 it will find anything between those two numbers (inclusive). Just a different way of handling a similar need, using the 'numeric paradigm'.

Another thing that will trip you up if you define all your number fields as text fields is that the sorting mechanism for numbers is quite different than for an alphabetic sort. A text field uses alpha-sorting, which groups everything starting with the first character, then moves on to the second character.

If you apply alpha-sort logic to numbers, then everything starting with 1 will come before everything else, so for instance, "11" and "156" will sort between "1" and "2" in an ascending sort on numbers in a text field.

The way around these issues is to use calculating fields with the NumToText( ) and TextToNum( ) functions to convert data types when you need to, but as a general rule, they are best stored in the field type that is designed for them.

[LaRetta]

Thank you for taking the time to clarify for a newbie ... well, I guess I'm no longer a newbie ... I've been using FM for 1 1/2 months now!