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?
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)
If ($Name ≠ "")
Set Field (Name; $Name)
If (GetFoundCount = 0)
Show All Records
Show Custom Dialog (telling them there are no results)
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.
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.
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.
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"}
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.
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?
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.
I spotted my mistake! I'm using FMP10 atm.
Many thanks for your solutions. It works perfectly fine now.
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?
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.