6 Replies Latest reply on Sep 11, 2016 6:48 PM by boltthecolt

    Pulling multiple sets of related data into a table?

    boltthecolt

      Very basic question (Newbie). Sorry but it has me stumped. I have only two tables so far. Major table is a heap of data around a book title. Minor table is a list of contributors ie authors, illustrators, editors etc. Contributors are few. ie often the same author with a small range of possible illustrators etc. Contributors table consists of the usual, Last name, first name, biography, previous works etc. In the main table I have fields for Contributor 1 Last name (Value list) then Contributor 1 First name, Contributor 1 biography etc then same for Contributors 2 and 3.  etc. by joining the two tables with Contributor 1 joined to Contributor creating a relationship I can look up the data for Contributor 1 First name, Contributor 1 DOB etc. All good but how can I do this for Contributor 2 and Contributor 3. If I have no relationship defined between the two tables is there any way to pull automatically data from the related fields for Contributor 1 and keep separate from Contributor 2 and 3. I hope this makes sense.  I don't even know how to ask the question effectively.  Many thanks Mark  Screen shot shows input page of the major table. The data all exists in the minor table. I want to pull the data from the minor and display it in the major table. ie only contributor 1, 2, and 3  name is decided here for the title (via Last Name). The rest should be pulled from the minor table and displayed. !e2eTitles.jpg

        • 1. Re: Pulling multiple sets of related data into a table?
          coherentkris

          Any time you are tempted to name tables or fields thing_1, thing_2, thing_3 you should stop and read up on how to normalize data.

          If you don't get the initial schema in a decently normalized state you'll be pushing technical debt into the future.

          1 of 1 people found this helpful
          • 2. Re: Pulling multiple sets of related data into a table?
            philmodjunk

            Remove all those name fields from your Major table. They shouldn't be there. The name fields in your contributors table are all that you need.

             

            From working with nearly the exact same issue with others (Literally a books and contributors like you have here), it is very likely that you need a many to many relationship between the major table and contributors because any given record in the book table can link to many contributor and any given contributor might link to more than one book.

             

            This requires a third table in order to link books to contributors and we call it a Join table:

             

            Books---<Book_Contributor>-----Contributors

            Books::__pkBookID = Book_Contributor::_fkBookID
            Contributors::__pkContributorID = Book_Contributor::_fkContributorID

             

            On your Books table, you can place a portal to Book_Contributor and, if "create" is enabled for the Books to Join Table relationship, you can select a different contributor in each row of this portal and by doing so, create a record in the join table (Book_Contributor) that links the current book to the selected contributor.

             

            You can also set up a portal to the join table on the contributor layout and use it to list all the books to which that contributor has/will contributed to.

            1 of 1 people found this helpful
            • 3. Re: Pulling multiple sets of related data into a table?
              beverly

              an alternative that's working in some of the rest of the world may be the EAV method:

               

              This is a table that relates back to the parent with foreign key, but the two fields/columns Attribute & Value store data that may be 'flexible' to the Entity (parent).

               

              This data may be shown in a portal (scrollable/flexible). This data (as related) can be searched and reported from the "line items".

               

              You may get into 'un-normalized', such as several books with the same author, publisher, etc. This may be where you decide to then normalize some of the related data into separate tables and keep some in the EAV table.

               

              Books -> authors

                         -> publishers

                         -> otherAttributes

               

              etc.

               

              Mostly it depends on how it needs to be searched and reported. If you find you may be searching for all books by the same author, then for consistency a separate table is needed. If you find you have multiple authors for a book, then a separate table may be more useful.

               

              beverly

              1 of 1 people found this helpful
              • 4. Re: Pulling multiple sets of related data into a table?
                boltthecolt

                Thank you so much for going to the trouble of decoding the question, seeing the need and responding appropriately. I really appreciate it. The fact that you understood it completely from my use-case was a real bonus! While the answer still assumed a little more knowledge than I have, it introduced me to terms that allowed me to Google appropriately and get the answers I needed. The hints were "Join" table, "Primary" and "Foreign" keys, "many to many" relationships. Also new were "Portal" view so you can see it is early days... We only have six books but even with that, all the metadata associated with each title has exceeded Excel's capability to handle elegantly. I am enjoying experimenting with FileMakerPro. It is all new to me and very different way of thinking about data. Your suggestion has enabled me to handle this requirement elegantly and I thank you for that! Thanks again.

                Mark

                • 5. Re: Pulling multiple sets of related data into a table?
                  boltthecolt

                  Thanks Beverly,

                  Thanks for taking the time to respond. Having just barely got my head around one method I think I will practice that for a while before seeking others.  I really need to walk first. Others I am sure will find the suggestion useful more immediately.  best wishes,

                  Mark

                  • 6. Re: Pulling multiple sets of related data into a table?
                    boltthecolt

                    Hmm.  A Quick Google shows a lot has been written about Normalizing data. I will start reading..,

                     

                    thank you

                    Mark