I am fed up with FileMaker's inability to allow searches for things like "lb" in a number field. The story goes like this: I inherited a database where some fields were text fields when they should have been number fields. Since then, I have corrected the field type and it is now a number field. Good, now people can't enter in silly things like "lb", "lb.", "LBS", etc. BUT, FileMaker leaves me stranded when I want to do a search for the non-numeric characters in the number field. The data is stil there, but because FileMaker forces the correct input type in Find Mode, I am left with either leaving the data as is, or unconveniently converting the field back to text mode to work with it.

My suggestion to FileMaker is to allow all types of searches in Find Mode. Only enforce the correct data type in Browse mode.

Can anybody identify with my problem, or am I just raving?

I'm guessing that the field in question was meant to store weight, and that users originally entered a number and unit, e.g. "5 lbs".

Based on this assumption, when you converted the file, you should have defined a separate field for units; then, using a script, carried the unit type (lbs, oz, etc.) to the new unit field and left the numeric data in the original, at which point you could convert the quantity field to strictly numeric.

This way, in your Find form you can allow users to find 5 lbs as opposed to 5 oz., and so on. They'd just have to use two separate fields.

That's exactly what I should have done, no doubt about it. But my real question is why Find Mode doesn't simply work with whatever I input, regardless of what SHOULD be there according to that field type. FileMaker should be smart enough to know that in some cases and scenarios, users would want to find data that really is there, but doesn't really fit the field.

I would much rather experience a "No records match this request" than "There are no valid criteria in this request. Type a valid request before clicking Find.".

Make an additional *text* field,

Replace its content with the content of the numeric field,

do your stuff in the text field,

Replace back the number field with the content of the text field and

finally delete the text field