Exact Match (==) says no records found!

[cmartin]

I have a field (Decision) whose value is selected from a value list. Two of the possible values are Exempt and Exempt + Pts. On many finds, I want to omit Exempt while including Exempt + Pts. The obvious solution is to Omit where the field Decision has find criteria: ==Exempt.

To test this, I did a simple find (not an omit) where Decision had aforementioned criteria (==Exempt) and I get no records found message when I know I have 200+ records that match this criteria. If I leave out the exact match symbol and just find on Exempt, then it returns the 200+ where the decision is exempt as well as the 100+ where the decision is exempt + pts. Anyone know what could be going on? I'm going crrrrazy!

[Vaughan]

Don't fiddle around with omits.

If you want to find the "Exempt" records, search for "==Exempt". If you want to find the "Exempt + Pts" records, search for "== Exempt + Pts".

What you've *gotta* do is change the value list items to something that won't get mixed up like this! It's a recipe for disaster, and makes it harder for users as well.

[cmartin]

Vaughn...thanks for the advice.

However, I must have an omit as I actually have about seven different values in my Decision value list and most of the time I want to find all records except those whose value is "EXEMPT."

As for changing the value list values so that users do not get confused, this is somethng I would love to do, however, I have inherited a disastrous db as well as a stubborn boss who does not like chnages, so that soln is not so feasible, unfortunately.

Which leads me back to my original question: does anyone know why exact match would not work? I have tested it in some other fields and seem to be getting the same result (no records found) no matter what. is this a bug? I'm using FM Pro 4.0.

Thanks a bunch!

Courtney

[Vaughan]

When stuff like this happens it usually menas there is a flaw in the logic somwehere -- user error, not FMP.

Are you *sure* that there are records where the *only* field entry is "exempt" no spaces or anything else? because that's what you are asking FMP to find...

[cmartin]

User error is what i thought, too...but I created a new record and typed in "exempt" myself with no spaces, nada and....nope, FMP wouldn't even find that one.

Also, I'm not sure if I mentioned already that while trying to solve this conundrum, I have tried using the "==" in a variety of other fields and no matter which I use, I get the no records found message.

I though perhaps the value list was causing the problem ( a space in there somewhere) but I've checked that as well and this does not appear to be the case.

[BobWeaver]

Double check that your field is defined as text and not number.

If it still doesn't work, try recovering your file and then try again. When Finds start behaving strangely, it's a sign that the index may be corrupt.

[slstrother]

Are your databases hosted on FM Server 3 ?

If so, the following from the FM 4 readme may apply

Databases hosted by FileMaker Pro Server 3.0 do not support the new Exact Match feature of FileMaker Pro 4.0. FileMaker Pro Server 4.0, currently under development, will support this feature. Until FileMaker Pro Server 4.0 is available, use FileMaker Pro 4.0 to host your databases if you require this functionality. All other features new to FileMaker Pro 4.0, such as the Open URL script step, JIF/JPEG support, and hyperlink support, do work correctly in FileMaker Pro Server 3.0.

[Vaughan]

Server 4 never eventuated. The next version, Server 5, requires a file format change and is not backwards compatible.

[cmartin]

thank you slstrother!

i'm NOT crazy. (or at least in regards to this, i'm not) yes i am using FileMaker Pro Server 3.0 to host, which I guess now explains why exact match is not working.

so now that this mystery has been solved and in turns out i will NOT be able to use EXACT MATCH, i'm back to the drawing board for a solution.

so if anyone out there has any hints on how to find those records where the search field = "Exempt + Pts" while omitting those where the search field = "Exempt" i'm all ears (or eyes, in this case)

[slstrother]

One way is to create a calc field that will flag the records where the "Decision" field contains "exempt" with one value and the other records with another value. Then perform a find on the calc field. As an example, create a calc field called "Decision_Flag", result of number with this calc:

If(Decision = "exempt",0,1)

This will result in 0 for the records with "exempt" and 1 for all others. Then perform a find on this field =1. The "exempt" records will be omitted.

[cmartin]

it is true that this solution would work, however, i am trying to avoid finding on calc fields, as their inability to be indexed really slows down my reports.

thanks for the idea, though!

-courtney

[Vaughan]

Calc fields can be indexed as long as they don't reference a global or related field.

I reckon you have two choices: change "exempt + pts" to something unique as I suggested; or use a calculation field as slstrother suggested (though I would use the Exact function).

BTW slstrother, I never congratulated you on your excellent detective work, deducing the FMS3 problem. Well done!

[cmartin]

yup, my field is a related one. guess i'll have to convince that boss of mine to change exempt+pts to something unique. thanks for all the help, folks.