Answered

Display Related SharePoint MultiSelect Columns in Nested Gallary

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have a Sharepoint Table1 where userdata gets stored. There is 1 row per request and each request has an ID, Name, Approved column as well as having SharePoint multiselect "sub_IDs", "Questions" and "Answers" columns

So my table ends up looking like:

ID	Name	sub_IDs	Questions	Answers	Approved
1	Foo	{s1;s2;s3}	{Q1;Q2;Q3}	{A1;A2;A3}	Waiting
2	Bar	{s1}	{Q1}	{A1}	Approved

(where the {...} is a table with a single column called "Value", I believe that this is forced by SharePoint and I have no control over this)

In Gallery1.Items = Table1, then I nest another gallery (Gallery2) inside Gallery1 and I want to display the "sub_IDs" and "Questions" in a label and the "Answers" in a text box that be updated.

I cannot even figure out how to get Gallery2 to display correctly though,

I tried:

AddColumns(ThisItem.subIDs, "_Question", LookUp(ThisItem.Questions, true, Value), "_Answer, LookUp(ThisItem.Answers, true, Value))

But while the "sub_IDs" display correctly, the Questions and Answers end up being the first row of the subtables repeating (Q1 and A1 when using ID=1 as an example)

I am guessing that there is some fancy way to use AddColumns and ForAll to get this to work but it escapes me...

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Could you tell me how do you distinguish which question and which answer is related to which sub_IDs?

For example:

In the first item, the sub_IDs value is : {s1;s2;s3}

Question value is {Q1;Q2;Q3}

Answers value is: {A1;A2;A3}

Is sub_IDs s1 related to Q1 abd A1?

Is sub_IDs s2 related to Q1 abd A2?

Is sub_IDs s3 related to Q1 abd A3?

The multiple choices only has one Value Column, there's no other field that could used to link other fields.

Since three columns do not have relationship with each other, I could give you two kinds of advice:
1)use lookup field instead of choice field

Create another list to express the relationship between these three fields.

Then by using lookup field, you could link them togther.

2)use three nested galleries to display these three fields separtly

For example:

gallery1's Items:

listname

gallery2's Items: (nested gallery)

ThisItem.sub_IDs

gallery3's Items:(nested gallery)

ThisItem.Questions

gallery4's Items:(nested gallery)

ThisItem.Answers

insert a textinput inside gallery4. set its Default: ThisItem.Value

In gallery1, set one button's OnSelect:

Patch(listname,ThisItem,{Answers:RenameColumns(ShowColumns(AddColumns(Gallery4.AllItems,"Value1",TextInput1.Text),"Value1"),"Value1","Value")})

//To update Answers column.

Best regards,
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@v-yutliu-msft Thanks for the support!

The IDs, Questions, and Answers are all related to each other by Row within their tables (so row 1 of sub_IDs table is related to row 1 of Question table is related to row 1 of Answer table) - as you seem to have guessed from the rest of your post.

I was hoping there was a way, knowing the above, that everything could be automatically collected into 1 gallery somehow in order to avoid your exact method 2 below since PowerApps is SO finicky with nested Galleries.

To that end how would you implement your method 1? The issue being that Answer can literally be someone writing in a number or multiple selected items from a combobox depending on the question... Is there a way to add a row number to each table using AddColumns and then do a lookup by row number to get everything into 1? Maybe that isn't worth it...
Verified answer

v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Actually, I do not quite suggest you try the second solution.

Yes, you could add a number field for each choice column. But you need to at only add the number column but also need to relate these three choice column together. This is quite complex.

Here's a doc about numbering rows , but not for nested table.

Maybe you could have a try:

http://powerappsguide.com/blog/post/generating-row-numbers

I more suggest you the first solution.

Let me explain more detailed:
1)create another list, with these fields:

sub_IDs（text type) Questions（text type) Answers（text type)

data looks like this:

s1;Q1;A1

s2;Q2;A2

....

2)in current list, insert three lookup fields

sub_IDs（look up to sub_IDs in list1) Questions（look up to Questions in list1) Answers（look up to Questions in list1)

Then these fields will have relationship based on the fields in list1.

The nested gallery's Items could be like this:

AddColumns(ThisItem.subIDs, "_Question", LookUp(ThisItem.Questions, Id=ThisItem.subIDs[@Id],Value),"_Answer", LookUp(ThisItem.Answers, Id=ThisItem.subIDs[@Id],Value) )

Best regards,