MartyW Posted February 20, 2020 Share Posted February 20, 2020 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 Link to comment Share on other sites More sharing options...
AHunter3 Posted April 15, 2020 Share Posted April 15, 2020 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? Link to comment Share on other sites More sharing options...
Recommended Posts