Search Script Checkbox Field for Multiple Values

[MartyW]

I am trying to create a search of my database using a Check Box in the search to retrieve multiple criteria. It is a people database with first name, last name, gender and hair color. I can search the database on the first name, last name and if they are male or female fine. I also can search the database if the search hair color field is a radio button, where I can only select 1 choice. It works fine in that instance. However, I want to be able to search for people that may have different hair color and they both be returned in the search.
 
I have the following tables/fields:
 
Table 1: Person
Fields: 1. Id 2. First Name 3. Last Name 4. global Search field for Last Name 5. global Search field for First Name 6. global Search field for Gender 7. global Search field for Hair Color
 
Table 2: Gender
Fields: 1. Id 2. PersonIdFK 3. Gender
 
Table 3: Hair Color
Fields 1. Id 2. PersonIdFK 3. Hair Color
 
I have attached one example database with the radio buttons searching only 1 hair color that works and one example database with check boxes that I want to be able to search mutiple hair colors that is not working correctly.
 
If I click on multiple hair colors, I want it to return all those hair colors if all other criteria is matched. It is currently returning no values if more that 1 item is selected.
 
It seems when searching using the checkbox that is searching using an "and" statement instead of an "or" statement. Not sure where I can change that.
 
There is a script for both the Search and Clear buttons.

 

 

Search with Radio Buttons for Haircolor - works.fmp12 Search with Checkbox Set for Haircolor - not working correctly.fmp12

[AHunter3]

Umm yeah.

As you've got it written, if someone checks off "blond" and "brown", you script is going to search for people who are brown-headed blondes.  Of course you get 0 found.

 

You need to loop through the values in your checkbox set and search for each checked value as a separate Find request, so that you are searching for blond people and also searching for brown-haired folks.

 

Hence:

 

Enter Find Mode [Pause: Off]

Set Field [Person::FirstName; "•" & Person::g_SearchField_FirstName & "*"]

Set Field [Person::LastName; "•" & Person::g_SearchField_LastName & "•"]

Set Field [Person::Gender; Person::g_SearchField_Gender]

Set Field [HairColor::HairColor; GetValue (Person::g_SearchField_HairColor; 1)]

Set Variable [$Pos; 2]

Loop

Exit Loop If [ GetAsNumber ($Pos) > ValueCount (Person::g_SearchField_HairColor)]

Duplicate Record/Request

Set Field [HairColor::HairColor; GetValue (Person::g_SearchField_HairColor; $Pos)]

Set Variable [$Pos; $Pos + 1]

End Loop

Perform Find []

Sort Records [Restore; with dialog: Off]

 

 

Do you see how that makes a separate request out of each checked checkbox value?