9 Replies Latest reply on Apr 7, 2015 9:27 AM by philmodjunk

    Perform Find - Combining multiple drop-down lists

      Title

      Perform Find - Combining multiple drop-down lists

      Post

      Hello,

      I would be very grateful for any assistance in designing the following 'search form'. I am sure it is pretty basic for any experimented user, but I am a little lost as to how to proceed.

      I have built a database with three tables (say, Customers, Orders, Payments) connected with one-to-many relationships (one record in Table Customers can be related to many records in Table Orders; and one record in Table Orders can be related to many records in Table Payments).

      I would love to design a "search form" layout made of multiple drop-down lists, in order to "guide" the database user.

      Some drop-down lists would be related to data in Table Customers (eg name, nationality); other to data in Table Orders (eg date order, product, quantity); and others yet to data in table Payments (eg date payment, mode of payment, amount).

      Ideally, the records displayed would be extracted from Table Customers (eg name, nationality) but could also display data from the other two Tables. Clicking on any of the records would take the user to another layout ("individual record display") gathering the relevant data from all 3 tables for that specific Customer.

      Ideally again, any change in one of the drop-down lists would automatically alter the list of records displayed in the search form.

       

      If anyone could guide me if only through the tools I should use, I would be extremely grateful.

      Alexandre

       

       

        • 1. Re: Perform Find - Combining multiple drop-down lists
          philmodjunk

          First a technical note on Performing Finds with criteria specified in fields from related tables:

          If you perform a find on a layout based on the customer table, but with criteria specified in fields from orders and/or payments, you are finding Customer records, not order or payment records. Example: if you perform a find specifying a criteria of > 200 in an amount field in Payments, FileMaker will find all customer records with at least one related payment record with an amount greater than 200. But now that the find is completed, portals to orders or to payments will show all records related to the customers thus found, not just those orders linked to a payment of over 200 or those payment records with an amount more than 200.

          For your search form, you can add global fields formatted with your drop down lists. These can even be conditional value lists if that makes things work better for your user. The user selects values from these lists while in browse mode. A script trigger on the drop down lists or a button added for the purpose can perform a script that uses the values in the global fields to construct a find request and perform a find.

          See this thread for multiple examples of such a scripted find: Scripted Find Examples

           

          • 3. Re: Perform Find - Combining multiple drop-down lists

            Thank you very much for your time, PhilModJunk!

             

            I perfectly understand your first point: a "search form" must be primarily linked with the primary table (Customers), but can then show related data from other tables.

             

            Regarding the use of global fields, I have been trying my very best, but my "gSearch" remains desperately un-editable in Find mode.

            I have created a Global table, in which I have created the global field "gSearch" (in the 'Storage' tab, I have checked "Use global storage"; I have left the max number of repetitions to "1").

            I have then added this field to my "search form" layout (in the Data tab, the field "Field Entry -> Find mode" is checked).

            Do I need to create a relation between Global table and Customers table, and then a portal linked to Global table in my "search form" layout?

             

            I hope I am not using too much of your time. I am asking this because I could not find the answer in the various posts on global fields and tables. 

            Many thanks again,

            Alexandre

            • 4. Re: Perform Find - Combining multiple drop-down lists
              philmodjunk

              Regarding the use of global fields, I have been trying my very best, but my "gSearch" remains desperately un-editable in Find mode.

              Better read my post and the other thread again. You NEVER EVER try to edit global fields while in Find mode. You edit them while in Browse mode. The script then enters find mode, builds the find request(s) and performs the find.

              • 5. Re: Perform Find - Combining multiple drop-down lists

                Many thanks for your advices Phil, and for the links David!

                I have been working hard in order to design a proper "Search Form" layout.

                Happy to say that I have now a Search Form filled with 25 global fields representing 25 search criteria, each of which laid out as a very neat user-friendly checkbox - all this thanks to you!

                I have one last problem. I have understood how to design a search script for 1 search criterion (see picture attached). Yet how should I design the search script for all 25 search criteria (ie the checkboxes), obviously skipping the ones left empty by the user? Should I somehow replicate 25 times the script for 1 search criterion (preceded by "if not isEmpty" conditions), or is there a way to "loop" all this in a more orderly fashion?

                Again, many thanks for your help: I am extremely grateful for your generosity, and very impressed by your capacity to understand and fix such problems.

                Alexandre

                • 6. Re: Perform Find - Combining multiple drop-down lists
                  philmodjunk

                  That depends on whether you want an "And" result or an "Or" result. You can set up a find where the records match each and every criterion specified (and) or you can set it up so that the records match at least one criterion. (Or)

                  Your current script does the latter. 

                  • 7. Re: Perform Find - Combining multiple drop-down lists

                    Within every search criterion, I indeed want an OR result (i.e. search covering all answers checked in one checkbox). This, I suppose, concerns the inner loop.

                    By contrast, there should be an AND result across search criteria (or checkboxes) - that would regard the outer loop, I suppose.

                     

                    To take a "fruity" concrete example:

                    - gfield_colour relates to colour field in Main Table.

                    - gfield_state relates to state field in Main Table.

                    All global fields are located in gTable.

                     

                    In the Search Form:

                    - checkbox I for gfield_colour shows 3 possible answers: red, green or yellow. 

                    - checkbox II for gfield_state shows 3 possible answers: not ripe, ripe or rotten.

                     

                    The search script I wish to design:

                    1. All answers selected in every individual checkbox should give an "Or" search: if user selects both red and yellow in checkbox I, then the list of results includes strawberries and bananas. This, I believe, is what the attached script does.

                    2. But if user selects answers in both checkboxs I and II, there should be an "And" search across checkboxes. Thus, if user selects both red and yellow in checkbox I, and then selects rotten in checkbox II, then the list of results includes rotten strawberries and rotten bananas. I don't know how to write that part of the script.

                     

                    Is my query clearer? If my intuition is not wrong, that would mean an Or search for the inner loop, and an And search for the outer loop.

                     

                    • 8. Re: Perform Find - Combining multiple drop-down lists

                      I am still trying my very best on this, to no avail. I have come to the view that I will need to define one individual loop for each and every search criterion, in order to "feed" the relevant fields in the main table with the relevant global fields in the search form.

                      I can make every loop work individually, based on the picture attached in a previous message: from the example above, when red and yellow are checked in the search form, filemaker displays the correct fruits from the main table (with an OR relationship). Likewise, when ripe and rotten are checked in the search form, filemaker displays the correct fruits from the main table (with an OR relationship).

                      But I am still struggling with searches mixing several search criteria (AND), that is when one (or more) colour AND one (or more) state are checked in the search form: say red and yellow for checkbox I AND then ripe and rotten for checkbox II.

                      I have tried to somehow "link" the loops, among others with the option "modify last search", but nothing seems to work.

                      Any idea?

                      • 9. Re: Perform Find - Combining multiple drop-down lists
                        philmodjunk

                        To set up the "And" criteria, this criteria needs to appear in each and every request (except any "omit" requests) that your script creates. one way to do this is to specify this criteria first and then use duplicate record/request to create each additional new request.

                        Another way is to set up a series of "extend found set" actions for each "Or" criterion, then finish by returning to find mode specifying all your and criteria and doing a "constrain found set".