Evaluation database with external forms

1

Hi all!

I am trying to setup a framework to evaluate certain products and rank them on different properties.
I have used “Bouncing Balls” as an example in this post.

There are certain parameters that can be objectively measured, and certain parameters that can only be determined through subjective data gathering.
As an administrator of the evaluation, I or one of my colleagues would add each new product into the evaluation framework and insert the objective data.

After that, we would let a focus group of individual participants (somewhere between 5-30 participants per product) evaluate the product, under more-or-less controlled circumstances.
The participants will be handed the product, follow a guided method for evaluation, and use some type of form sheet to fill in a subjective value for each parameter.

Eventually, there could be around 30-40 products that have been evaluated and ranked against each other.
Some participants will evaluate most products, but many participants will just evaluate a couple of products.

I would really like to be able to use Coda for this, but I am not sure if it can suit my needs.
I know there are a lot of really smart people in this community so I would be very glad to hear your thoughts on this project! :slight_smile:

I have shared a rough draft of how a picture the document at the moment:

To start with, I have a few concrete questions:

What formula should I use on the “Average Participant TOTAL SCORE”, in order to get an average value from all participants per Ball Model?

How to get participants to fill in the data for each Ball model without being able to edit the table?
Has anybody tried to connect Google Forms or Formtyper for something like this?
I saw that it is possible to use Buttons to create a kind of Form, but if I understand correctly the participants will either need to be logged in or they will not be able to save the data (I think).

I would like the form to dynamically load the products (as a multi-choice for the participant), otherwise there is a risk that the participant will use different spellings.
But I think that connecting Google Forms or Formtyper via Zapier is only a one-way communication to feed data into Coda, and not vice versa.

If the Participants cannot use Coda directly (but via a third-party form), then Coda is just used as a database.
Would it be useful to use Coda for this, or do you know any other solution that could fit me better?

Thank you for reading down to here, and I would appreciate any help I can get!

Best regards,
Rickard

2

Hey Rickard!

  1. The formula to aggregate scores for this model will be [Subjective Data].Lookup([Ball model], thisRow).[TOTAL SCORE].Average()

  2. To the best of my knowledge, there’s no way to set granular access to parts of the document. Maybe someone from Coda would answer that better. If you have a controlled environment where you can monitor what your focus group is doing, I wouldn’t worry about them editing other parts of the document — there’s a doc history so you can still revert changes if any participant goes rogue.
    What else you can do is make a section for current user’s answers only, and embed the doc on a webpage in such manner that the user cannot see and switch to other sections.
    Also you can set up automation to track data edits and detect if any user wanted to tamper with previous replies.

  3. Not sure on forms integrations. If the form software (Typeform etc) allows populating a single-choice question from external data, there is a Coda REST API that you can use to query values dynamically from your Coda table. If Typeform etc exposes their WebHook/API to update the form, I think you can set up automation or a pack (Zapier etc) on your products table to detect changes and then ping that API. But the general answer is: I don’t think that’s an easy task, and much easier would just be to manually update the form along with updating the list of products.

1 Like
3

Hi Paul,

Thank you very much for your feedback!
Your formula did the trick, and your answers were more than helpful.

I have now made some progress by using TypeForm, and it works pretty neat! :slight_smile:
I just have to finish up a few more things and then I can share it here to show what I did.

Just a quick formula question again:

Summary

In the document now I have moved from the Device being the key identifier, to using the Form ID key that I get from TypeForm.
So every device I will test will be setup with a unique TypeForm, and this ID will be automatically inputted to the table via Zapier.
So in my “Ball Model & Objective Data” table I want to specify in advance the TypeForm ID key that belongs to a certain Device.
Then when the participants do the evaluation via TypeForm, the “Subjective Data” table will be automatically updated.

So my question is:

How can I compare the Form ID between “Subjective Data” and “Ball Model & Objective Data” table, and write the “Ball model” name in “Subjective Data” so that it corresponds with the “Device” name from “Ball model & Objective Data”?

Sorry for a super confusing sentence…

4

You can again just use a lookup formula to insert the model name. But what I’d suggest is go further and full-fledged link two tables:

  1. In “Subjective data” table make a column with formula:
    [Ball model & Objective Data].Lookup([Typeform Form ID], thisRow.[Form ID])
    This will not just insert the string, but assign an “Objective data” row to this row.
  2. Since “Device” column is already a display column in “Ball model” table, no extra steps required — its value will be displayed in the “Subjective data” table.
  3. Since rows are now linked, you can simplify “Average Participant TOTAL SCORE” formula from:
    [Subjective Data (from Typeform)].Lookup([Form ID],thisRow.[Typeform Form ID]).[TOTAL SCORE].Average()
    to
    [Subjective Data (from Typeform)].Lookup(Device, thisRow).[TOTAL SCORE].Average(),
    i.e. lookup not on the Form ID string, but on the link between rows.

5

Also, interesting choice to make separate forms for separate products. Didn’t you find a way to implement this with a single form and just having an e.g. pre-set selection list for picking a product?

6

Hi again,

I have made some new setup with my document, and am now confused as to how to do what I want.

  • I have 5 product samples that will be evaluated by a number of participants
    • The participants will fill in a Google Forms document which will populate this Coda document
    • The question sheet looks something like this:
      • Question 1: Rate the product quality for each sample:
      • image.png
        • Question 1: Please make a comment on your decision (FREE TEXT FIELD)
    • And then I have a few other similar questions about the product samples (2, 3 & 4), plus some standard questions (like email, age and experience)
    • The participants are not aware of the Real Sample Name, but will only see the an Abstracted Sample Name in their questionnaire
      • These will change between each question. I have an index table that specifies which Real Sample Name is behind each Abstracted Sample Name for each question.
    • In Coda, each column in the DATA table represents a question, and each row represents the answers from one participant.

Now I want to create a table that is somehow based around the “Real Sample Names”, that pulls all the average from each respective question, for example:

  • Sample A Results
    • Average from:
      • 1-A
      • 2-C
      • 3-B
      • 4-B
  • Sample B Results
    • Average from:
      • 1-B
      • 2-A
      • 3-C
      • 4-E

Does anyone have any tips for how to structure my document to achieve this in a nice way?

Complications I’ve stumbled upon so far:

  • The data from Google forms doesn’t just contain numbers, but also text (e.g. “1 - Bad”)
    • This I think can be fixed with “left(Table.Column, 1)” to filter out only the first character
    • Do I need to set this up in a separate Table/Column first before I start to do the average, or can I do this directly in the “Results” table?
  • I have noticed that it doesn’t always work to find columns that start with a number
    • E.g. when doing “left(DATA from Google Forms.1-A, 1)”, Coda does not recognise “1-A” as a valid column.
    • Is this a bug?

Here is a link to my document:

Grateful for any help!

/Rickard