2 Replies Latest reply on Oct 29, 2008 9:59 AM by scottgh

    Entering and viewing multiple values



      Entering and viewing multiple values


      Hi, I am working with an existing library database, where one book can have multiple authors, subjects, library locations, etc.


      So for example, to relate books and authors, my database includes the following tables:



      -Book ID





      -Author ID

      -Last Name

      -First Name



      -Author ID

      -Book ID


      My goal is for all these fields - Book Title, ISBN, author(s), subject(s) - to look like a single seamless "record" per book to the end user. So, within a single book "record," we need the ability to view multiple authors per book, as well as the ability to enter multiple authors when creating new book records.


      Ideally, all values entered would be validated against value lists (e.g., the Author list or the Subject list), and new values (e.g., new Authors) could easily be added.


      I’m new to FileMaker, so any advice would be greatly appreciated!

        • 1. Re: Entering and viewing multiple values



          It appears that you have all the necessary fields in all three tables.


          If you haven't already created these tables, launch FileMaker Pro and create a new database.  At any time, you can pull down the File menu and select "Manage -> Database..."  This allows you to create the three tables, the fields for each table, and then in your Relationship graph, you can take the BookID field from your Book table and drag it to the Book ID field in the Book_Author table.  This now connects the two tables together.  Do the same with the Author ID field in the Author table to the Author ID field in the Book_Author table.


          While still in the Relationship graph, double-click on one of the links, and at the bottom, check "Allow creation of records in this table via the relationship" for both tables.  This way, you can add information to either table, even if you are in a different table.  Do this for both links.


          Once you exit out of Manage Database, pull down the View menu and select "Layout Mode".  This allows you to add the fields onto the form.  On the left side, there are some tools.  Just below the oval tool is a graph object.  Click on that, and draw a square on the layout. This will allow you to have a "portal" into one of the other libraries, so you can view authors in your Book table, or view books from your Authors table.


          This should get you pointed in the right direction.


          If you need clarification for any of the above steps, please feel free to contact me.



          FileMaker, Inc. 

          • 2. Re: Entering and viewing multiple values

            I'll get you part of the way there, then by studying this calculation, you will be able to add the other things you want to display for one record.
            First, in Authors, create a calculation called "Full Name". Its result is text (at bottom of calculation dialog). It simply displays the full name of the author. The If statements simply display nothing if either the First or Last Name fields are empty. So if Madonna wrote a book, her name would display properly.
            If ( IsEmpty ( First Name ) ; "" ; First Name ) & If ( IsEmpty ( Last Name ) ; "" ; " " & Last Name )
            Now, in the Books table create a calculation called "All Authors".
            Substitute ( List ( Authors::Full Name ) ; "¶" ; ", " )

            The "List" statement gets a list of authors related to that book. If there are multiple authors, their names will be separated by a return character.
            The "Substitute" statement replaces each return,"¶", with a comma and space, ", ".


            Now you can expand on this to create a calculation in Books that displays the book's Title, ISBN and subject also. I hope this helps.