Find returns incorrect result set.

[CCBtx]

I have a field with "Y", "N" or <empty> as values.

When I search on the field for "Y", I get all of the Y's back and one of the "N"

There are many more records with N in the field.... I just get one... the same one every time.

I am performing the search manually.

The environment is FMP 15 against a FM Server 15.

Any ideas?

[comment]

Is it possible that the field actually contains something like:

N

Y

?

 

[CCBtx]

I thought that... It doesnt.

And... I just checked it.... no matter what I put in the field, it gets returned when I search on "Y"

So... I put an "X" in the field... the record gets returned on a "Y" search, but not a "N" search or "=" search.

...it also returns on a "X" search.

I guess I will just delete it and re-enter.... but I wish I knew the cause.

[LaRetta]

That is PRECISELY what you have, CCBtx (what Comment suggests) ... one of your records contains both values (N and Y).  Since you are performing a find for 'Y', FileMaker finds that record too.  FM, when searching, treats a multiline (any field with multiple values) as two separate searches.

Find that record, place the field on the layout as set it as 'edit box' instead of checkbox and increase its height so you can see all values within the field.  You'll need to protect from multiple values being selected.  There are a few ways to accomplish that but we couldn't recommend without knowing more.  Are you using a checkbox or toggle script to set the value or ?

 

[CCBtx]

LaRetta.... that is not what is happening here..... I have expanded the size of the field box... done Ctrl-A + Delete, re-entered "N" and "X".... made sure my cursor does not go past that 1 character.... still the same result... no matter what I have in that single char field... the record gets returned with a "Y" search.

 

...if this makes a difference.... the field entry is controlled by a radio button that only has "Y" and "N" as an option.

To test the problem with the search function.... I created a Edit Box field right beside the radio button.

So... When I click the "Y" or "N" in the radio button, it obviously changes the value in the Edit Box to the corresponding value, "Y" or "N"... so I am very confident that there is no other value in there separated by a CR or LF

[comment]

Well, I don't think there are too many options here. At least I cannot think of any except:

• the field does contain a word starting with "Y"';
• the index is corrupt;
• the file is corrupt.

 

Edited June 14, 2021 by comment

[CCBtx]

I suspect #2 or #3.

Can I rebuild the index without copying the database to a new one?

[comment]

4 minutes ago, CCBtx said:

Can I rebuild the index without copying the database to a new one?

Yes, just set the indexing to None + Automatically create indexes as needed.

 

[CCBtx]

Great.  Thanks

 

Re-indexing the field corrected the problem.  Thank you.