2 Replies Latest reply on Feb 10, 2010 3:12 AM by jtoynbee

    Linking records and updating shared fields

    jtoynbee

      Title

      Linking records and updating shared fields

      Post

      Using FM10 on iMac G5, OS10.4. Newish to FM.

       

      I've built a DB to track academic book reviews. I have three tables: books (1 record per book, fields with bibliographic details and milestone dates), reviewers (contact information) and publishers (contact information) all interrelated via ID number keys and working fine. Most books are reviewed individually and this is working fine. However, some are grouped to form double book reviews and some into groups of 3–10 books for a review article.

       

      I am trying to find the best way to link records in the books table in such cases. So that, for instance, one doesn't have to update the project milestones (book disptached, deadline, review received and so on) in each book's record. Not all the fields will be behave in this way however, the book titles will of course be different so too the publisher ID and publisher name fields, and so I need to link them in such away that I can choose which common fields will update together across the set of records, leaving the others with their individual data.

       

      Suggestions please and, if you have the time, an idiot's guide to setting it up.

       

      Thanks

       

      Jeremy

        • 1. Re: Linking records and updating shared fields
          philmodjunk
            

          Is it always one reviewer to a given "review"? I see you have multiple books that can be assigned to a given review and wonder if multiple reviewers is also a possibility.

           

          In any case, you'll need at least two more tables to make life easier. I'll assume one reviewer only to start:

           

          table: Reviews

          (Put your review specfic data here rather than in Books)

          ReviewID (auto-entered serial number)

          ReviewerID

           

          table: Books_Reviews

          ReviewerID

          BookID

           

          Relationships:

          Books::BookID = Books_Reviews::BookID

          Reviews::ReviewID = Books_Reviews::ReviewID

          Reviews::ReviewerID = Reviewers::ReviewerID

           

          Books_Reviews becomes a "join" table that allows you to link many books to many reviews.

           

          Apologies for not providing more details, but maybe this will help get you started.

          • 2. Re: Linking records and updating shared fields
            jtoynbee
              

            Dear Phil

             

            Many thanks. No you are right to assume there is only ever one reviewer per book. See what you are getting at re extra tables and will have a go at modifying the DB. Will come back to you if I get stuck. This also covers a related question I didn't include. How to handle issues, groups of accepted reviews selected to go in one printed issue, I see I would handle this the same way with a further table: Issues.

             

            Thanks again

             

            Jeremy