Randomise Questions and Storage for Later Retrieval

Question

Folks, I am racking my brain with this one, I am hoping a power apps master can help. I am designing a interview questioning app. This will take 38 questions from 10 different categories, randomly select a question from each category and then randomly select 6 of those. That part is fine.

The tricky bit is that we have to have two interviewers see the same questions in the same order in the app. The current solution is Onselect at the start;

Builds the list of questions into a temporary collection.
Runs an if statement to see if the candidate ID has been previously assigned for the same date -If yes, pull those questions (however they are currently not in the right order and cannot get them into it) -If No, place the generated collection into a share point list for retrieval later.

The reason for the storage is to that randomisation triggers inside the app, but the second assessor, when they log in and enter the candidate ID, will have the same set of questions.

The solutions behaviour at the moment is first hit or miss, some time the collection doesn't apply, and secondly, it will not received the order id from the storage which means we can pull the questions, but they are not in the right order.

The dataset used thus are are:

A static excel list of questions indexed by a unique ID Question_ID, the Question itself and the Value being tested
A temporary collection Temp_Q that eventually becomes Temp_Q_Ordered
A shareppint list Candidate_Q_ID_Storage that contains the Candidate_ID, Session_date, Order_IF, and Question_ID

This is the current code and a link to some dummy data in the same format that I am using. I am happy to give as much as I can if it will help. I am hoping it is an obivous logic problem.

//Clear existing Collection
Clear(Temp_Q);
Clear(Temp_Q_Sorted; 
Clear(Temp_Q_Ordered); 

//Build Collection                                                                                                                                                                                                                                                                                                                                                           
Collect(Temp_Q,FirstN(Shuffle(Filter(Question_Set,Value = "Analyse Critically")),1));                                                         
Collect(Temp_Q,FirstN(Shuffle(Filter(Question_Set,Value = "Collaborative")),1));
Collect(Temp_Q,FirstN(Shuffle(Filter(Question_Set,Value = "Deliver,Support, Inspire")),1));
Collect(Temp_Q,FirstN(Shuffle(Filter(Question_Set,Value = "Emotionally Aware")),1));
Collect(Temp_Q,FirstN(Shuffle(Filter(Question_Set,Value = "Impartiality")),1));
Collect(Temp_Q,FirstN(Shuffle(Filter(Question_Set,Value = "Integrity")),1));
Collect(Temp_Q,FirstN(Shuffle(Filter(Question_Set,Value = "Open-Minded")),1));
Collect(Temp_Q,FirstN(Shuffle(Filter(Question_Set,Value = "Ownership")),1));
Collect(Temp_Q,FirstN(Shuffle(Filter(Question_Set,Value = "Public Service")),1));
Collect(Temp_Q,FirstN(Shuffle(Filter(Question_Set,Value = "Transparency")),1));

//Get list of 6 questions

ClearCollect(                                                                                                                                                                                                                                                                                                                                                     
    Temp_Q_Sorted,
    FirstN(Shuffle(Temp_Q),6));

// Add in the mandatory diversity question
Collect(Temp_Q_Sorted,FirstN(Shuffle(Diversity),1));                                                                                                                                                                                      

// Create row number index
ForAll (Temp_Q_Sorted,                                                                                                                                                                                                                                                                                                                               
   Collect(Temp_Q_Ordered,
     Last(FirstN(AddColumns(Temp_Q_Sorted, "RowNumber", CountRows(Temp_Q_Ordered)+1),
        CountRows(Temp_Q_Ordered)+1
         )
     )
   )
)

//Where things get iffy (pardon the pun)

If(And(Assessing_Text.Text in Candidate_Q_ID_Storage.Title, Today() in Candidate_Q_ID_Storage.Session_Date),
ClearCollect(Temp_Q_Ordered,
Filter(Question_Set,
     Question_ID in
      Filter
      (Candidate_Q_ID_Storage,
          Assessing_Text.Text =  Title && Today() = Session_Date
       ).Question_ID));

// Where is where I cannot get the order to stay the same, I have tried to call the column from retrieval into the new set, but to no avail.

       ,
ForAll(Temp_Q_Ordered,Patch(Candidate_Q_ID_Storage, Defaults(Candidate_Q_ID_Storage),
{Title: Assessing_Text.Text,
Session_Date: Today(),
Question_ID:Question_ID,
Order_ID:RowNumber}))
)

user_15411523 · Answer

EDIT - The actual problem with your code is that ForAll in PowerApps doesn't run in a consistent order. In ForAll essentially all the rows run at once and they tend to finish in a random order. All the ways around this are pretty hacky and tend to involve timers.

The ideal solution to this problem would be if the shuffle formula had a seed input. A seed allows a random number generator to give the same results for the same input (in your case, the candidate ID.)

Unforutnately, PowerApps doesn't support seeds. However, instead you could use a very simple hash. A simple hash will give a randomish output that is always the same for a given input

Lets say you have a list of questions in a single category, and our candidate ID is 333. We could generate some ids to use as input for our hashes by adding the rownumber to the candidate id eg

Question	QuestionKey
1	334
2	335
3	336

You could scramble it up more by multiplying it by the candidate number, dividing it, whatever.

We could then run a simple hash formula on the QuestionKey, which would transform the QuestionKeys into different numbers in a way that is not exactly random, but good enough for our purposes

Question	HashResult
1	888
2	6
3	24

Then sort your table by the hash result and pick the top one. Because you are basing your numbering off the candidateID, the interviewers will always see the same list of questions, and because the hash gives somewhat randomish answers, the order will tend to be unique for each id.

A simple hash function is something like Cormen's multiplication method

HashResult = Mod(A*QuestionKey,1)*m

Where A is a number between 1 and 0 (eg 0.884) and m is a largish power of 2 (eg 8192)

Admittedly, this answer isn't quite addressing your setup, but I think it's a lot simpler and would reduce the mucking around with Sharepoint lists and trying to store values.

Randomise Questions and Storage for Later Retrieval

1 Answers