11 Replies Latest reply on Sep 8, 2009 12:02 PM by philmodjunk

    Record Search



      Record Search


      What is the best way to allow a user to run a search query from a drop down menu?




      Project Layout:

      Basic Project information fields (Project Name, Date, Location, Producer, Editor, Talent, Status and so on)

      Search Button: turns all fields on the layout into a searchable field.

      Project Load Drop Down Menu

      Load Button


      This Does not work yet.......                       

      I would like the drop down menu box to be loaded with all of the project name records from the project table.

      When the user selects the project he wants to work with, they would click on the load project button, then the layout would be refreshed with all of the fields from that particular project.


      How do I make this work?


      Thanks for the help ahead of time!


        • 1. Re: Record Search

          There are multiple approaches and the "best" approach may depend on the design of your database.


          Here's a fairly simple approach that may get you started:


          Define a global text field and place it on your layout, set it up with your value list as the type of menu you want.


          Create a script: (You'll need to change some details to make it work on your Db)


          Enter Find mode []

          Set Field [Yourtable::YourProjectNameField ; Yourtable::GlobalTextField]

          Set Error Capture [on]

          Perform FInd []

          Set Error Capture [off]


          This assumes your data is in "Yourtable" and that your current layout is based on this table.

          You can attach this script to a button, or, if you are using FMP 10, you can set a script trigger on the global field to perform the script when it is exited and/or modified.


          Edit note: changed Set Autocapture to Set Error Capture in above script

          • 2. Re: Record Search

            Thank you! I understand most everything you have instructed me to do except for I can not find the "Set Autocapture" when I am setting up the script. I am using the latest version of FM on a mac.



            • 3. Re: Record Search
                 My mistake, it's Set Error Capture.
              • 4. Re: Record Search
                   Phil, I guess I am still missing something. I have setup the script exactly as you said. It does not appear to do anything. How should I troubleshoot this?
                • 5. Re: Record Search

                  Test it manually.


                  Select a value in your global field.

                  Enter find mode.

                  copy the text in the global field and paste it into the matching name field.

                  perform the find.


                  I suspect that at some point you won't be able to do this or you'll get a "no records match" error message when you do the find.

                  • 6. Re: Record Search

                    I ended up getting to to work with this script


                    Go to Object


                    Enter Find Mode

                    Go to Object


                    Perform Find


                    I am not sure why I was unable to get yours to work but thanks for the help! it got me started!!

                    • 7. Re: Record Search

                      You haven't defined the menu field as a global field. Use Manage | Database | Fields to find the field definition, double click it to pop up the options dialog and select Global storage on the storage tab. The value in this field won't be available in find mode until you have this set up this way.


                      On a side note, it's not a good idea to use copy and paste in this fashion. If you must move data from one field/record to another, use the set field and set variable script steps.


                      Copy and Paste only work if the referenced field is present on the current layout. If you update your layout to remove the referenced field at some point, the script will then fail to work.


                      When copy and paste fail, filemaker does not tell you that they didn't work. The script just continues on as though that step isn't present in your script. That "Silent Failure" can produce confusing script results and possibly mess up your data.


                      Copy's use of the clipboard means that anything the user has copied to clipboard has now been replaced by the value copied by your script.


                      Set Field and Set Variable have none of these issues.

                      • 8. Re: Record Search

                        Howdy ChrisB,


                        Phil didn't mention it, likely 'cause he wasn't proposing copy&paste, but many of us shy away from the copy&paste functions very intentionally.  They put info on the clipboard that you may not want there.  Will it cause a problem?  Maybe yes, maybe no.  But why take the risk if it's avoidable.


                        Phil was talking about SetField to avoid this.


                        If you're willing to, I would counsel you to look further into what is hanging up with the script Phil wrote and get the method working that does NOT use copy and paste but uses Setfield instead.



                        • 9. Re: Record Search

                          That would be great, I see why you stay away from copy and paste but I dont really know a whole lot about filemaker so I tend to go with what I can get to work. I am changing the script back over to what Phil said to do. I have also verified that the field is set to global. Ill let you know what I find this time. Thank you so much for all the help!!

                          • 10. Re: Record Search



                            Well I am not really sure how it works now but it does. Thanks for all the help!!

                            • 11. Re: Record Search

                              A bit of explanation may help you adapt this technique for other uses.


                              The global field is a simple way to hold data from the user in a field that will be accessible when the database is in find mode. Watch what happens when you take a layout with both global and non global fields and click find in the task bar.


                              The non global fields will become blank so that you can enter find criteria. The global fields will remain unchanged as you can't enter find criteria into them anyway.


                              Thus in find mode,


                              Set field [table::localfield ; table::globalfield]


                              will copy the contents of the global field into the search field.




                              Set field [table::localfield 1 ; table::local field 2]


                              will not as both local fields would be blank.