cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ildeloge
Frequent Visitor

PowerApps: Join multiple SharePoint lists

Hello,

In PowerApps I created a canvas app to fill in 7 SharePoint lists, all linked via lookup columns, in a 1 to many relation. Is there a way In PowerApps to flatten that parent-child hierarchy and display all the fields from all the lists in only 1 table and finally to create filters on that table?

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

Hi @ildeloge ,

Do you want to display all related items of 6 tables based on 1 tables?

If so, I suggest you use a gallery to display table1, insert 6 listboxes(or galleries)  inside the gallery to display other tables.

Could you tell me whether these lookup fields allow multiple choice? I assume that no.

I've made a similar test for your reference:

1)set gallery1's Items:

table1

2)insert 6 list boxes inside the gallery:

714.PNG

 

 3)set listbox1's Items:

Filter(table2,table1ID.Value=ThisItem.ID)

set listbox2's Items:

 

Filter(table3,table2ID.Value in 
              Filter(table2,table1ID.Value=ThisItem.ID).ID
       )

 

set listbox3's Items:

 

Filter(table4,table3ID.Value in 
              Filter(table3,table2ID.Value in 
                            Filter(table2,table1ID.Value=ThisItem.ID).ID
                     ).ID
)

 

set listbox4's Items:

 

Filter(table5,table4ID.Value in 
              Filter(table4,table3ID.Value in 
                     Filter(table3,table2ID.Value in 
                            Filter(table2,table1ID.Value=ThisItem.ID).ID
                            ).ID
                      ).ID
         )

 

set listbox5's Items:

 

Filter(table6,table5ID.Value in
              Filter(table5,table4ID.Value in 
                     Filter(table4,table3ID.Value in 
                             Filter(table3,table2ID.Value in 
                                    Filter(table2,table1ID.Value=ThisItem.ID).ID
                                    ).ID
                             ).ID
                      ).ID
)

 

set listbox6's Items:

 

Filter(table7,table6ID.Value in
              Filter(table6,table5ID.Value in
                     Filter(table5,table4ID.Value in 
                            Filter(table4,table3ID.Value in 
                                   Filter(table3,table2ID.Value in 
                                           Filter(table2,table1ID.Value=ThisItem.ID).ID
                                         ).ID
                                   ).ID
                            ).ID
                        ).ID
)

 

Then, listbox1 will only display related items in table2.

 

 

Listbox2 will only display related items in table3.

Listbox3 will only display related items in table4.

Listbox4 will only display related items in table5.

Listbox5 will only display related items in table6.

Listbox6 will only display related items in table7.

 

 

Best regards,

 

 

 

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
eka24
Super User III
Super User III

You may have to display sample of each table.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

ildeloge
Frequent Visitor

Please find attached the database model

v-yutliu-msft
Community Support
Community Support

Hi @ildeloge ,

Do you want to display all related items of 6 tables based on 1 tables?

If so, I suggest you use a gallery to display table1, insert 6 listboxes(or galleries)  inside the gallery to display other tables.

Could you tell me whether these lookup fields allow multiple choice? I assume that no.

I've made a similar test for your reference:

1)set gallery1's Items:

table1

2)insert 6 list boxes inside the gallery:

714.PNG

 

 3)set listbox1's Items:

Filter(table2,table1ID.Value=ThisItem.ID)

set listbox2's Items:

 

Filter(table3,table2ID.Value in 
              Filter(table2,table1ID.Value=ThisItem.ID).ID
       )

 

set listbox3's Items:

 

Filter(table4,table3ID.Value in 
              Filter(table3,table2ID.Value in 
                            Filter(table2,table1ID.Value=ThisItem.ID).ID
                     ).ID
)

 

set listbox4's Items:

 

Filter(table5,table4ID.Value in 
              Filter(table4,table3ID.Value in 
                     Filter(table3,table2ID.Value in 
                            Filter(table2,table1ID.Value=ThisItem.ID).ID
                            ).ID
                      ).ID
         )

 

set listbox5's Items:

 

Filter(table6,table5ID.Value in
              Filter(table5,table4ID.Value in 
                     Filter(table4,table3ID.Value in 
                             Filter(table3,table2ID.Value in 
                                    Filter(table2,table1ID.Value=ThisItem.ID).ID
                                    ).ID
                             ).ID
                      ).ID
)

 

set listbox6's Items:

 

Filter(table7,table6ID.Value in
              Filter(table6,table5ID.Value in
                     Filter(table5,table4ID.Value in 
                            Filter(table4,table3ID.Value in 
                                   Filter(table3,table2ID.Value in 
                                           Filter(table2,table1ID.Value=ThisItem.ID).ID
                                         ).ID
                                   ).ID
                            ).ID
                        ).ID
)

 

Then, listbox1 will only display related items in table2.

 

 

Listbox2 will only display related items in table3.

Listbox3 will only display related items in table4.

Listbox4 will only display related items in table5.

Listbox5 will only display related items in table6.

Listbox6 will only display related items in table7.

 

 

Best regards,

 

 

 

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hello @v-yutliu-msft 

Thank you for your reply! What I would like is to have only 1 table where I add the columns from all the 7 SharePoint lists.

I was thinking I can maybe do that using ClearCollect, AddColumns and Lookup functions. Example:

ClearCollect( Tb6Collection, AddColumns( 'Tb7', "ED", LookUp( 'Tb6', ID='Tb7l'[@Tb6ID].Id) ) ) ;

With this solution I can only add 1 lookup, not more(for ex. to table 5, and table 4 etc).

 

Hi @ildeloge ,

Ok...

So have your issue been solved?

If yes, could you change your issue's status to "Answered"?

Thanks!

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

I decided to use Spotfire in order to display the results as I wanted (with all the joints). Since with PowerApps this was not possible. 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (40,663)