10 Replies Latest reply on Feb 9, 2010 3:33 PM by philmodjunk

    Create a search form



      Create a search form


      Hi, I am trying to create this musician database and search form, which allows me to choose the Year and Musician Name from 2 drop down menus and returns results of the albums that the musician has released. I think I need a script to do this but I have no idea how to.


      I started by creating a new layout with 2 drop down menus - Year and Musician Name. After selecting and click on a "Go" button, I want results to be returned in a new window. If there is no result, I would like to have a pop-up window that says "No album released by musician in this year".


      Please help

        • 1. Re: Create a search form

          Do you know how to search your database "by hand"? In other words, can you enter find mode, enter a musician's name and then press enter or click find to locate the records you want? Understanding how to do this can be the first step of what you are trying to do.


          It'll also help if you can describe the design of your database in more detail. What tables/field/layouts/relationships have you created?

          • 2. Re: Create a search form

            Hi, Guava,


            In the mean time, I'll give you a general answer.  The first script you'll need will be the one that brings your user from the main layout to the search layout.  That's a simple:


            Enter Browse Mode

            GoToLayout (Search Layout)

            Set Field (gYear; "")

            Set Field (gMusicianName; "") 


            The last two steps ensure that the search fields start out empty until the user selects a year or a name.  The "Go" button will perform this script:


            Enter Browse Mode

            Set Variable ($Year; gYear)

            Set Variable ($Name; gMusicianName)

            Enter Find Mode

            If ($Year ≠ "")

                Set Field (Year; $Year)

            End If

            If ($Name ≠ "") 

                Set Field (Name; $Name)

            End If

            Perform Find

            If (GetFoundCount = 0)

               Show All Records

               GoToLayout (WhereverYouWantThemToLand)

               Show Custom Dialog (telling them there are no results)


               GoToLayout (WhereverYouWantThemToLand)

            End If


            You can also toss in a check to see if they left both search fields blank, yelling at them if they do.


            You can also add two different year fields (first year of a range to last year of a range), which would require you to search on the date field for:


            $StartYear & "..." & $EndYear


            Hope that gives you a place to start. 

            • 3. Re: Create a search form

              I have 3 tables. And one layout attached to each of the tables. These 3 tables are musician, record company and production.


              With these 3 tables, I have created a 4th layout/report - Musician Production Report. In this report, I have the following fields MusicianName (musician), CompanyName (record company), Year (production), Album (production).


              I have since created another table (and layout) titled "Search Form". In this table, I have 2 fields, searchViaMusician and searchViaYear. 


              User will enter musician's name into searchViaMusician field and click on a "Go" button to trigger a search script that looks thru the Musician Production Report for related results.


              If there is any related record, it will be displayed in Musician Production Report (with the rest of the musicians' details omitted)


              If there is no result, it will prompt the user via a message window and user will remain on the Search form.


              I'm not too sure how I should write the script for this. Please help.

              • 4. Re: Create a search form
                   Thank you for the solution jsalzer. I'm not sure how I should apply it to my database though. Could you please read the reply that I have provided and guide me from there. Thank you.
                • 5. Re: Create a search form

                  Define your search fields to be global fields.


                  THen you script could look like this:



                  Go To Layout [Musician Production Report]

                  Enter Find Mode[] (deselect the pause option)

                  Set Field [Musician::MusicianName,SearchViaMusician]

                  Set Error Capture [on]

                  Perform Find []

                  If [get(foundcount) = 0]

                     Show custom Dialog ["No musician found"}

                  End IF

                  Set Error Capture [off]


                  Your Search by Year script would be the same except the set field instruction would be:

                  Set Field [Production::Year,SearchViaYear]


                  The set error capture line temprorarily turns off FMP's error trapping so that the user doesn't see the generic "no records found option" so the script can respond with a custom dialog message instead.


                  Make sense?

                  • 6. Re: Create a search form

                    Thanks PMJ. I am not sure what I have done wrong. But the script is not returning what I would like to sing. But example, let's just say I select Beyonce in the drop down list on the Search Engine layout. After I clicked on a Go button, it returns Michael Buble.


                    What I want to see in the result is just all the details of Beyonce and no one else. 


                    How do I make this work?

                    • 7. Re: Create a search form

                      All it takes is for one little detail to be incorrect for a script to fail. Show me exactly what your Set Field step looks like in your script.


                      Also, what version of Filemaker are your using? If you are using FMP advanced, you can use the script debugger to watch your script execute step by step and this can be very helpful in determining why your script didn't do what you expected.

                      • 8. Re: Create a search form

                        Hi Phil,


                        I spotted my mistake! I'm using FMP10 atm. 


                        Many thanks for your solutions. It works perfectly fine now.

                        • 9. Re: Create a search form

                          I have a separate list of retail items in a "Word" document that I can switch into an "Excel" file if I need to. Can my database tell me if I have an exact item that the customer wants that is listed in this other file?



                          • 10. Re: Create a search form

                            The simplest approach to describe is to import your data into a table in filemaker so that you can search it. Filemaker can import from many file formats including text and excel files.