Creating Reports across Join Tables
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):
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.