Creating a quiz in Dataverse and storing the answers and questions

[shavora]

As Dataverse has the permission structure we need, I am looking into creating a table to collect answers to questions. 

I have 76 questions and the question text exceeds 100 characters.

I am wondering how to create it:

QuizTable:

Column1 = Questions

Column2 = Answers (text input)

For each participant I would have 76 rows of data.

Another option:

Column1 = Question1

Column2 = Answer1

Column3 = Question2

Column4 = Answer2

Column5 = Question3

Column6 = Answer3

This would give one row per participant and internally I would have to map the Questions and Answers in the canvasapp. Is this is good option?

Third option:

Using power automate add the questions into beginning of the answers columns.

----

The use case is that we need to review test results in a manageable way.

In Sharepoint long titles are accepted and you could use the column title as the question, but we can't view this table in Model driven app. 

[shavora]

Forgot to add we get the form responses from Forms. The titles of the questions doesn't follow when you import the response details so the solution seems to need to be a custom one. 

[shavora · Accepted solution]

Solution: 

Create the Forms inside the Teams app in a Team. You will get an Excel file under documents and here you can get the Questions. In your flow set the trigger to when a new forms response has been submitted. Get the response details and then fetch the row in question from the excel-document using the responder's email.

  For my purposes I have decided to store the questions and answers in columns. The questions exceeds the limit on column names in Dataverse, but if your titles are shorter than 41 characters that can be a valid option.

Take the body of the row in the excel file and place in an object.

 I create two more variables. The goal here is to first clean the body of all other properties except the answers.

 The goal here is to replace special characters like the @ sign so that you later can remove this property. For that to work you first need to make the object into a string and replace them. I also replaced how dots shows up in the excel body (_x002e_ into a dot). Gives a cleaner look.

 I decided to place the output in another  variable object, this step might be unnecessary. Next step is to remove all properties that is not a question/answer. If you have special characters in the properties this step won't work, so clean it first.

 This. is the solution I found that converts an object into an array, it works but is not so clean. We delete the curly brackets and we split the string at "." to avoid splitting at unwanted places. Then place it into an array.

In the next step use an apply to each for the array you just created:

 For each item in the array we split it it and take the first/last section to be able to store the question and answer separately.

My choice to store the answers in are in Dataverse, like this: 

 

 Here you can place the questions and answers output. The rest is added stuff that's not necessary.  I also added submission time from the Get a row action. You can also add the email here if needed or any other data from the Get a row action.

Result is:

 This is just test data ^^

As you can see the quotation marks are not so pretty. There is probably a way to clean them even further...

I then used this in canvas power apps:

Use a gallery with flexible height and follow a guide on how to make the resizing work like a charm. Put on the scroller bar to be visible if you have many questions.

In the items property on the gallery I only want to showcase a specific person's results I use this formula on the items property on the gallery:

Sort(Filter('Review product tests',Candidate.Candidates in First(ColCandidate).key_candidatesid),'Submission time',Ascending)

 (First(ColCandidate).key_candidateid is a way to reference a collection column)

[shavora]

Changed to this if people submit more than one respons

 

e: