3 Replies Latest reply on Dec 18, 2013 12:37 PM by philmodjunk

    Field information from other table's field



      Field information from other table's field


           Hello to you all,

           I just started my trial period of FM13. Checking out what I can do with it in order to decide to purchase or not. So far it looks pretty promising, but I guess I'll be asking quite a few questions in the following days ;)

           So, I'm creating a database for a Library, and one of the first things I'm trying to deal with is the Authors field. As to make things easier to input new records, and to keep the Authors names nice and tidy, I suppose it's a good idea to create a separate Authors table, right? As I imagine it, there's the Main table with all its fields, like Title, Editor, ISBN and so on, and of course one field for the book's Author. I would like to have the Author's field in the Main table to be related to the Authors table.

           Let's say I'm creating a record for a William Shakespeare's book for the very first time. Since there's no record for William Shakespeare in the Authors table yet, I would need to create one. Can I set like a button (or something else, feel free to suggest it) right next to the Author field that, once clicked would open a new window to create a New Author in the Authors table?

           That's the first part of the question. The second part is about creating a new record for a book of an Author that already exists. How can I set the Author field in the Main table, to automatically sugest similar names as I type the first letters of the name? Suppose there's already a record for William Shakespeare in the Authors table. When I try to create a new book from William Shakespeare, as I type "wil..", the field would automatically show me a list of authors names that start with "wil.." so that I could pick the correct one. Is this possible?

           Hope I was able to explain what I mean.

           Thank you so much for your help.

        • 1. Re: Field information from other table's field

               What you are describing will be more complex to set up than you might first think. An author can write many books and since authors collaborate on books, a book can be written by more than one author. This will require setting up a Many to Many relationship in order to make that work.

               Start with these relationships:


               Authors::__pkAuthorID = Author_Book::_fkAuthorID
               Books::__pkBookID = Author_Book::_fkBookID

               You can place a portal to Author_Book on the Authors layout to list and select a Books record for each given Authors record. Fields from Books can be included in the Portal to show additional info about each selected Books record and the _fkBookID field can be set up with a value list for selecting Books records by their ID field. And you can place a portal to Author_Book on the Books layout to see all authors credited with writing that book as well.

               To make the above work, you'll need to learn how to set up relationships, portals and value lists. Scripts are also often needed to make it easier to add new authors and/or new books and link them in with a matching Author_Book record at the same time.

          • 2. Re: Field information from other table's field

                 Well, that is a lot to process, but is much welcome!
                 I will dig into it as soon as possible, and will likely come back with a few more questions ;)
                 I have the Lynda.com series about FM12, and I think there's a part about relationships and a bit of scripting too, so I guess I'll start there. Any additional resources on this particular issue that I should read/watch?

                 Thank you so much, you're being most helpful.

            • 3. Re: Field information from other table's field

                   You can examine this demo file that I set up for illustrating different layouts and scripts for working with many to many relationships: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                   You can open this file by using Open from FileMaker's File window to convert the file to the .fmp12 format.