Problem with "hidden data" in field

[susan siegel]

For lack of a better term or phrase, I'll call my problem one of "hidden data"

When viewing a record, a field may "look" empty but it turns out there is "something" in the field because the record shows up on a "find" when, based on the find criteria I have entered, the record should NOT show up. (example: The "find" criteria calls for every record that has data in "field x" -- but -- some records come up that don't have anything in "field x")

When I suspect that the "find" results include a record that doesn't belong, I go back to the record, and even though the field looks empty, I delete all the spaces in the field again. This usually clears out whatever is 'hidden" so that the next time I do a find, the record does not come up.

But - this "solution" only works only when one or more records in the find results looks questionable. If I don't suspect a problem, then I don't know that there's error that needs to be corrected -- and I end up using a flawed "find" list of records.

Is there some way that I could "see" that there's something in the field even though it looks empty? Is there a preference like setting that might show something that is otherwise hidden?

What am I possibly doing wrong to create the problem in the first place?

thanks for any help with this rather mysterious issue.

Susan

[Raybaudi]

What am I possibly doing wrong to create the problem in the first place?

Hi Susan

usually a wrong "Return" inside the field or a "Space".

To avoid those errors you can use an auto-enter calculation into options of the relevant field; so, if the field is: FirstName, the calc is:

Substitute ( Trim(First Name) ; ¶ ; "" )

This is only an example; you have to adapt it to your needs.

[comment]

It seems that the flaw is not in find, but in your data. You haven't told us anything about your workflow, so I don't know how you manage to get spaces (or other invisible characters) into your fields. Perhaps you should concentrate on that.

Meanwhile, you could set up a calculation field =

Case ( not isEmpty ( field x ) ; "some type of warning" )

[mr_vodka]

Susan,

You can use a field validation to prevent users from entering a space, empty data, or empty carriage returns. You can use something of a hybrid from both Daniele and Comment's solution for the formula.

not IsEmpty (Substitute ( Trim(YourField) ; ¶ ; "" ) )

However, as comment pointed out, you should post how the data gets like that to begin with if its not error by user entry.

[susan siegel]

Many thanks to all three responders. (I hope this one response suffices for all three.) Your examples have given me several options to test out for different databases and data entry practices.

I get the gist of creating a new calculation field to get rid of the extra spaces. It seems that using the "substitute" option is the best because it automatically corrects the original field for me.

The other options, including adding a "spacecheck field" alert me to a problem that needs attention.

I have different types of databases with different entry patterns and different histories as to how they came into FM8 (some via import from other DBs and some new in FM). In some cases, the extra space or carriage return is inserted at data entry (my carelessness). In that case, the 'substitute" should work fine.

In other cases, I might be modifying data, e.g., initially the field might have an "x" indicating an active record. Then, at a later date when the record becomes inactive, the "x" is deleted.

I've got some experimenting to do - but you've put me on the right track. THANKS.

Susan