Text in numbers

[LaRetta]

I understand the theory behind displaying a number field in a value list. FMI sees only the number in the field. Then it takes the first entry (of that number) and displays that in the value list (including any garbage with it). By garbage I mean non-numbers.

So in attached:

-1234 is seen first as -12aazzzz34 so it displays. ---aa12 a ---34 isn’t seen as separate number (but as -1234). 1234 is first seen as a1234a so that displays as well.

But why is Monday showing in the pop-up? Well, the first time (and only the first time) that any straight text appears in a record, it displays in the value list. Notice that the dash and slash won’t display. But if the slash was the first non-number, it would display instead of Monday. I consider this a bug.

So whenever text exists in a number field, you can be easily deceived. What displays in the value list is NOT one of each unique value (as it is in text fields). You will be shown that a1234a is in the field but not that /1234 is in the field so you can't search for /1234 because you don't know it's there.

You can be further deceived when you trust CTRL-I to display the index. On this particular set of records, CTRL-I displays the first and second entries in the value list but drops the Monday and produces ? (as expected). Why is CTRL-I (which is supposed to show the index) and the value list different? CNTR-I shows only true numbers whereas value lists show first number (including junk). It strikes me as inconsistent.

Text in numbers is further complicated by searching for ?. In this example, ? finds those records with only text. But how can we find mixed type to clean it up? We must create a calculation, something like:

Length ( num ) ≠ Length ( Filter ( num ; "0123456789" ) )

… then search for 1. Text in number fields also causes major problems with external targets. An Insert statement will drop the entire record if it includes text in a number field. It is easier to keep your data clean to begin with. However, even using a pop-up on the field, it can’t be protected because someone can paste into the field and paste text. Script trigger or field-level validation seems the only true protection (until FMI fixes it).

BTW, this is the pattern in all FM versions 7-11 (Windows XP).