Finding "1"

[TheTSart]

In FileMaker 9, I have a new problem I've never encountered before...

I have a field whose contents have values like:

1

1A

1B

1 - Small

In previous FileMaker versions finding those fields with "1" was quite easy. However, I cannot figure it out in 9... Here are some examples of my Find requests:

1

"1"

=1

="1"

=="1"

==1

All of these requests find ALL of the above values. I just want those with a "1" and nothing else.

What am I doing wrong? Thanks!

[Fitch]

Is it a number field or a text field?

[TheTSart]

It is a text field.

[comment]

Either one of the last two should work:

=="1"

==1

Make sure your version is updated with the latest patches.

[fabriceN]

is this a related field ?

[Fitch]

Are you doing a manual Find or is this happening in a script?

[TheTSart]

Yes, I'm up-to-date on software.

No, it's not a related field.

Both manual find and from a script give me the same results.

[comment]

Try re-indexing the field?

[TheTSart]

Try re-indexing the field?

Well this was just an example of one of the fields where this is so problematic due to the types of values, but it will happen in any field in any database I have.

[fabriceN]

Can you post a file ?

[comment]

I don't see where this is "so problematic due to the types of values". It's the same as finding "John" but not "Johnson" or "John Smith". It should work with the == field content match symbol - assuming it's a text field, and that your data is what you think it is.

Does this work for you?

FindTest.fp7.zip

[TheTSart]

I don't see where this is "so problematic due to the types of values". It's the same as finding "John" but not "Johnson" or "John Smith". It should work with the == field content match symbol - assuming it's a text field, and that your data is what you think it is.

Does this work for you?

Well, it's so problematic because any other field in the database I'm not going to be typing a single character in to find something. In your example, finding Johnson from "John" is better than also getting Jackson, Ajay, etc. in the results.

And no, in 9, I no longer have the ability to find an exact match in any field. I just talked to a friend of mine over the weekend who also uses 9 and he doesn't have the ability either, he just didn't realize it until I asked him to try it.

[mr_vodka]

And no, in 9, I no longer have the ability to find an exact match in any field. I just talked to a friend of mine over the weekend who also uses 9 and he doesn't have the ability either, he just didn't realize it until I asked him to try it.

I just dont quite understand where you are getting these broad assumptions from. Frankly, I dont even quite follow what your issues are at this point. As others have said ==1 should give you the result that you wanted.

Have you tried reindexing the field as suggested? Why dont you post a copy of your file as fabrice suggested. We cant help you if you arent giving us the feedback from the tests.

[David Jondreau]

I'm taking a guess here and saying the OP has defined these fields as number fields or calc fields with a number results.

That would produce the results he's seeing, though that's no different than previous versions of Filemaker and is completely logical.

[mr_vodka]

But in their 2nd post, they answered Tom as saying that it is text.

[David Jondreau]

"Well, then, I have no idea..." Except that the OP isn't telling us something...

[mr_vodka]

:iagree:

[RPCia]

TheTSart

I ran a quick test with a new file and yield the same results as you in both versions 8 and 9...

=1 works as you would like it to except when you throw "1 - small" into the mixture then it also finds the fields with that value.

I believe the reason why ==1 does not work is becuase there are no fields with just the character 1 in them.

take this example text field

record 1

1

1A

1B

Record 2

1A

1B

1 - Small

Record 3

1A

1B

=1 return 1 and 2

==1 returns none

I see 1 of two solutions

1) two find requests

first one =1

second one omit =1 -

2) use a calculated field or a scripted find to throw out any of the values with "1 " and then something following it flag those one way and the others another way

hope this helps

[comment]

I believe the question was how to find the record with just "1" in the field and nothing else. If the field has multiple values within the same record, you would have the same problem in previous versions too.

[David Jondreau]

I still don't understand what the problem is.

Here's a test file, created in FM9A. For me, finds work as expected.

Four records each with a single value:

Rec 1 = 1

Rec 2 = 1A

Rec 3 = 1B

Rec 4 = 1 - Small

Finding on:

1 gets all records

=1 gets Recs 1 and 4

==1 gets Rec 1

"1" gets all

="1" gets Rec 1 and 4

=="1" gets Rec 1

1test.fp7.zip

[Vaughan]

DJ, the problem is that *all* of those values are in one record, paragraph-delimited (I assume).

This is really a case of poor data design, not problems in FMPs search process.

Split the multiple values off into related records, display them in a portal, and life will be good again.

Oh, and generating meaningful reports will be sooo much easier.

[comment]

Vaughan, please read previous posts before replying.

[David Jondreau]

I think Vaughn may be considering the OP as an "unreliable narrator," (perhaps I think that because I think that's the case) which is justifiable considering the facts as presented, but frustrating from the perspective of us someones trying to help.

I would not be surprised if the field contained multiple values or if it was not defined as a text field, despite what's been written. From my support experience, when things seem very unusual, it's good to do away with all assumptions.

It seems however, those assumptions cannot be examined as the OP has fled the thread.

[comment]

I would not be surprised if the field contained multiple values

Me neither - but this possibility was already discussed.

[Vaughan]

Comment wrote: "Vaughan, please read previous posts before replying."

What, and change a lifelong habit? :)

[comment]

Yes.

:joust: