Best way to create a questionnaire for each user and centralise answers

Hi, I have 3 tables - one of users to interview, one of questions, one of questions and answers together. I’ll ask the same question each time but could add new users all the time. I want all responses to be displayed in one table.

What is the best way to do this? If I was doing it as a one off I could do it but making it update when there’s a new user is tricky.

So here I have a table of users and I can add new users at any point:

Southampton University user research

These are my common questions. Ideally I would get this as a blank sheet by filtering on a new user name, or filter to see an existing user’s response

Southampton University user research (1)

And here are all my answers together (though I realise that I could perhaps make the questions a view of this table):

Is there a simple way to do this? Using lookup functions only gives me a drop down so I have to select each answer. I can copy and paste blank questions into the answer table and could have the questionnaire be a filtered view by user of the answer table. But is there a simpler way?

Hi @Jonathan_Richardson,

I has a similar need some time ago and I approached in a similar way, but with some “automation” in the middle.
Basically, there is a Questions table and a Users table.
The complete Questionnaire table is the union of the two with a Date, the Answer column and some other stuff you might find useful.

Anytime you add a new User, you just create the whole set of Questions for that user at once with a button.

Here a basic working example: let me know if it helps:

A better “detailed view” for the questionnaire is obviously a plus.

Cheers!

1 Like

Thanks yes I just copied it and looks like it is. I’ll have a play around and explore the rest of your doc, thanks for sharing.

1 Like

Thanks again @Federico_Stefanato I managed to get a basic version working and I’m very happy! However now I want to make things a bit more complicated and can’t for the life of me work out how to get the next item in a list from the Formula Map.

So in your Questionnaire the Question comes from CurrentValue. However I have also categorised the questions (ie have an extra column) and want to include this in my Questionnaire. There seems to be no ‘next value’ so CurrentValue is always a question. I have used Nth(2) and thisRow.RowID() and other variants but am stumped. Any suggestions welcome.

Thanks for your previous help on this!

Hi @Jonathan_Richardson,

I CurrentValue() refers to the current list item, in this case a row reference for the Question. So a “next value” would return the next question. I think what you’re mentioning is that you want the category of the current question.

If you look at the formula for the button action, you’ll see AddRow() which is the part that adds the items in the row and is setup in Column,Column Value pairs.

The formula in the button right now is…

Questions.FormulaMap(Questionnaire.AddRow(
Questionnaire.Date, Today(),
Questionnaire.Question, CurrentValue,
Questionnaire.User, [Selected User]
))

To adjust it, you can add another pair for the category…

Questions.FormulaMap(Questionnaire.AddRow(
Questionnaire.Date, Today(),
Questionnaire.Question, CurrentValue,
Questionnaire.User, [Selected User],
Questionnaire.Category, CurrentValue.Category
))

Where the CurrentValue.Category would refer to a “Category” column in your Questions table.

3 Likes

CurrentValue can be tabbed out into sub values? Mind blown! That has solved a few problems, thanks @BenLee

PS - please make this clear in formula help, it was the first place I went to and the first reference to CurrentValue just says Can reference currentValue but lacks any link or description of this, nor does the help site reveal anything.

@Federico_Stefanato - could you please change the access level of the doc as I cannot see it. Thanks!

Hi @mallika,
I think I had moved the doc somewhere else.

It should now work: please do let me know if it’s OK.