14 Replies Latest reply on Oct 6, 2015 10:14 AM by siplus

    Struggling with relationships


      Hello All,


      Following today's announcement regarding where questions should be posted so that everybody can see them, I'm reposting the following.


      My limited knowledge of FM can only take me so far and I've now reached that point on one issue.


      I am using FM 14. I have four tables in my Library DB: Books, FuturePurchases, Authors and BooksAuthors (join table between Books and Authors). And I am using an Authors value list. Right now, I have the following relationships:

      Books:IDBook →BooksAuthors:IDBookFK



      This works well and allows me to display in my books layout all books from the same author and all authors of a same book.


      I want to also have access to the Authors table from a portal in my FuturePurchases layout. I’ve tried several different relationships between the FuturePurchases table and the BookAuthors and the Authors tables but I can’t manage to make it work. Sure, I can access the Authors list from the FuturePurchases layout portal but there is always something that goes wrong, for instance replacing or recording the name of an author in the FuturePurchases layout portal leads to the replacement of some authors names in the Books layout portal. In other words, I can’t manage to make the Author portal in the Books layout and the Author portal in the FuturePurchases layout to work independently. I suspect the issue is wrong relationships. What exactly should be the relationships between the FuturePurchases table and the BookAuthors and the Authors tables?


      Also is it ok to use the same value list in both the authors field in the Books layout portal and in the authors field in my FuturePurchases layout portal? Or should other settings be changed?


      Thanks for your help

        • 1. Re: Struggling with relationships

          If a FuturePurchase is also a Book, why not do away with that extra table and use a flag in the Book table to denote the status of a Book record (Purchased, FuturePurchase)?

          Otherwise you'll have to replicate for FuturePurchase the entire functionality that you implemented for Book, without any real gain.

          • 2. Re: Struggling with relationships

            Thank You erolst.


            Yes a FuturePurchase is also a book, but I greatly prefer to keep books that I actually own separate from those I may (or may not ever) own. Plus once this FuturePurchase table is in place and working properly, I'll need to create another table (Newspapers) to store documents which definitely must not be in the same table as books. And I'll also want this Newspapers table to rely on the same Authors table as books. Question is, what relationships do I need to put in place?

            • 3. Re: Struggling with relationships

              I believe this would benefit from additional field(s) to designate the 'type' or other 'flags'. That way there is one table with similar information.


              you can use the additional fields in finds to limit what's shown in a list view and/or use filtered portals for the same purpose.


              using one table would definitely facilitate any report that would ever need to show all these 'types' in one report. (and I'd bet that eventually you'll need this).


              one table can have multiple relationships, if you prefer not to use filtered portals.



              • 4. Re: Struggling with relationships

                Thanks Beverly,


                For reasons explained earlier, I really want separate tables for Books, FuturesPurchases, Newspapers, and maybe other stuff later. The entire FuturePurchases functionality is already in place. All that's missing is the appropriate relationships between FuturePurchases and BooksAuthors and Authors. Is it feasible to establish those relationships?

                • 5. Re: Struggling with relationships

                  I suspect that what you want is a trivial portal placed on your FuturePurchases, showing all the Authors, filtered by a global field in which you type/choose the author or leave it blank for ALL authors.

                  • 6. Re: Struggling with relationships

                    I understand! those of use that have done these kinds of relationships can only give advise based on years of experience. We also may have use methods such as yours, because it made sense to the client.




                    • 7. Re: Struggling with relationships

                      Hi siplus,


                      My intention has been to have four (main) tables in this DB: Books, FuturePurchases, Periodicals, Newspapers.


                      On my Books layout, I have a portal with an IDAuthor field in it and a AuthorName on top of the IDAuthor field. Along with the Authors table and the join table BooksAuthors between Books and Authors, I can have access to the list of authors in the Authors table, and I simply pick the author I want for the book I am recording. I want to do the same on the FuturePurchases layout. Hence my initial question: how do I connect the FuturePurchases table to the Authors table and its join table so as to have access to the list of authors from the FuturePurchases layout?


                      The one-table solution proposed by Beverly and erolst is appealing. It sure makes a lot of sense and I will try it. But even if I amalgamate the Books and the FuturePurchases table, it won't make my initial problem go away because what do I do when the time comes to get access to the Authors table from the Periodicals layout. A periodical is not a book and the Periodicals table clearly cannot be amalgamated with the Books table.


                      Any solution to suggest?


                      Thanks a lot everyone.

                      • 8. Re: Struggling with relationships

                        Establish a PubType code, 0 = Book, 1 = FutureBook, 2 = Newspaper etc.


                        Every record in the Book table has a calculated field called PubType, = 0

                        Every record in the FutureBook table ""    ""      "                                 = 1


                        and so on.


                        The join table, common to all, will have its relationships based on SourceTableID AND PubType.


                        A join table record will have a minimum of






                        SO now for example you can be on an author and travel both to all publications, through one relationship authorID - AuthorID, or to a specific type of publication using both elements.

                        • 9. Re: Struggling with relationships


                          This is going to make me sound a bit ignorant to your situation so I apologize, but what is the difference in the data that you want to collect between a periodical, a book, a future book, and newspapers?


                          I know that book and future book are pretty much identical and a flag would work for both of them.


                          What fields do all of them have in common verses the ones that they wouldn't? If I were working on a solution like this I would think I would want to see all of the same basic information from all of the publications in a list, and then any additional information when I would click on them on the side.


                          I would do my table structure something like this:



                          I know this is very basic structure and probably missing a lot of fields that you will need that they have in common, but with a setup like this you  could keep all of your materials in one table, and then any additional information in the Additional Details table.


                          For Example:

                          Some common things I would see between them is a name, and author, a date, etc. So these would all be kept in the publishing information table.


                          Now for a book you may want to keep things like ISBN numbers, revisions, etc. You could create one entry for each of these in the additional details table.


                          For a newspaper you could track the section and page in this table as well.


                          The relationship allows you to pick and choose how many fields you need for each individual item. There may be cases where you need to collect more information on one particular book than another, and this would allow you to do that task.


                          If you have a consistent set of items that you need to capture for each individual type you can create a script that will prepopulate the additional details table with the list of information you need depending on type. I would see this done by clicking a button that says new - newspaper, new book, etc.


                          This could give you a lot of extra flexibility in the future with changes.


                          Then to display all of this on a layout you would just need to create a portal that looks like it is integrated to the rest of the layout. The end user wouldn't know the difference if you had a maximum number of options and never displayed the scroll bar.


                          Here is an example of something similar in action. This is a bit more complex and script driven, but the bottom section is driven off of what is selected on the list to the right. The list to the right is driven from the list on the left. This happens to be an inventory solution I'm working on. Also please ignore my sarcastic comments as I couldn't help but make myself laugh while working on this.


                          Browse View


                          Layout View



                          Just my thoughts on the matter. If you have questions I would be glad to elaborate.

                          • 10. Re: Struggling with relationships


                            Hi Siplus,

                            Thanks for your help.

                            I've created the PubType calculated fields in each of the Books and FuturePurchases tables for now and the PubType field in the join table. Problem is as soon as I created the new relationships, the authors names in all records in the Books layout disappeared. Not sure I did exactly what you had in mind. Here are the relationships I put in place:

                            For Books

                            Books:IDBook → BooksAuthors:IDBookFk




                            For FuturePurchases





                            Not sure what you meant by SourceTableID. Surely, I goofed somewhere to have all the authors names disappear. What do you think?



                            • 11. Re: Struggling with relationships

                              Hi sreese,

                              thank you for providing such detailed comments. To begin with, I am the only user of this db. It will have no public purposes at all. When I began building it two years ago, I had absolutely no idea how databases in general or Filemaker in particular worked. And at the time the db was to have only a Books table, nothing more, but I added three more tables over time. I have a very Cartesian mind and I like to have things well categorized. So I made a conscious decision then to keep the different types of publications separate, despite the fact that all these types of publications have several elements in common. Your solution is an interesting approach which I would surely consider if I were to start from scratch. There is always more than one way to do things and although my approach may not be the best one, I think there is some legitimacy to it. In any case, I've sweated over this db for the last two years and, frankly, I don't have much appetite to go back to the starting block. If I were an expert in FM, il could probably be all rebuilt in a few days but given my poor knowledge of FM, starting from scratch would probably require dozens and dozens of hours of work.


                              Thanks again for your comments.

                              • 12. Re: Struggling with relationships

                                All your existent records in Books must have their pubType filled with 0 and the records in the JoinTable BooksAuthors belonging to them must of course be filled with 0, too.


                                You can do that preparatory work easily through the Replace field Contents command.


                                The new join records you'll create in the JoinTable from Books must of course inherit the pubType of 0, which is book.


                                Same work has to be done for FuturePurchases, too (using 1 instead of 0).


                                Of course, if you went with the calculation field idea (0 in books, 1 in FuturePurchases, insteead of an autoenter) you don't need to fix the Books and FP databases, you only need to fix the BooksAuthors join table.

                                • 13. Re: Struggling with relationships

                                  Thanks siplus,

                                  Once that's done, are the relationships shown in my message three days ago the correct ones?

                                  • 14. Re: Struggling with relationships

                                    MauriceG wrote:


                                    Thanks siplus,

                                    Once that's done, are the relationships shown in my message three days ago the correct ones?