2 Replies Latest reply on Jun 15, 2009 12:22 PM by CMT

    Relationships on tables



      Relationships on tables




      I used FileMaker during some years in the past, but never worked with relationships. Now I'm on a project for a sheet music library that is almost huge (about 500.000 titles), so saving disk space is crucial.


      I really need a help on building this project.


      I was thinking how to develop it, and I had my conclusions. That's what I got:


      For the Composer Field (as an example), there would be a separated table. The fields on this table are 4:


      1.Composer ID

      2.Firt Name

      3.Last Name

      4.Full Name (this field is a sum of the last two above)


      The problem is:


      I don't know if it's possible to do the following: When creating a new record of a sheet music, in a "general" table, adding the composer name will add a new record on the Composer table?


      Also, the ID must be serial number, and we can't keep memorizing all composer's IDs. Resuming: the ID must be stored on the "general" table record saving disk space, and It has to be found when typing the composer's name. At the same way: when the name is not found on composers table, the new record is automatically created.


      If possible, any ideas on how do do it? 


      Thanks in advance for everyone who took some time to read this.

        • 1. Re: Relationships on tables



          Thank you for your post.


          Pull down the File menu and select "Manage -> Database...".  Click on the Relationships tab, and you will see a graphical representation of your Sheet Music Library table and Composer table.  Make sure that "Composer ID" field is present in both tables, because we will want to link the two tables together via this field.  Once linked, double-click the icon that is halfway between the two tables.  On one side will have information from the Sheet Music Library table, and the other side will have information from the Composer table.  On the Composer table side, near the bottom, be sure to check the option "Allow creation of records in this table via this relationship".  Click OK.


          Now, go to the layout that displays your Sheet Music Library table, and create a portal into the Composer table.  Include the fields Full Name, First Name and Last Name.


          Double-click on the Full Name field and a "Field/Control Setup" dialog box appears.  Change the "Display as:" pop-up to show "Drop-down list"


          Just below that, "Display values from", select "Manage Value Lists..." and another dialog box appears.


          Click "New..." and another dialog box appears.


          Enter the name "Full Name", and select the option "Use values from field...".  Another dialog box appears.


          Under "Use values from first field", select the Composer table, and then select the "Full Name" field.  Click OK a few times.


          Go into Browse mode, and you will be able to click on the Full Name field, and a list of composers will appear.  Type the first couple of letters to move you down the list to the appropriate entry, where you can then select the Composer.  If a composer isn't listed, then you can add the First Name and Last Name in the appropriate area.


          This also allows you to enter two or more records for composers.  For example, John Lennon and Paul McCartney.


          This should give you a good starting point.  If you need clarification for any of the above steps, please let me know.



          FileMaker, Inc. 

          • 2. Re: Relationships on tables

            Hi TSGal,


            Thanks a lot, your instructions worked very well. I can see that relationships provide a much more powerful database.


            Just one problem appears:


            When a use the drop-down list (that's working) to select a composer name in a new sheet music record, I receive the following message: "This action cannot be performed because this field is not modifiable". It's impossible to select an item.


            Do you know what's happening and how to fix this? Maybe I made any mistake… 


            Thanks so much, and congratulations, FileMaker is incredible.


            Best wishes,