To start, I suggest structuring your interview questions after the pattern shown here: Need aid on generating a report from a survey layout.
With that set up, you can pull up different sets of question records for different interviews and still record all the results for each interviewee.
It has taken me over a week to come back to this project. Thanks for the response. I read and re-read that post several times and still a lot confused. Is there a sample file out there that could help? Here is what I think it should be, lets omit the candidates table 'cause that's pretty straight forward.
Interview::p_ID ----- Answers::f_InterviewID
Answers::f_questionsID ----- Questions::p_ID
Interview- Each interview that is performed.
Answers- Answers to question asked. An answer is created per each question.
Questions- A list of questions, each as a separate record.
I apologize but I am really confused and may just need to ask a couple questions. So... when a new interview is created, how does the answers table populate with new records, is it automatic or is a script required?
So... One interview with 5 questions would have 5 records in the Answers table? All would have the same f_InterviewID but different f_questionIDs? It's slowly sinking in, just can't figure out how a new record in answers is created.
Interview::p_ID = Answers::f_InterviewID
Answers::f_questionsID = Questions::p_ID
Looks correct for a many to many relationship between interview and questions with the answers populating a join table linked to both an interview and a question.
When a new interview record is created, you need some means to select the questions you want to ask during that interview. By creating a record in Answers linked to the new interview and linked to a specific question, you select that question for that interviewee to answer. There are many options for how you might select questions for a given interview.
You can select the questions one at a time in a portal. (I recommend that you set this method up first to confirm that you have things set up correctly.)
Put a portal to Answers on an Inteview layout. Format f_questionsID with a value list of Question ID's and question text. Select a question from that value list and you have created a record in Answers linked to that interview and that question.
You can select groups of records for an interview.
If you add a category field in questions and assign each question to a category, you could select a category from a value list on a field and a script could then pull up all questions in that category and loop through them to create the needed records and links in the Answers table to link them all to the current interview record.
You can pull up groups of questions and then click to select records from that group for your interview.
This is a more sophisticated version of the previous. Instead of simply selecting all questions from a specified category to your interview, you select a category and a list of question in that category are displayed. You then click individual items in the list to select them. You can even set this up to look and function like a group of check boxes where you can click once to select a question and click a second time to clear the selection. Such a list of questions can be shown in a list view layout--possibly popped up in a modal window, or in a portal--that could be placed in a popover in FileMaker 13.
These methods are not mutually exclusive, you can set up your system to use any or all of them in combination.
Here is a demo file for linking Contacts to Events (think questions to interviews) in a many to many relationship. It illustrates the first option and also shows a simple version of the simulated check boxes method: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
If you are using FileMaker 12 or newer, use Open from FileMaker's File menu to open this file and produce a copy converted to the newer file format.