Finding in a related field

[smoozy]

I'm pretty new to FMPro so this might be a dumb questions.

Say I've got a recipe table with a related ingredients table. If I enter find mode and type "beef" in the recipe name I get all recipes that contain the word "beef" anywhere in the name, like "BBQ Beef" and "Beef Stew". But when I do an ingredient search for "beef" in the same layout I only get records returned where the ingredient name is exactly "beef". "Beef steak" and "ground beef" aren't found. I know that my relationship is set up correctly because if I search for an ingredient like this: "*beef*" I get the expected results.

Am I doing something wrong? Is this just a quirk of FMPro? Is there a workaround that doesn't require my users to type wildcards around an ingredient search?

[Ender]

This type of search should work without the wildcard. There are no other search criteria involved?

[smoozy]

Nope. I just made a new layout that only has the "name" and the "ingredient" fields.

- Typing "beef" in name worked as expected.

- Typing "beef" in ingredient got no records, because I don't have that exact phrase as an ingredient.

- Typing "*beef*" in ingredient worked as expected.

- Typing "salt" in ingredient worked as expected because I have an ingredient named "salt" exactly.

[smoozy]

Grrr...I just made a brand new FMP file with a very simplified version of the tables and it does work without the wildcard. I guess now I have to figure out what's different between the two.

[smoozy]

I'm a little embarrassed to report the final solution, but remember that I'm a newbie.

Apparently, and I'm sure this is no surprise to most of you, you can put text in a field defined as a number. My ingredient name field was defined as a number (why? I have no independent recollection...) and changing it to a text field fixed my problem.

I defend myself by stating that no other database I've ever used has allowed you to store text in a numeric field. You usually are notified of the error the first time you try to put in invalid data. Is there a historical reason that FMPro makes you specify that you only want numbers in your number field?

[dbruggmann]

I know of a potential

[The Shadow]

Uh, that's pretty interesting.

It appears text searches can operate on a number field, but only if the find doesn't also contain any numbers - I don't think FM6 did that.