9 Replies Latest reply on Jun 26, 2017 9:28 AM by philmodjunk

    Script to perform search from a drop down list



      Script to perform search from a drop down list


           Dear Forum

           This is a follow up from my post “How to "find matching records" from a portal row, without having to use Quick Menu”. See:


           PhilModJunk provided me with a script to achieve what I had in mind. The script was needed to overcome a bug identified by PhilModJunk – see:

      Find Matching Records Script Step Fails when field is in a portal

           PhilModJunk’s script is as follows:

           Set Variable [$ID ; value: casenote_keyword::keyword_id ]

           Enter Find mode []

           Set Field [ casenote_keyword::keyword_id ; $ID ]

           Perform Find []

           What I want to do now is have a new layout (“search”), where the user can select a keyword (in find mode) from a dropdown list of the keywords, then press a button that performs a script as follows:

                  1. Go to the “casenote” layout

                  2. perform PhilModJunk’s script as if the set variable was the keyword_id selected by the User from the dropdown list.

           I attach a hyperlink to a file that incorporates the script provided by PhilModJunk, but now with a draft “search” layout. See:


           I am only at the beginning of the learning curve on scripting, and would appreciate any help on what the script should be for the button on the “search” layout.


           Scott Nalder

        • 1. Re: Script to perform search from a drop down list

               If your drop down list is set up on a field with global storage specified, you can modify the above script like this:

               Set Error Capture [on]
               Set Variable [$ID ; value: casenote_keyword::keyword_id ]

               Enter Find mode []

               Set Field [ casenote_keyword::keyword_id ; YourGlobalFieldWIthDropdownHere ]

               Perform Find []


          • 2. Re: Script to perform search from a drop down list

                 Dear PhilModJunk

                 The script works great. Thank you.

                 I have an associated issue. I attach a screenshot of the best I can do with the drop down list.

                 What I need is for the dropdown list to show only the keyword_name, not the keyword_id and the keyword_name. Also I need for the field, when the selection is made, to show only the keyword_name. The user can then have confidence that when he/she clicks the search button it is searching, from the user's perspective, on the keyword_name, and not the keyword_id. As a further complication, I need the keyword_names to appear in the dropdown list in order of their associated keyword_id (ie alphabetical is not sufficient for my needs as I am listing the keywords according to legal concepts, not easily translatable into alphanumeric listing)

                 However, I am having trouble using value lists to get this result. Is there a way of:

                 (a) having the dropdown list show only the keyword_names, in order of the keyword_ids,

                 (b) but still have the global field (in which the dropdown list resides), populated by the keyword_id so it can be used in the script.

                 I also attach a link to the latest version of the file incorporating your suggestions to date, and which has my primitive version of the drop down list which works, but would be unacceptable to a casual user.


                 Thank you for your assistance.


                 Scott Nalder

            • 3. Re: Script to perform search from a drop down list

                   a) This will either be impossible or difficult to do. You can specify that the ID field be hidden, but then the value list will sort on the name field into strict alphabetical order. Getting a field based value list to sort into a different order is difficult--especially for lists with large numbers of values.

                   b) For display purposes, you can hide the ID number via one of two methods: 1) use a pop up menu instead of a drop down list. 2) Hide the drop down list formatted field behind the selected keyword field (use an opaque fill color) from the related table and use behavior settings to prevent access to this field when in browse mode. When the user clicks on the visible keyword field, the drop downlist pops up and you can select a value. When you exit the field, it disappears back behind the keyword field and that field now displays the selected keyword.

                   To get better control of your value list, I can suggest several options:

                   1) use a conditional value list where the user first selects a category in a category field and then your value list only displays values that are a member of that category. This can produce a much shorter list to scroll through in order to make a selection. If you are interested in this option and are unfamiliar with how to set them up, let me know and I'll post a list of links about them.

                   2) Use a search portal instead of a value list. The portal can then be sorted exactly how you want and the ID numbers need not be visible in the portal. The fields in the portal row are set up as a button to perform a script that sets the field to the ID of the clicked portal record.

                   See this file for examples: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

                   With FileMaker 12, open this file from the File menu to get a converted demo copy of the file.

                   3) We can go full circle and use a value list strictly of text that auto-completes to save time and typing. A script uses the selected text to look up matching records and enters the ID. This method can also handle duplicate names but probably can't be used to sort values like you want, but it could be set up as a conditional value list.

                   FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                   Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7


              • 4. Re: Script to perform search from a drop down list

                     Dear Phil

                     Thank you for your help. I now need help in writing a script that will return the records that have the fields as inputted by the user from up to three dropdown lists.

                     Previously I had a search page with one drop down list. The user selects a keyword from the drop down list, and then presses the adjacent button to run your script:

                     Go to Layout [“casenote’ (casenote)]

                     Set Error Capture [On]

                     Set Variable [$ID ; value: casenote_keyword::keyword_id ]

                     Enter Find mode []

                     Set Field [ casenote_keyword::keyword_id ; $ID ]

                     Perform Find []

                     My challenge is now to have a search page that enables the user to select from up to three fields – “keyword”, “legislation” and “house”, with the default selection in each drop down list being blank (but blank =  all records for the purposes of the combined search button).

                     How do I write a script that I can attach to a button that returns the set of records that have all three fields as selected by the user (again, with all records being the default selection for each dropdown list).

                     The complication I face here is that each field has a many to many relationship with the record set, and (I think) I will have trouble using your script as it relies on only one selected input / variable ($ID??).

                     I attach an abridged file to show you what I am getting at:


                     In the file on the “search” layout, I have a button labelled “COMBINED SEARCH” on the “search” layout, but with no active script attached.

                     Also, how do I go about writing a script to clear the values in the drop down boxes for a new search? As always, any help is greatly appreciated.


                • 5. Re: Script to perform search from a drop down list

                       I don't have time to download and examine your file, but this sounds like something that can be done simply by adding two more set field steps so that you have one for each drop down.

                  • 6. Re: Script to perform search from a drop down list



                    I have a drop-down list, a global field.

                    How do I do a smart search in this drop down?

                    I want the user can either select the techncian in the drop-down list or do a smart search in it.



                    • 7. Re: Script to perform search from a drop down list

                      What is a "smart" search?

                      • 8. Re: Script to perform search from a drop down list

                        Like this: https://www.youtube.com/watch?v=bYZUwW93prs


                        My scenario is different; I don't use pop-over button.

                        I have a global field that's a drop-down list of technicians.

                        Instead click the scroll bar to select the tech, I want able to search the technician too.

                        • 9. Re: Script to perform search from a drop down list

                          I don't have time at this moment to watch a video.


                          There are several ways to find records in FileMaker.


                          Enter Find mode, set criteria and perform a find. This is the method described in this thread and should do exactly what you want.


                          Performing a quick find--which applies specified criteria to multiple fields in each record. I generally find that if I have to use this method, I have design issues with my data model.


                          Go To Related Records--If you have a relationship that matches to the right records, you can often select a value or values for a match field and use this method to pop up a set of found records. ExecuteSQL can also be used to generated a list of ID's for use with this method. (And a portal to the related records is really using this same technique, but without getting an actual found set.)


                          Loop through--usually an inefficient approach, but can be done and once in a while, it has its uses.