3 Replies Latest reply on Feb 10, 2014 7:58 AM by philmodjunk

    Help Structuring Customer Survey database



      Help Structuring Customer Survey database


           We would like to generate reports based on customer satisfaction surveys we receive each month in a single csv file that we import into FileMaker. Each survey contains about 15 fields including a Comments field containing free form text.

           After import, we classify each survey with one or more metatags. Metatags are used to standardize comments entered by the customer. One customer may use "complicated." Another may use "complex", Another may say, "not too easy." etc. Our code for all of these would be, "Complex." We have 25 of these codes but the list might grow. Once classified, we want to generate reports showing trends. 

           In principle, the process is simple but I'm having a hard time with the relationships.

      To Classify New Surveys:

      1.           Find New, Unclassified Surveys.
      3.           Go to Layout showing Customer Comments (from Survey table) and checkbox list of metatags from Issues list
      5.           Create an Evaluation by assigning one or more metatags using standard tags from Issues table.
      7.           Mark Survey as Classified.
      9.           Go to Next Unclassified Survey.

      For Reports

      1.           Summary table showing ranking of most commonly used metatags.
      3.           Chart showing same.
      5.           Evaluation showing how each Survey was classified.
      7.           Similar reports based on info in the Survey table (product used, date of survey, customer size, etc.) 

      Table Structure So Far

           I currently have three tables.

      1.           Surveys: Approximately 700 records holding surveys already imported. Each Survey is assigned a unique Survey ID.
      3.           Issues: Approximately 1,200 records. Each record contains a field referencing the Survey ID. Each record in Issues contains a field holding the Survey ID and a single value for the Issue.
      5.           Codes: This is the standard set of metatags we currently use. 25 records. 

           Here’s where I'm stuck.

           How to associate the records already in the database.  e.g. Do I need an Evaluations table joining the Surveys and Issues?

           What table and relationship structure, table occurrences, etc, do I need to create new Evaluations? Similar question to above? Do I need to create a new table, Evaluations, to link Surveys, Issues and Codes? If so, how is this table linked to the others?

           What fields are needed to count and rank the issues and to generate the summary reports?

           Thanks for any and all help.


        • 1. Re: Help Structuring Customer Survey database

               How exactly do you do this:


                    Create an Evaluation by assigning one or more metatags using standard tags from Issues table.

               For greatest reporting flexibility, I suggest using a portal to a related table to assign the metatag data. Then you can base your report on this related table to get your results.

          • 2. Re: Help Structuring Customer Survey database

                 Thanks for your fast reply yesterday. For some reason - I'm still struggling to make this work. Maybe the attached diagram will help.

                 Do I need a join table (similar to Invoice table) between Surveys and Issues Assigned table? 

                 To generate reports that count and rank Issues, do I need a table holding separate records for each Issue assigned to each Survey?

                 Thanks again for your help

            • 3. Re: Help Structuring Customer Survey database

                   IssuesAssigned should be set up as a join table between Issues List and Evaluations: one record in this table should link one issue to one evaluation.

                   A single value field in IssuesAssigned should record a single issue--no lists of values.

                   With that set up correctly, a list view layout based on IssuesAssigned can allow you to sort records by the Issues field to group them by issue and a sub summary layout part can be used with a "count of" summary field to provide a count as to the number of records with that specified issue.