But I need to have different Sections for Questions as well
Please provide a detailed explanation of that.
What is a "section"?
What is the purpose to having your questions grouped in sections?
At the moment Questions are grouped into sections on the paper based version. Each section is a particular area of criteria that a location gets surveyed on. If any of the questions within a section get a 'fail' against them, the whole section gets a 'fail' regardless of the outcome of the other question asked within a section.
Then I suggest that you don't need InterviewSections and that you should rearrange your relationships to be:
Surveys::_pkSurveyID = Questions::_fkSurveyID
Sections::_pkSectionID = Questions::_fkSectionID
Thanks, that looks a lot better. So this it what i have now. The idea is that a new record will be created in the Interview table when the user starts a survey on site. They pick what location they are at from the Location table. The __fk_SurveyID identifies the survey template.
I'm stuck however on how to populate the InterviewQuestions table with the list of records from the Questions table and give them the relevant _fk_InterviewID field..
Any ideas on how to make this work? Or if my thinking on this is even correct?
It would be done with a script. And I would link a table occurrence of questions to InterviewQuestions instead of copying the question text.
#From a layout based on Interview...
If [ Not IsEmpty ( Questions::_fk_SurveyID ) // confirm that question records exist ]
Set Variable [$InterviewID ; value: Interview::_pk_InterviewID ]
Go To Related Record [Show only related records; From table: Questions ; Using layout: "Questions" (Questions) ]
Import Records [no dialog ; YourFileNameHere.fmp12 ; Add ; Windows ANSI ]
Go To layout ["InterviewQuestions" (InterviewQuestions) ]
Replace Field Contents [no dialog; InterviewQuestions::_fk_InterviewID ; $InterviewID ]
The Import Records step should import the needed data from the Questions table into the InterviewQuestions join table.
Wow that's perfect thanks! Will this work on FilemakerGo? That's where most users will be inputting data. I read the import records script step doesn't have full functionality there.
It should work as you are importing with target and source tables in the same file. If it doesn't there are ways to loop through the found set of records pulled up by the Go To Related Records to accomplish the same result one record at a time.
Thanks a lot for your help PhilMod