search for semi-empty fields

[stefangs]

i have a file where i need to search for empty fields regularly. i'm using a find script that puts an equal sign in the field and then displayes the records - easy enough.

there are some records i want to exclude from that find, because they're supposed to be empty, so i figured i just put a space in these fields, so they're technically no longer empty, but the find script still returns them as being empty. can i trick FM to make a space count as data? the smallest character i could find (a period) still counts as empty by that script. any normal ascii character works as expected, but then the field doesn't really look empty any longer.

[kennedy]

Add an extra field 'foo blank' that is set if its intended to be blank.

Then do your search with an = in both fields.

[CobaltSky]

Hi stefan,

If it's important that it look empty, but not necessarily be empty, another 'trick' you might consider would be to have a piece of text in a (hidden) global text field on your layout, which is custom-formatted white (or whatever colour your field background is) then for fields which are intended to appear empty but not be, have a script which will copy and paste the white text character from your global.

The coloured text will keep its formatting (provided you don't select the 'paste without formatting' option) and will not be visible to the human eye - but will of course still be 'visible' to the 'find' process.

[Vaughan]

omit " " -- a space between quotes.

[stefangs]

actually, that's an excellent workaround which works fine in my situation. thanks!

i still don't understand why a space in a field is ignored however. for example, if i have two text fields , called a and b, along with a calc field like this

If (a = b, "same", "different")

and then i enter a space in one field, but nothing in the other, they show up as 'different'. so sometimes the space matters, other times it doesn't?

[BobWeaver]

The single equal sign is somewhat misnamed as an exact match symbol when it's really a whole word match symbol. So:

If you use Fred for your search, it will find "Fred" and "Frederick" and "Fred Smith".

If you use =Fred for your search, it will find "Fred" and "Fred Smith" but not "Frederick".

If you use ==Fred for your search, it will find "Fred" but not "Frederick" or "Fred Smith".

Since the space is not considered a word, using an equal sign in front of it won't make any difference.

[Vaughan]

If (a = b, "same", "different")

That's an indexing issue I believe. The correct way to compare text strings is

If (Exact(a, :, "same", "different")

The Exact function returns 1 (true) of the strings match, 0 (false) if they don't. Exact is case-sensitive too. If you want a non-case-sensitive string comparison, use

Exact(Lower(a), Lower(:)

[stefangs]

thanks! that's an excellent explanation! so the equal sign is real data, but not to the find function. live and learn.