10 Replies Latest reply on Feb 17, 2011 12:09 AM by Corné

    Guidance requested for a song book/directory



      Guidance requested for a song book/directory


      Hello all,

      As a database (and thus FileMaker) newbie, I request your help with this. Let me explain my situation:

      I play the guitar in a few bands. I have an Excel sheet with notes for all songs that I play in these bands. I use checkmarks in columns to check what song I play for what band. There are songs that are performed by two (or more) bands as well. I use the 'auto filter' function in Excel to select all songs that I need for a particular band. Now I would like to transport this sheet to FileMaker Pro; an iPad version would be great! Can you please help me in the right direction how to do this? I have the following picture in mind:

      - I have one song with chords / notes on one page.

      - With a dropdown box I can select the band I'm performing with at that time.

      - In a left column I have a list overview of all songs that is played by that band. When I select a song from that list, the chords / notes of that song are displayed.

      1. Should I use one checkbox per field (1 per band) to assign the songs to the band, or should I use 1 field with multiple checkboxes?

      2. Ehm, in general, what would be the approach of your choice how to make this project?

      Any help is greatly appreaciated. Thanks in advance!

      All the best,


        • 1. Re: Guidance requested for a song book/directory

          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.


          • 2. Re: Guidance requested for a song book/directory

            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".

            • 3. Re: Guidance requested for a song book/directory

              Hello Phil,

              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?



              • 4. Re: Guidance requested for a song book/directory

                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.

                1. 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.
                2. Drag from a field in Songs to a field in AllSongs. Doesn't matter which field.
                3. 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.
                4. Also define a text field, selectedBand which we can format with the same value list as your check boxes.
                5. Put a portal to AllSongs on your Songs layout and specify a portal filter as:
                6. not isEmpty ( FilterValues ( AllSongs::BandCheckbox ; SelectedBand) )
                7. Format SelectedBand as a drop down list.
                8. write a one line script: Refresh Window [Flush cached join results]
                9. Set up SelectedBand with an OnObjectSave script trigger to perform the above script.
                10. 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.

                • 5. Re: Guidance requested for a song book/directory

                  Hi Phil,

                  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?



                  • 6. Re: Guidance requested for a song book/directory

                    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.

                    • 7. Re: Guidance requested for a song book/directory

                      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.


                      • 8. Re: Guidance requested for a song book/directory

                        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!


                        • 9. Re: Guidance requested for a song book/directory

                          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:

                          1. In layout mode, Select all the fields in your portal row, right click them and select conditional formatting. Enter this formula:
                          2. AllSongs::SongTitle = Songs::SongTitle
                          3. Select a file color for these fields that you like and click OK to dismiss the dialogs.
                          4. Return to browse mode and test it.
                          • 10. Re: Guidance requested for a song book/directory


                            Sometimes things take a little time. I have followed your guide and I succeeded to program what I desired. Many thanks for your help!!