Find text in number field

[LaRetta]

I needed all records which have text in a number field. No prob, says I ... we can find text in number field easily. Not so. Attached is a small sample file to play with. If there is a comma or a space within the text, we cannot find it. I am trying to find (specifically) data like this:

Long, Lucas (LLUCA01)

Smith, Wil

Nelson, David

I can find it with *,*. But it got we wondering about why the following examples won't work:

I can search for a and find a but not a, nor a, L.

I can search for ? but it doesn't find 34a or a 34 or Long, Lucas (LLUCA01).

I can search for a, L but it doesn't find a, L nor a, nor a.

I assume the principle is something like this:

1) Number field assumes number which is ONE word. So with two words, the subsequent words are ignored (or invalidate the entry).

2) Number doesn't acknowledge space so data with space is ignored.

3) Number CAN find a, because it *is* the first word and, although the a is ignored, the comma is valid to number.

But if these statements were true, the search for a,L should have found a,L because it is first word (no space) and it contains a comma. Also, a search on ? should NOT have found Nelson, David but it did.

Of course the answer is NOT to mix data but when working with external sources and cleaning up older systems, that isn't always possible. And as in my case, if there isn't a comma, I'd have trouble using a straight find (I believe); I would have to create a calculation with PatternCount() or Filter() and test the length of the result to know whether it contains text.

If anyone can help me pinpoint the principle here so I don't have to keep a list of exceptions (as we have to for word separators and invalid characters), I'd appreciate it. If I can nail down the principle, it will be easy to always produce the correct found set when needed. Thank you!

LaRetta

TextInNumber.zip

[databaser]

i think one of these 3 things is the right answer:

1.

but what is the advantage of keeping it as a number field rather than making it a text field?

2.

Are you sure this isn't easy; maybe i don't understand?

can't you do something like this:

If (Length (num) = Length ( Filter ( num; "0123456789")); "NO" ; "YES" )

or couldn't you use the filter and/or substitute functions to do this any number of ways?

I attached an example. it seems to work, unless i don't understand.

3.

create a text calculation field that = num

TextInNumber_Copy.zip

[Raybaudi]

Hi LaRetta

you can find the answer loking at the index of that field. ( Insert>>From index... )

Your best way to solve the problem definitely is to make a calc fiel:

GetAsText ( num )

( the new field will have its index correct )

[LaRetta]

Hi databaser,

Our ID fields are number. This ID is number through many tables as foreign key and I don’t plan to change it to text just because of this issue.

Hi Daniele,

First thing I did was view the Index (except I always just use CTRL-I). I don’t see how that tells me anything about the principle here, can you explain further? Because if the Index shows what should work then I should be able to search for Long, Lucas LLUCA01 and find it but it won’t.

Truly, I’m not asking for how to find in this instance … I realize I can make a calc field to get the answer. But simply using GetAsText ( num ) and then searching on this wouldn't give me the results of those fields which are NOT straight number either ... I'd need to use something like:

GetAsText ( num ) = Filter ( num ; 123456789 )

... then search on the 1. It is the underlying principle on why searching is inconsistent that I want to understand here.

[LaRetta]

Hi databaser, I just saw you changed your response. Yes, as I said originally, I realize I could use a filter calc and many calcs to get the desired effect. My question has to do with principle and lack of consistent pattern (at least no consistent pattern on theory that I can spot).

I appreciate your responses though!! :wink2:

[databaser]

I believe the creators of filemaker simply intended that such data be contained in a text field.

If you look at the help topic "About choosing a field type"

It says this:

Text

Up to approximately 2 GB of letters, symbols, and/or numbers used as text per field repetition, limited by available RAM and disk space. Indexes nominally based on the first 100 characters of each word or value. Text fields may contain carriage returns.

Number

Supports values up to 800 digits or other characters, and the negative values of the same range. Index is based on the first 400 significant digits. Number fields can also contain Boolean values, to indicate, for example, true, false, yes, and no. Number fields can't contain carriage returns.

Is there really ANY disadvantage to making it a text field?

... Other than that, you could solve with a calculation.

[LaRetta]

I believe the creators of filemaker simply intended that such data be contained in a text field.

If what you say is true, then why does FileMaker allow mixed data in number fields? What you say sounds fine in theory but that is NOT reality when data arrives from outside sources and during migrations. Again ... this is old migrated data that was missed during a cleanup. It is NOT SUPPOSED to have text in it and won't in the future! It is a number field and SHOULD be. It is no different than all the people that had used True, False, Yes, No, T, F, Y and N in a number field to determine boolean and, when vs. 7 came out, they needed to clean up their fields! Reality is reality.

Again, my question is not how to strip it; you keep missing my words; I'm able to strip it just fine. My question was about what appears to be inconsistent FIND theory in general. Hey, it's okay if you don't understand why it is inconsistent (neither do I and that's why I'm asking). And it's even okay if you don't see it as inconsistent behavior but I DO see it as inconsistent. I believe that, if you play with my examples, you will see exactly what I mean. :

Edited September 12, 2008 by Guest

[LaRetta]

... Number fields can also contain Boolean values, to indicate, [color:blue]for example, true, false, yes, and no. Number fields can't contain carriage returns.

Well, help is incorrect on the blue unless one is using versions 6 and older.

[comment]

Looking at the index can indeed reveal some of the rules. First, a field does not have an entry in the index unless it contains at least one numeric character. Next, if there is an entry in the index, it includes the entire field's contents (up to some maximum length). Only text fields have a separate word index.

Searching for ? in a number field finds invalid values. Those are the same values that do not contain any numeric characters.

Searching for "Long, Lucas LLUCA01" (without the quotes) does not find anything because it's looking for records that contain a word that start with "Long" AND also a word that starts with "Lucas" AND a word that starts with "LLUCA01". Since there are no words in the field's index, it will fail.

I believe you can find practically anything, if you force the search to unindexed by prefixing the criteria with == and/or using quotes.