Transposing and Merging Collections

[PowerSoumyo]

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.

[Alex_10 · Accepted solution]

@PowerSoumyo 

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

[PowerSoumyo]

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?

[Alex_10]

@PowerSoumyo 

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

[PowerSoumyo]

@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))

[Alex_10]

@PowerSoumyo 

with the bottom collection that code should work in general.