AnsweredAssumed Answered

Survey Totals

Question asked by FileMakerNovice on May 14, 2014
Latest reply on May 15, 2014 by philmodjunk


Survey Totals



     I am trying to provide some reporting into a survey layout.  I started with this (  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:

     John Smith
     4 Surveys

     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.