10 Replies Latest reply on Jun 25, 2012 8:19 AM by philmodjunk

    Creating Reports across Join Tables

    ScottSimmons

      Title

      Creating Reports across Join Tables

      Post

      I have just recently converted an Access database to FMP12, and generally speaking, I'm happy with the change, but I'm having a hard time creating a report that I need. In Access, this is relatively easy, but I can't figure out how to do it in FMP12. I suspect this is because I'm thinking like an Access user instead of an FMP12 user. Here's my situation:

      I am tracking interview answers that I place in lessons. Each lesson has multiple questions, and each question can have multiple answers. But the kicker is that we may also use the same question in multiple lessons. So I have four tables (related to this issue): Lessons, Questions, Answers, and a join table called LessonQuestions, since the relationship between Lessons and Questions is many to many. What I need to do is print a report that will let me find a lesson, and in that lesson list the questions that are associated with that lesson and then all the answers to each question.

      Here's a listing of my Table fields (that I need to put into the report):

      Lessons
      LessonID
      LessonName

      LessonQuestions
      LessonID
      QuestionID
      Order
      Type 

      Questions
      QuestionID
      Question

      Answers
      Answer
      AuthorID
      QuestionID
      Rating
      Notes
      DateAdded

      The problem I'm having I beliee stems from the fact the same question can appear in multiple lessons, so if I start with answers and build a layout with Lesson, Question, and Answer data, it will only pick one lesson for each answer, and it may not pick the one I want. I'm told you can create a table in FMP that will import data from each of these tables so that I can have one record for every time a question is used in any lesson and every answer to each question. I'm trying to do this using calculation fields, but I simply can't figure it out. In Access I can use a query to do this, but it seems more troublesome to figure out in FMP12.  Any guidance would be greatly appreciated. 

      Just in case I'm not clear above, what I need is a report that follows an outline like this:

      I.  LessonID | LessonName [user should be able to choose one lesson for his/her report]
           A.   Order | QuestionID | Type | Question
                 i.  AuthorID | Answer | Rating | Notes | DateAdded

      On a much less important note, if possible, I'd like to be able to combine the fields "Order," "QuestionID" and "Type" into one code, so that FMP can report the same question as 3-3342F in one lesson and 6-3342M in another. 

      Thanks in advance for any help you can provide. 
      Scott

        • 1. Re: Creating Reports across Join Tables
          philmodjunk

          Even in Access, I'd have a problem with this setup.

          There's no way that I can see to link a specific answer record to the lesson in which it originated as answers link by QuestionID. If they linked by a LessonQuestionID, that would make possible this set of relationships:

          Lessons---<LessonQuestion-----<Answers
                                   v
                                   |
                               Questions

          You could start with a Lesson record, perform a find or use Go To Related records to find all LessonQuestion records, then use Go To Related Records with the 'match found set' option to pull up all the answer records for that lesson on a layout based on Answers. Then you could sort your answer records by QuestionID (in related LessonQuestion record) and use a sub summary part with the same field specified as its "when sorted by" field to display the Question from the related Question record.

          • 2. Re: Creating Reports across Join Tables
            ScottSimmons

            The way I have the relationships set up is like this:

            Lessons---<LessonQuestion>----Questions---<Answers

            I'm thinking about the ramifications of changing the relationships to be the way you described, but I think that setup works only if I want to report the answers for a partciar instance of a question in a lesson.  What I want to do is create a report that will show all answers to a question in any lesson where the question is used, so I (or another user) can choose the best one to place in the lesson script.

            So I'm trying your solution.  I have layout that a user can find a lesson.  Then I placed a button next to each lesson record to run a script called "Lesson QA Report."  The script has three steps:

            1.  Go to Related Record, showing only related records from the LessonQuestions table, matching current record only.

            2.  Go to Related Record, showing only related records from the Answers table, matching all from the found set.

            3.  Sort Records by "Order" in the LessonQuestions table

            This gives me the right answer information, but if I group by lesson and Order, it sometimes reports the wrong Lesson.  This would not be a huge issue, since all the found answers are in the same lesson, so I could simply not show the field.  But the "order" and "type" fields may be different for each lesson that the question is used.  So the report sometimes sorts the answers incorrectly.  I think if I had the answer table linked to the LessonQuestion table by LessonQuestionID, your solution would work, but then, I think, I'd have to separate answers to the same question based on which lesson I'd expect them to be placed.  I'd like to have all answers to a question available to me when I place answers in lesson scripts.

            Is it possible to generate a temporary table from which to run this report?  Let me see if I can explain what I'm thinking:  I have about 1800 answers currently in the database.  Let's suppose about 200 of those answers are to questions that are assigned to two lessons.  So then could I make a table with records, one record for each instance of a question in a lesson and each answer to each question?  The table would have about 2000 records and would have all the lesson, question, and answer info I need to make the report.  I could perform a find on the lesson and then group by the QuestionID sorted by Order.

            Also, is it possible that a different database design would have worked better?  I taught myself Access, and I found ways to make everything work, but that I suppose doesn't mean that everything I did is "right" now that I've moved my tables over to filemaker pro.  Perhaps I should do something differently.  In Access,I created a report based on LessonQuestions (and I wrote a script to let you pick the lesson you want).  It then reports all questions assigned to the lesson grouped under an "Order Header" and the "Detail" reports alll the answers to the QuestionID.

            Thanks in advance for any help you may be able to provide. 

            • 3. Re: Creating Reports across Join Tables
              philmodjunk

              The reason it sometimes reports the wrong lesson is why I suggested a change in the relationships. I did assume that you wanted to see the answers specific to a given lesson, but you can still get your list of all answers for each question with my relationships also--but the steps would be different.

              What you need appears to be a combinaton of the two sets of relationships. Let's see if a few extra table occurrences can make this work for us:

              Start with my original setup but add more occurrences to get:

              Lessons----<LessonQuestions>----questions----LessonQuestions 2----<Answers 2
                                          |
                                          ^
                                      Answers


              A single Go To related records step that takes you from Lessons to answers 2 should pull up the answers you want and now you can sort them as needed to get the results that you need.                        

              • 4. Re: Creating Reports across Join Tables
                ScottSimmons

                Just to be clear, changing the the relationshiops so that I have a

                LessonQuestions---<Answers

                would would require me to write a script to copy a LessonQuestionID into the Answers table so that there could be a one to many relationship between the two tables, right?   And if I do that, wouldn't I end up with Answers only associated with one Lesson?

                Thanks again for your help.
                Scott

                • 5. Re: Creating Reports across Join Tables
                  philmodjunk

                  Yes, and if you want to correct this problem: " it sometimes reports the wrong Lesson.  " then that's what you need to do.

                  It is not the only relationship here, however and does not prevent you from seeing all the answers associated with a given question independent of the Lesson to which it is linked.

                  • 6. Re: Creating Reports across Join Tables
                    ScottSimmons

                    I tried that solution, but it didn't work.  I wrote a script that copied a LessonQuestionID into the Answers table, and established the relationships like this:

                    Lessons----<LessonQuestions>----Questions----<LessonQuestions 2----<Answers 2
                                                |
                                                ^
                                            Answers

                    I then wrote a script that, from a particular lesson, would Go to Related Records in the Answers 2 table.  Again, it shows all the right answers, but it does not always show the right lessons, I believe because the Answers 2 table only has one LessonQuestionID per answer--if the wrong one is in the table, it will report the wrong lesson (and more importantly, the wrong type and order in the lesson).  I think I need a table that shows all possible LessonQuestionIDs for every answer, so that an answer may appear more than once in the table.

                    Now, even with the old relationship [Lessons---<LessonQuestion>----Questions---<Answers], I could create a form that would go to the related records in the LessonQuestion table, and then I could use a portal to show all the right answers.  That worked just fine for being on screen, but I don't know how to get the same thing in a report.

                    Again, any help you can provide would be greatly appreciated.

                    Thanks,
                    Scott 

                    • 7. Re: Creating Reports across Join Tables
                      philmodjunk

                      You'd use GTRR to pull up the desired found set, but specify a layout based on Answers, not Answers 2. That gives you a direct link back to the Lesson linked to each answer record.

                      • 8. Re: Creating Reports across Join Tables
                        philmodjunk

                        Go To Related Record [Show only related records; From table: Answers 2; Using layout: "Answers" (Answers)]

                        • 9. Re: Creating Reports across Join Tables
                          ScottSimmons

                          Okay, I've done that.  And yes, there's a definite improvement.  If I add the Lessons:Lesson field to the Ansewrs 2 table, now all but 2 of the questions show the lesson properly (for the lesson I tested it with).  I also displayed the LessonQuestions 2::Lessons field, and it's all over the map.  But there seems to be one more cause for the wrong lesson being reported.  Do you have any thoughts about what might be causing that?

                          Thanks,
                          Scott 

                          • 10. Re: Creating Reports across Join Tables
                            philmodjunk

                            I also displayed the LessonQuestions 2::Lessons field, and it's all over the map.

                            Use LessonQuestion not LessonQuestion 2, unless, after selecting an answer, you want to see all the other answers to the same question in a portal or something.

                            As to why just one or two don't match records as expected, I can't really tell from here. Since all your other records match, there's something different about them obviously. The obvious things to check are the values in their match fields, to make sure they have the correct values and it's not impossible for there to be an issue with your layout design. Such as: If you put a field from a related table occurrence on your layout, you'll see values from the first related record so if you have more than one related record and expected to see data from a record different than the first related record, you won't see the value you expected.)