How much do you know about how relational databases work? How much time do you care to invest in learning?
Filemaker can certainly do what you describe, but you may find that you'll need to spend some time learning some basic database theory and how to properly structure your database.
You'll need a least 3 different database tables for what you describe:
Songs::SongID = Band_Songs::SongID
Bands::BandID = Band_Songs::BandID
Everytime you assign a band to a song, you create a record in Band_Songs to link them and this can be easily done with portals.
You might be able to get away with two tables and a checkbox formatted field that lists all your bands as checkboxes, but you may also lose quite a bit of flexibility in your final design (People often find themselves adding more and more stuff to their database system once they learn how much they can do with it.)
You can perform finds to find all songs on a given band's play list and this can be automated with a script so that selecting a band's name triggers the script that pulls up all the songs that are performed by them.
The more I think about this, the more I convince myself that I am hitting you with a serious case of overkill.
If you stick to the limits of your original description,
a single table of songs with a checkbox field where you list all your bands would work well. You can easily fill out such a table and then simply enter find mode, click a check box and find all songs for that band. The catch is that this won't work if you have two bands with very similar names such as "Crazy" and "Crazy eights". In such cases, clicking "crazy" will also find songs performed by "crazy eights".
Thanks for your prompt reply, including the second thought!
I know just a little about (relational) databases. Though as a Windows/OSX administrator I am very willing to invest time in learning this, since my Songbook database is some sort of testcase to see if I can handle a CRM-like assignment for a client of mine.
The names of the bands are different enough not to get caught in the examples issue you mentioned. The find mode should work like you pictured. With this solution in mind, would I still be able to get my column with all songs for that band from which I can select a song to be viewed in detail on the right side?
You can set up a portal to your songs filtered (you have FileMaker 11?) by band. Then clicking a row in the portal can trigger a script that finds the song records that have that band's name selected in the check box field.
- In Manage | Database | Relationship, Click the box for your songs title, then click the button with two green plus signs to make a second box. You can double-click that new table occurrence box to change it's name. I'll call it AllSongs.
- Drag from a field in Songs to a field in AllSongs. Doesn't matter which field.
- Double click the line you've created linking these two and use the edit relationship dialog that appears to change the relationship operator from = to X. This way, any song record in songs will match all song records in All songs.
- Also define a text field, selectedBand which we can format with the same value list as your check boxes.
- Put a portal to AllSongs on your Songs layout and specify a portal filter as:
- not isEmpty ( FilterValues ( AllSongs::BandCheckbox ; SelectedBand) )
- Format SelectedBand as a drop down list.
- write a one line script: Refresh Window [Flush cached join results]
- Set up SelectedBand with an OnObjectSave script trigger to perform the above script.
- Now when you select a band in the drop down, the script will refresh the portal filter to show only the songs that are assigned to that band.
See if you can get that far and then we'll take a crack at the script to find a song when you click it in the list.
I'm afraid I'm not quite following you. I'll have to dig in to the FMP11 manual for some more info on the basic(?) stuff like portals etc.
Just to verify if I'm on the same page as you, I'll explain my current situation:
I've got currently one table 'Chords_db' with the fields 'Bands' (with 6 checkboxes), 'Artist', 'Song', 'Chords', 'Created date', 'Modified date'.
I've completed step 1 and 2 and please find the result below:
To me it seems that the left 'Songs' table is not quite alright with the 'Date Create' in the top?
The SelectedBand field is added as you can see. Since I'm not sure about the situation above I hesitate to continue. Is this done right, or should I make some changes first?
Step one is complete. The X operator is used to match all records in one table occurrence with all records in another. Since we are matching all records, it doesn't matter which fields we select on each side.
Portals are documented fairly well in FileMaker help and there's a tool in the status area at the top of your screen when you are in layout mode that you can use to add a portal to your layout. You just click this tool, draw a rectangle on your layout and then select AllSongs in the "show records from" drop down.
Alright, look like I've got some homework to do. Thank you so much for your directions! Please allow me to get back for advice on this one if needed.
Back again and I managed to make it work so far!! I got some educational struggle with the FilterValues thing: the correct syntax for my situation was: not IsEmpty ( FilterValues ( AllSongs::Bands ; Songs::SelectedBand) )
Allright, now I've got these songlists which are selected with respect to the SelectedBand drop-down list. Now I would like to get the corresponding chords/notes when I click the desired song. Would you please point me in the right direction? Thanks in advance!
Write this script:
Set Variable [$Song ; Value: AllSongs::SongTitle] // or use AllSongs::SongID if you have serial id field for each Song record
Enter Find Mode  // clear pause check box
Set Field [Songs::SongTitle ; $Song]
Perform Find 
Now, enter layout mode, select the fields you've placed in your portal and choose button setup from the Format Menu. Set this new "button" to perform the above script.
To tie a nice bow on this, let's add a conditional format so that the song you click in the portal highlights to show which song was selected:
- In layout mode, Select all the fields in your portal row, right click them and select conditional formatting. Enter this formula:
- AllSongs::SongTitle = Songs::SongTitle
- Select a file color for these fields that you like and click OK to dismiss the dialogs.
- Return to browse mode and test it.
Sometimes things take a little time. I have followed your guide and I succeeded to program what I desired. Many thanks for your help!!