6 Replies Latest reply on May 15, 2014 9:14 AM by philmodjunk

    Survey Totals

    FileMakerNovice

      Title

      Survey Totals

      Post

           Hello,

           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:

      TABLES:

           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.

        • 1. Re: Survey Totals
          philmodjunk

               Let's see if I can parse out the relevant relationships:

               Employees----<Evaluations-----<TopAnswers------<SubAnswers     (---< means "one to many" )

               For simplicity, I've left out the "questions" tables.

               If that is correct, you may be able to get the report that you want by setting up a list view report based on SubAnswers with data from Employees in the header or a leading grand summary part, data from Evaluations and TopAnswers in two different Sub summary layout parts and data from SubAnswers in the body.

               But this assumes that you only want to report data in the context of Sub answers and do not need to include data from TopAnswers when a perfect score is recorded and thus there are no related Sub Answers for that TopAnswers records as those records would be automatically omitted from this report.

               So this is your best bet for a simple approach, but you'll need to tell me if that will actually work for you.

          • 2. Re: Survey Totals
            FileMakerNovice

                 You hit the relationships on the head.

                 Displaying perfect scores are not a big deal because the main thing I want to convey in the table is what are the main pain points.  

                 Just wanted to say, if there are 10 active questions in a table, for each evaluation, there would be 10 records in the answer table.  

                 If I omitted my desire to display the top question/answer, is there any way I could do a portal instead of a new layout?  I'd really prefer this to be in a layout that already has a bunch of employee performance data.  Not a big deal, just curious.  

                 So... let's say I only wanted to list each question and how many "isMissed" are marked.  It seems that I should create a new layout for the report.  Upon entering the report, it should search for employee.  Then it should sort by question.  The subSummaries would display the question.  I would then need a summary field that provided a total of isMissed.  Does this seem right?

            • 3. Re: Survey Totals
              FileMakerNovice
                   

              So... let's say I only wanted to list each question and how many "isMissed" are marked.  It seems that I should create a new layout for the report.  Upon entering the report, it should search for employee.  Then it should sort by question.  The subSummaries would display the question.  I would then need a summary field that provided a total of isMissed.  Does this seem right?

                   This was correct and worked flawlessly, thanks again Phil.

                   I'd really like it in a portal but don't think there is a way to do this.

              • 4. Re: Survey Totals
                philmodjunk

                     What do you gain from using a portal?

                     That's possible, but I didn't see that as a good thing if you are going to print or save as PDF from such a layout.

                • 5. Re: Survey Totals
                  FileMakerNovice

                       I guess the primary reason I was wanting to do in a portal was for easy display.

                       The layout that I have is a sort of employee evaluation.  It provides an overview of what the employee is doing, as well as how they are doing.  The customer surveys are one part of the evaluation.  That's the primary reason that I wanted it in a portal.

                       I ended up just putting a button that makes a pop up of the layout that we previously discussed.  I think this breaks up the employee overview and the printing part is something that I didn't consider.  I really like the solution that we created and don't need the portal any longer.  

                       But... if you were to do a portal, which table would it reference and what field would count the total occurrences of a particular question.  No worries if it's too much to explain.  I was just curious more than anything.

                       Thanks again.

                  • 6. Re: Survey Totals
                    philmodjunk

                         You'd still end up with another layout, but if you based the layout on TopAnswers, you could use a portal to SubAnswers to display that data.