Forum Academy Marketplace Showcase Pricing Features

Database for Survey Results

I’m having trouble setting up my fields in a database to calculate for reporting for a survey:

Hypothetical Survey asks typical questions like:

  1. “How satisfied are you with your company?” Responses: <Dissatisfied, Satisfied, Extremely Satisfied>
  2. “How satisfied are you with your manager?” Responses: <Dissatisfied, Satisfied, Extremely Satisfied>
  3. “How satisfied are you with your position?” Responses: <Dissatisfied, Satisfied, Extremely Satisfied>
    Responses are currently radiobuttons displaying from an option set.

I currently have the database structured as:
Data Type: Survey Results
Fields: Company, Manager, Position and I’m saving the response (the option) to the field.
So let’s say the user responds:
Company - Dissatisfied
Manager - Extremely Satisfied
Positon - Satisfied

I then want to report: “You scored lowest on “Company” you scored highest on “Manager””. Or, for a bigger survey, use a RG to say “Your lowest 3 responses were X, Y, and Z”

My thought was to add an Attribute to the option set to tie a response to a numerical score, or just change the fields to a number field and use the workflow, “when input is “Dissatisfied”, Company = 1, Satisfied = 2, … etc.” To get:
Company - 1 (Dissatisfied)
Manager - 3 (Extremely Satisfied)
Positon - 2 (Satisfied)

Then I could sort a RG by the numerical ranking and display the field that ties to that number. But that’s where I’m getting stuck: how to write the search for the RG to tie the numerical ranking back to the data fields so it returns 3, 2, 1 = Manager, Position, Company and I can then take the top Row(s) for scored the highest and lowest for scored the worst.

Any thoughts from the DB experts would be appreciated. Also, if this is covered in another thread or tutorial I"m not finding then just point me to it. Thanks!