1 of 1 people found this helpful
You need a third table between questions and exam.
If you're not already, consider creating an ERD first to better understand and communicate your data design.
As bigtom suggested, and as I read your posting...
1. An EXAM can contain many questions, and
2. A QUESTION could be on multiple exams.
Thus, if that assumption is true, you have a M:M relationship.
The "third" table Tom mentioned is a join table which holds the primary keys from both of the tables so you can look at each 1:M separately. (Possibly called EXAM_QUESTION)
Below is an example of a M:M relationship with STUDENT and COURSE. Notice the STUDENTCOURSE join table in the middle.
FileMaker handles M:M relationships with ease.
HOPE THIS HELPS.
Sometimes I feel really stupid ...
I can do what I want quite easily with just the WHERE_USED table. The join table might be a bit more elegant, but all I need to do is to enter the Exam_Number into the WHERE_USED portal as I select records for inclusion in an exam.
So I've marked bigtom "helpful" and fmpdude "correct" since I wouldn't have figured this out without you!
And maybe someday, I'll create the join table ...
That can work if it's just one form with set questions. The join table is great when you have multiple forms with multiple question and some forms might share the same questions.
What do you mean by "form"?
The database has over 500 questions, all of which are multiple-choice with 4 options. Each exam uses 60 questions, and virtually all of the questions have been used on multiple exams.
Maybe I missed something.
You really only need the join table if you want to view the 1:M both ways (as in M:M). Otherwise, just create a regular 1:M relationship.
As I recall, though I never do it this way, FMP will fake the M:M even without the join table.
Glad to have helped!!!
the 'secret' multiline key is a quasi-M:M that is not in any other db that I've seen.
A field (indexed, global or otherwise) has many values (list of items separated by return). This field can "match" any of the values (an OR match) in the related key field of the other table (or self-join).
many articles out there (and here) on this little well-known secret. here is one:
Right, I was only implying I'd never use FMP's internal trick since it's not cross-platform or standard in any way I know of.
So, if M:M, I'd always create the join table.
And the answer may help those who want to know?
Beverly -- I read the article you provided the link for (twice), but I couldn't understand how it would help address my question.
Although I now have a very viable workaround, I'd be interested to learn another approach since it might help me in the future.
So ... to repeat ... I have a found set of, typically, 60 records. I want to create one child record for all 60 parents, and I want to initialize two fields in each child record with values from the parent.
Multiline keys may not be the solution for you.
That's a simple, but uncommon, 1:1 setup; like a 1:M, ,except you don't have "many" records for each parent.
Create the child table with the PK from the parent and draw the relationship in the RG allowing records to be created in the child table.
As I understand what you just wrote, you should be able to do what you want then, right?
And, try to use an ERD to communicate your design. An ERD will help you as well.