Answered

Help with merging collections

(0) Share

Report

Posted on by Moores35TFL

I have a canvas app and am currently using SharePoint as my datasource. The original intention was to use Dataverse or another Database system, however due to the pandemic that solution is currently on hold.

I have 3 main SharePoint Lists, a 1-1 relationship exists between all 3 using Item No as the key.

The lists currently contain 2650 rows, with the potential to grow.

I need a gallery that holds fields from all 3 lists and allow the user to also filter on some of these fields, which of course are spread across the 3 lists.

Currently I am creating 3 collections for each list using @WarrenBelz code found here

I then create a 4th collection by using the AddColumns function to my first collection.

This is obviously quite slow , so was wondering if there is a more efficient way of performing this action. We do not want to revert toa single list with all fields as we still intend to migrate over to the dataverse at some point and various other integrations are happening elsewhere which use just one of these lists.

Sample lists and gallery is below

List 1

Item No	Description	Required	Status	Type
DMO0100	Demo Programme	True	Live	Programme
DMO0110	Demo Project	True	Live	Project
DMO0200	Another Programme	True	Live	Programme
DMO0220	Another Project	False	Withdrawn	Project

List 2

Item No	Delivery Area	Function	On Site	Project Manager
DMO0100	Civils Engineering	Engineering	Ye	Bob
DMO0110	Civils Engineering	Engineering	Yes	Bill
DMO0200	West Side	Finance	No	Jill
DMO0220	West Side	Commercial	No	Jane

List 3

Item No	Sub-Contracted	SC Detail
DMO0100	No
DMO0110	Yes – In House	Bobs Team
DMO0200	Yes - Contractor	Safety r Us
DMO0220	Yes - Contractor	We Dig Holes

Gallery

Item No	Description	required	Status	Type	Delivery Area	Function	On Site	Project Manager	Sub-Contracted	SC Detail
DMO0100	Demo Programme	True	Live	Programme	Civils Engineering	Engineering	Yes	Bob	No
DMO0110	Demo Project	True	Live	Project	Civils Engineering	Engineering	Yes	Bill	Yes – In House	Bobs Team
DMO0200	Another Programme	True	Live	Programme	West Side	Finance	No	Jill	Yes - Contractor	Safety r Us
DMO0220	Another Project	False	Withdrawn	Project	West Side	Commercial	No	Jane	Yes - Contractor	We Dig Holes

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@Moores35TFL ,

Set the items property of the gallery to list 1 and use Lookup() to obtain text values for the labels from the other lists.

Was this reply helpful? Yes No
Moores35TFL 26 on at

Like (0)

Report
Copy link

Link copied!

Thanks @Drrickryp If all I was doing was displaying all the dats then that would be fine, however I need to have filters on my screen to filter the gallery based upon various fields that span al 3 lists,. What I have in the lists above is just an example of the fields, there are actually a bunch of additional fields in List 2 for example (Sponsor, Project Controls Manager, Lead Engineer,for example)

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Moores35TFL :

I've made a test for your reference:

I assume there are three tables:

ClearCollect( 'List 1', {'item No':"DMO0100",Description:"Demo Programme"}, {'item No':"DMO0110",Description:"Demo Project"}, {'item No':"DMO0200",Description:"Demo Project"} ); ClearCollect( 'List 2', {'item No':"DMO0100",'Delivery Area':"Civils Engineering",Function:"Engineering"}, {'item No':"DMO0110",'Delivery Area':"Civils Engineering",Function:"Engineering"}, {'item No':"DMO0200",'Delivery Area':"West Side",Function:"Finance"} ); ClearCollect( 'List 3', {'item No':"DMO0100",'Sub-Contracted':"No"}, {'item No':"DMO0110",'Sub-Contracted':" Yes – In House"}, {'item No':"DMO0200",'Sub-Contracted':" Yes - Contractor"} )

Merging them using this formula

ForAll( 'List 1', Patch( ThisRecord, LookUp('List 2','List 2'[@'item No']='List 1'[@'item No']), LookUp('List 3','List 3'[@'item No']='List 1'[@'item No']) ) )

Best Regards,

Bof

Was this reply helpful? Yes No
Moores35TFL 26 on at

Like (0)

Report
Copy link

Link copied!

Thank you, this works well for what I need.

Was this reply helpful? Yes No