Answered

PowerApps: Join multiple SharePoint lists

(0) Share

Report

Posted on by ildeloge

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

eka24 20,923 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ildeloge 12 on at

Like (0)

Report

Please find attached the database model

DatabaseModel.PNG

Was this reply helpful? Yes No
Verified answer

v-yutliu-msft on at

Like (0)

Report
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:

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,

Was this reply helpful? Yes No
ildeloge 12 on at

Like (0)

Report

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

Was this reply helpful? Yes No
v-yutliu-msft on at

Like (0)

Report

Hi @ildeloge ,

Ok...

So have your issue been solved?

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

Thanks!

Best regards,

Was this reply helpful? Yes No
ildeloge 12 on at

Like (0)

Report

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.

Was this reply helpful? Yes No