Finding compound phrases in checkbox value lists. (I.e. "Dog", "Dog House," "Dog Sitter" etc.)

[Gilbert Osmond]

 

This seems like a basic problem but after several hours of googling & reading these forums I can't figure it out.

I have a regular text field (indexing turned on) set to display as Checkboxes. The value list for these checkboxes is:

Dog
Dog house
Dog owner
Cat
Cat owner
Automobile

 

As you can see, some words repeat in multiple values, e.g. "Dog" and "Cat".

How do I do a Find that will show me: ( "Dog house" OR "Dog owner" ) OR  ("Dog house" AND "Dog owner") but NOT "Dog"  ?

Due to the way Filemaker searches value lists, no matter what I try (different combinations of =, ==, ="" operators, using multi-line find criteria in single find request, different permutations of multiple Find requests), I cannot seem to get the simple result I specify above. I always get records with "Dog" included in the search results, and I can't Omit them using a ==Dog operator because that only applies to records that have ONLY "Dog" in the text field. Using ="Dog",  ="Dog " (with a space), etc. etc. also does not work.

 

Edited December 3, 2015 by Gilbert Osmond

[comment]

25 minutes ago, Gilbert Osmond said:

How do I do a Find that will show me: ( "Dog house" OR "Dog owner" ) OR  ("Dog house" AND "Dog owner") but NOT "Dog"  ?

To which part does the "but NOT "Dog"" apply? I believe "Dog house" OR "Dog owner" already includes ("Dog house" AND "Dog owner").

[Gilbert Osmond]

Let me see if I can clarify:

( "Dog house" OR "Dog owner" ) OR  ("Dog house" AND "Dog owner") but NOT "Dog" 

Means, at the end of my single Find ( or chain of multiple Find requests, or scripted solution, whatever is necessary),

My found set of records should include:

- Reords that have "Dog House" OR "Dog Owner" checked off in this text field.

- Records that have both "Dog House" AND "Dog Owner" checked off in this text field.

The same found set shoud NOT include:

- Records that have "Dog" checked off, regardless of other checkboxes that may be checked.

[comment]

Well, as I said, once you have found records that have "Dog House" OR "Dog Owner" checked off, you have also found all records that have both "Dog House" AND "Dog Owner" checked off - so the second condition is redundant.

Anyway, I don't think it's possible to achieve your goal through searching that field. Some alternatives you could consider;

1. Find records that have "Dog House" OR "Dog Owner", then loop among them to omit those that also have "Dog";
2. Use a relationship and find the records through GTRR;
3. Use a calculation field where checked values will have a terminating character;
4. Abandon the checkbox field and use individual related records instead.

Edited December 2, 2015 by comment

[Gilbert Osmond]

OK, I see your point about my "AND" condition being redundant.

That seems incredible to me, that with all of FM's find options, operators, built-in and possibly custom functions, scripts & variables, etc. -- that it is not possible to construct some method of searching a single value-list text field with the criteria:

("Dog House" OR "Dog Owner" ) but NOT ("Dog").

-- and without resorting to hocus-pocus terminating or invisible /special characters.

I'll experiment with your suggestions #1 and #2.   #3 is not an option, this is a legacy solution.

Edited December 2, 2015 by Gilbert Osmond

[comment]

Perhaps you should make a suggestion to add a "match whole value" operator to the find operators; say ===dog. That could be useful.

 

12 minutes ago, Gilbert Osmond said:

I'll experiment with your suggestions #1 and #2. 

Note the edit I have made.

 

 

Edited December 2, 2015 by comment

[Gilbert Osmond]

Witnessing Filemaker Inc's user-opaque development process over the past decade does not incline me towards making any suggestions about adding an operator.

But your suggestion to simply loop through the found set and Omit records meeting my criteria works. Noting particularly the addition of a carriage return (option-7) character in the FilterValues.

(With apologies for formatting, I don't see how to do clean formatting of code in this forum?)

 

#
Go to Record/Request/Page [First]
  Loop
    If [FilterValues  (Contacts::Category;  "Dog¶" ) = "Dog¶"
    Omit Record
    End If
    Go to Record/Request/Page [Next; Exit after last]
  End Loop

 

 

Edited December 2, 2015 by Gilbert Osmond

[comment]

I would suggest:

not IsEmpty ( FilterValues  ( "Dog" ; Contacts::Category ) )

 

3 minutes ago, Gilbert Osmond said:

I don't see how to do clean formatting of code in this forum?)

Use the <> code button.

[Gilbert Osmond]

As it turns out, to save time and cost I am just going to update the value list and append a bullet • (option-8) character to the problem words. Then do a 1-time search & replace to update the stored values in each record.  That neatly skirts around the problem completely. Updated value list will look like:

Dog•
Dog house
Dog owner
Cat•
Cat owner
Automobile