I am working on a solution for one of our clients where I need to merge a collection with transpose of a row in another collection as explained below:
The two tables on the left represent the two collections that I already have, storing the question data and another one storing the responses to those questions by each user. When admin users log in to the app, they see a gallery of responses (just a title, not the detailed responses) and when they click on a title, the detailed responses from collection 2 will need to be displayed in the format shown in the output collection above. Let me know if this will be feasible or will the solution needs to be designed differently. Thanks in advance.
Solved! Go to Solution.
If Collection2 is a data source for admin gallery (lets call adminGallery):
adminGallery.OnSelect =
Clear(colCollection1_2);
ForAll(colCollection1 As ThisQues,
Collect(colCollection1_2,
{
'QuesID': ThisQues.QuesID,
'Ques': ThisQues.Ques,
'Response': Switch(ThisQues.QuesID,
"Q1", ThisItem.Q1,
"Q2", ThisItem.Q2,
"Q3", ThisItem.Q3,
"Q4", ThisItem.Q4,
"Q5", ThisItem.Q5
)})
)
create a new gallery (lets call resultGallery):
resultGallery.Items = colCollection1_2
If Collection2 is a data source for admin gallery (lets call adminGallery):
adminGallery.OnSelect =
Clear(colCollection1_2);
ForAll(colCollection1 As ThisQues,
Collect(colCollection1_2,
{
'QuesID': ThisQues.QuesID,
'Ques': ThisQues.Ques,
'Response': Switch(ThisQues.QuesID,
"Q1", ThisItem.Q1,
"Q2", ThisItem.Q2,
"Q3", ThisItem.Q3,
"Q4", ThisItem.Q4,
"Q5", ThisItem.Q5
)})
)
create a new gallery (lets call resultGallery):
resultGallery.Items = colCollection1_2
Thanks for your help @Alex_10
So, this means whenever a new question is added to the data source, my app will need to be updated in the Switch part of the formula, correct? Is there a way to make that automatic?
yes, you are right.
to make it in auto mode, you need to redesign COllection2 - each question answer has to be a separate column like:
PersonID QuesID Answer
---------------------------------------
John_Doe_ID Q1 yes
John_Doe_ID Q2 no
Jane_Doe_ID Q1 no
@Alex_10 Thanks again So, which one of these designs (top or the bottom one) do you think will be better:
And if I go with the second design that you suggested (bottom), will the following formula work to merge the two collections and get the desired output as per my requirement:
ClearCollect(newCol, Addcolumns(Col2,"ques",lookup(col1,QuesID=col2[@Question]).Ques))
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
User | Count |
---|---|
191 | |
45 | |
45 | |
38 | |
36 |
User | Count |
---|---|
261 | |
83 | |
81 | |
69 | |
69 |