I am trying to provide some reporting into a survey layout. I started with this (http://forums.filemaker.com/posts/beeff7fcd9). I hope I can explain this:
Evaluations- This table contains customer surveys. A new record is created for each survey received.
TopAnswers- This contains the foreign key of id_evaluations. At new survey creation, it creates a record for each active TopQuestion.(Copying id_evaluations and id_TopQuestions) There is a field called "answer" with a 1-5 value for rating.
TopQuestion- This contains a question, such as "Were you satisfied with EmployeeName?" Additionally, it contains a boolean field called "active". At eval creation, a script searches for active questions and creates a TopAnswer record for each.
SubAnswers- Upon a non perfect score (4 or below) a sub answer tables creates a record for each active SubQuestion. Similar to how TopAnswers/TopQuestions works. Upon marking the 4 or below in TopAnswers, a new record is created for each SubQuestion. It marks the id of SubQuestion and the id for TopAnswers and this is how the tables are related. This table has a "missed" field that is boolean.
SubQuestions- Exactly like TopQuestions. Contains id, the question and an active boolean value.
The creation and survey layout works flawlessly (thanks Phil for pointing to that forum) but I can't get reporting straitened up. I am looking at the employee table. This links to Evaluations by EmployeeID. What I want is to be able to get a total and a breakdown of each SubAnswer, ideally, broken down by TopQuestion. Something like:
Were you satisfied with employee: 4 *Count of <4 score.
Did employee understand your issue? 1 *Count of how many times this was marked.
Did employee provide friendly survice? 4
Was employee interested in resolving your issue? 0
Were you satisfied with automated phone options: 5
Time to reach rep was too long? 5
I created a portal in a layout that uses the Employee table. I am starting out small with only listing and counting the subQuestions/subAnswers first. I've tried summary totals of "isMissed" as well as calculation fields in both topAnswers and subAnswers. Nothing seems to work.