6 Replies Latest reply on Mar 21, 2013 1:28 PM by keywords

    Finding with multiple options


      If one of my USERS wanted to perform a FIND for the following information


      People who live in New York City and San Francisco only

      People in those two cities who are African American, Male,

      People in those cities who have a HS diploma or SOME college


      Our DB has a drop down menu listing all of the cities so doing a find on city=New York then expanding the found set to include San Francisco is easy......(2408 records out of a possible 10399)


      Then I constrain the found set asking for African American (another drop down) and Male (another drop down. (761 records)


      This gives me Africian American Males living in New York City and San Francisco.


      How do I then constrain the set to include only HS grads and people with Some College (both in cluded in the Education drop down list)


      Maybe the better question is....Can a user choose two variables from a dropdown list while searching? That would make searching so much easier with out the expand and constrain. What I find myself doing is getting the search to a certain point and then exporting the records out to excell and finishing the search from there...but this totally defeats the purpose of the FMP database....as no one can alter any of those records while outside of FMP.

        • 1. Re: Finding with multiple options

          If you replace your drop-downs with checkbox sets, you may find your life a little easier.



          • 2. Re: Finding with multiple options



            I guess if a checkbox list allows multiple selections, that would work…but as with cities…there are over 400 in that dropdown..getting that to show on a layout would take the entire layout….now if I could get a checkbox list to appear and work like a dropdown list…..:-)


            Dave Scott

            Research Associate

            The Henne Group

            116 New Montgomery Street, Suite 812

            San Francisco, CA  94105

            d    908.444.8610

            t     415.348.2948

            f     415.348.1770

            c    732.331.2590




            • 3. Re: Finding with multiple options

              It really depends on what you want returned when selecting mutiple criteria - those marked with both values or those marked with either value.  If you want to find anyone who is marked with either HS Grad or Some College, you'll want to add a Request to the find.  In the first request you have HS Grad entered, in the second request you have Some College.  This essentially tells FileMaker give me anyone with a value of either HS Grad OR Some College in that field.  To me this is more easily viewed in list view, but adding requests to a find if you are in a form view will simply make the requests look like additional records when you are in browse mode, i.e. you can use the roldex to switch among the requests.


              As Mike indicated a checkbox set will allow the user to view and select mutliple options, but unless you add scripting to split things out, you will only return people who are marked with both HS Grad AND Some College.  As a general rule of thumb, values specified in the same request are treated as AND, in separate requests they are treated as OR.



              • 4. Re: Finding with multiple options



                FIrst of all, yes, you can Shift-click on drop-down items to select multiple values, as well as to toggle selected items off. That said, I don't think that is what you want because, as Craig pointed out, that would return only people (for the education part) who had BOTH HS Grad and Some College. Depending on how your data is controlled, that could mean no found records at all (e.g. if folks were allowed to enter only the HIGHEST level of education, then noone should have both checked).


                For this particular query, you have a couple of options:


                A. After finding for the two cities*, and then narrowing to African American and male, you can go back into Find mode, create two requests (one for HS Grad and one for Some College) and then choose Constrain Found Set.


                B. Go into Find and set up four requests (you could start with one, duplicate it, and then edit as necessary before actually peforming the find):

                a. New York - African American - male - HS Grad

                b. New York - African American - male - Some College

                c. San Francisco - African American - male - HS Grad

                d. San Francisco - African American - male - Some College


                * Not sure how you're finding two cities - first one, then Extend Found Set for the other? That could also be done via two requests in one Find action. I think your key is going to be using multiple requests.




                Debi Rubel

                FullCity Consulting

                • 5. Re: Finding with multiple options
                  Stephen Huston

                  Debi hit the correct note here.


                  If you try to use multiple values in a single find screen, the only matching records will be those which each contain both values. The mutliple search values each need their own request screen to be treated as individual requests, whether or not you are doing a new find or a constrain-found-set.


                  Also, if you use check boxes and select multiple values in a single request (i.e. NY & SF) that request may be treated as a combined text string, so you may get different results from NY & SF than you would get from SF & NY, if any at all. The text gets entered in the order you click the check boxes, not the order in which they appear onscreen.


                  That also means that when using check boxes for data entry, you need to be aware that the order in which you mark the check boxes deteremines the field's actual text content ("NY SF" vs "SF NY").

                  • 6. Re: Finding with multiple options

                    There's plenty of interesting discussion here, but it seems to me you have gotten yourself almost there. You question is:


                    How do I then constrain the set to include only HS grads and people with Some College (both in cluded in the Education drop down list)


                    At this point I think the answer is two separate Find requests which together further constrain the set of 761 you have already isolated.