1 2 Previous Next 23 Replies Latest reply on Oct 12, 2016 5:29 PM by marksealey

    Searching by selecting multiple values in dropdown

    marksealey

      I have a Layout scripted for a search:

       

      Go to Layout [ “Full search” ]

      New Record/Request

      Enter Find Mode [ ] [ Pause ]

      Perform Find [ ]
      Go to Layout [ “Results” ]

       

      One of the fields in the table/on this Layout is a Dropdown.

       

      How do I allow users to search using multiple values in this Dropdown, please - both 'Or' and 'And'?

       

      Thanks!

       

        • 1. Re: Searching by selecting multiple values in dropdown
          user19752

          Why do you add a record before entering find mode?

          • 2. Re: Searching by selecting multiple values in dropdown
            philmodjunk

            To use "and", find records with value 1 and value 2 and value 3, you need not change your example script.

             

            But to find records that have value 1 or value 2 or value 3, you need a looping script that puts each value into a different find request.

             

            One option is to collect your search criteria in global fields while in browse mode, then use a script that builds the find requests in find mode in order to find your records.

            Let's keep the example simple. This is an "OR" find with two global fields. One field specifies criteria that all records must match and the other is the field with multiple values selected. The criteria for both fields are entered/selected in global fields before this script is run:

             

            Enter Find Mode [ ] ---> no pause, no stored criteria

            Set Field [YourTable::Field1 ; YourTable::gSingleValueField ]

            Set Field [YourTable::Field2 ; GetValue ( YourTable::gMultiValueField ; 1 ) ]

            Loop

               Set Variable [$K ; value: $K + 1 ]

               Exit Loop If [$K > ValueCount ( YourTable::gMultiValueField ) ]

               Duplicate Record/Request

               Set Field [ YourTable::Field2 ; GetValue ( YourTable::gMultiValueField ; $K + 1 ) ]

            End Loop

            Perform Find [ ]

            • 3. Re: Searching by selecting multiple values in dropdown
              marksealey

              user19752 wrote:

               

              Why do you add a record before entering find mode?

              Don't I need to start a new Request?

              • 4. Re: Searching by selecting multiple values in dropdown
                philmodjunk

                If you are not in find mode, you are creating a record, not a request. New record/Request is one of several script steps that does different things depending on the current window mode (find, preview or browse).

                • 5. Re: Searching by selecting multiple values in dropdown
                  marksealey

                  philmfdjunk,

                   

                  Thanks! The script puts me into Find Mode in that Layout. Maybe user19752's comment was a question for information and not a (very welcome!) suggestion?

                   

                  Are lines 2 and 3 in the wrong order; and should they perhaps be:

                   

                  Enter Find Mode [ ] [ Pause ]

                  New Record/Request

                   

                  Taking this one step at a time, in the case of AND queries, I cannot get multiple selections of the dropdown list items to work.

                   

                  When I select value 1 --> then CTRL/Cmnd + value 2 --> CTRL/Cmnd + value 3, only Value 1 appears in the field with the dropdown.

                   

                  So shouldn't I get that working before tackling OR, please? If so, which is the best way, please?

                  • 6. Re: Searching by selecting multiple values in dropdown
                    philmodjunk

                    That is the point that he was making. You have those two steps in the wrong order. If your script does a new Record/Request before entering find mode, then presumably, you are in browse mode and this step creates a new blank record and that is not a good thing.

                    When I select value 1 --> then CTRL/Cmnd + value 2 --> CTRL/Cmnd + value 3, only Value 1 appears in the field with the dropdown.

                    That should select multiple values--though a check box format would eliminate the need for using a modifier key. Is it possible that you actually have all the values selected, but can only see one when you exit the drop down?--this would be normal behavior for a drop down and you should see all values selected if you pull it down again and check.

                    • 7. Re: Searching by selecting multiple values in dropdown
                      marksealey

                      My script is now this:

                       

                      Go to Layout [ “Full search” ]

                      Enter Find Mode [ ] [ Pause ]

                      New Record/Request

                      Perform Find [ ]
                      Go to Layout [ “Results” ]

                       

                      But… in the DropDown field (Text) when I select the first value, Yes, it gets highlighted.

                       

                      Cmnd + the second value results in two values highlighted. There are 33 in total.

                       

                      The Dropdown list items all remain visible with two selected.

                       

                      Clicking away to the Submit button, though, results in the display of a found set which includes records containing only one of the search terms.

                       

                      Do I need to do something more to/with the Perform Find script step, perhaps, etc, please?

                      • 8. Re: Searching by selecting multiple values in dropdown
                        philmodjunk

                        What "submit" button? Do you mean the "perform find" button in the status tool bar or a layout button?

                         

                        IF this is a layout button, you'll need to explain what it does.

                         

                        More about your script.

                         

                        Entering find mode automatically creates a new find request. There's no need to create additional requests unless you are setting up an "OR" type request since FileMaker will find the records specifiied in one request OR the other.

                         

                        An AND request would find the records that have value 1, value 2 and value 3 all specified in the same field of the same record. Is that what you want here?

                        • 9. Re: Searching by selecting multiple values in dropdown
                          David Moyer

                          Hi,

                          If you can get the results you want by using the FM interface, you should be able to recreate it in a script.  One way to approach this is to face your layout and write down each keyboard shortcut, keystroke and mouse click that you use to get the found set you want.

                          • 10. Re: Searching by selecting multiple values in dropdown
                            marksealey

                            Thanks, Yes - everything is scripted. Nothing in the Status tool bar.

                             

                            I'll explain how I have it scripted now. maybe it's wrong?

                             

                            I have a first button which runs the (now amended) script pasted above/here; it takes the user to the Find Layout in question with those script steps.

                             

                            The Submit button I refer to performs a Single Step: 'Resume Script', which I intend(ed) to submit the Find request.

                             

                            I have this one field - a text field - with the 33 Values in the dropdown. I want the user to be able to select one OR more of these values from that dropdown and the resulting Found set to contain all records containing one OR more of those values.

                             

                            So the way I have it now is:

                             

                            1. a button takes the user to the Find Layout (and - per the script quoted) enters Find mode
                            2. the user must be able to select multiple values from the dropdown; ORed
                            3. they click Submit to perform the End by Resuming the Script that was paused in step 2

                             

                            Thanks again for your help!

                            • 11. Re: Searching by selecting multiple values in dropdown
                              philmodjunk

                              See my looping script example posted yesterday. You'll need one request for each value selected from the value list.

                              • 12. Re: Searching by selecting multiple values in dropdown
                                marksealey

                                Thanks,

                                 

                                Yes - I did try it. Nothing happened :-(

                                 

                                Why two variables, please? Not sure what they each do. And I couldn't enter the first variable line inside the loop that way in FMP 14.

                                 

                                And is my basic flow/structure - including the Submit button correct?

                                 

                                I suspect I need some more help, please :-)

                                • 13. Re: Searching by selecting multiple values in dropdown
                                  philmodjunk

                                  There is only one variable, $K used in my script.

                                  If the only criteria used is that specified in your drop down list, omit the  Set Field [YourTable::Field1... step and you can use New Record/request in place of Duplicate Record/Request. What that shows in my example is how to combine "and" and "or" criteria.

                                   

                                  Another note. The set field steps will only work if you have previously paused the script and the user has specified criteria in those fields. An alternative that I usually use for this type of thing is to set up global fields for the user to enter search criteria while they are in browse mode. They then click a button to run a script and the script transfers data from the global fields while in Find mode to set up the needed find requests.

                                   

                                  For more scripted find examples of this type, see this thread:

                                  Scripted Find Examples

                                  • 14. Re: Searching by selecting multiple values in dropdown
                                    marksealey

                                    Thanks!

                                     

                                    And thanks for making that script more comprehensive.

                                     

                                    Is the structure of my two buttons - one to get to the Find Layout and the other to Submit the find itself from that Find Layout - there correct one, please? if that's not the (best) way to do it, I need to correct that before I go further!

                                     

                                    Can that transfer button be used to change modes on the same Layout, please?

                                    1 2 Previous Next