Create a Collection from multiple related SharePoint lists

(0) Share

Report

Posted on by rameshmukka

1,117

I have SharePoint lists as shown below.

List A

ID	Job Number (Text)	Site Address (Text)	Supervisor (Text)
44	C20-65	PRINCE OF WALES AV, MILL PARK REHAB	Nick

List B

ID	Job ID (number column reference to ID column of List A)	Machinery (Multi Select Choice Column)	Crew Member(Text)
1	44	DPU, CIVIL Truck - 9	David
2	44	BOBCAT-6, BOBCAT-5	Benjamin

List C

ID	Job ID (number column reference to ID column of List A)	Machinery (Multi Select Choice Column)	Crew Member (Text)
1	44	Tipping-Truck-5, Tipping-Truck-6	Ethan

My resultant table or collection should look like below

Job Number	Site Address	Supervisor	Machinery List B	Crew Member List B	Machinery List C	Crew Member List C
C20-65	PRINCE OF WALES AV, MILL PARK REHAB	Nick	DPU, CIVIL Truck - 9	David	Tipping-Truck-5, Tipping-Truck-6	Ethan
C20-65	PRINCE OF WALES AV, MILL PARK REHAB	Nick	BOBCAT-6, BOBCAT-5	Benjamin

Can someone help in writing a collection function to get me the resultant table. I initially tried using Power Automate to get into a csv but the format of table is going completely wrong with my flow. I though achieving this in PowerApps is bit straight.

Categories:

Building Power Apps

I have the same question (0)

All responses (22)

Answers (1)

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @RameshMukka

You would use the AddColumns() function to lookup the items from another list. Something like

ClearCollect( mycollection, AddColumns( ListA,"Machinery",Lookup( ListB, JobID=ID, Machinery ) ) )

Was this reply helpful? Yes No
rameshmukka 1,117 on at

Like (0)

Report
Copy link

Link copied!
@Drrickryp I tried your formula like below but its throwing error at JobID = ID.

ClearCollect( colJobitems, AddColumns( ShowColumns( 'Site Startup', "ID", "JobNo", "JobName", "SiteAddress", "Weather", "CrewSupervisor" ), "Labour", LookUp( 'Site Labour', JobID = ID ).CrewMembers ) )

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

It assumes the foreign key is jobid but it could be whatever you have chosen as the link field between the tables.

Was this reply helpful? Yes No
rameshmukka 1,117 on at

Like (0)

Report
Copy link

Link copied!

@Drrickryp Yes JobID is the foreign key here.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Lookup('Labor Site',ID=JobID, crewmembers) would show a field value for crewmembers.

The order in the equation is table, primary key = foreign key ,fieldname

Was this reply helpful? Yes No
rameshmukka 1,117 on at

Like (0)

Report
Copy link

Link copied!

@Drrickryp What I have in list 'Site Labour' is foreign key JoBID. So comparing it with ID (primary key) of list 'Site Startup'. I would like to fetch column CrewMembers (text column) when this condition is met in LookUp function. But it could return 1 or more records as I have 1 or more matching records with my primary key. Do you think thats the problem? I hardly see any other issue.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@RameshMukka

Are you saying that the relationship between the two lists is one to many? ie. one Job to many SiteLabor. If this is the case and you want to show all the crew members on a particular job. I would approach the problem a different way.

Was this reply helpful? Yes No
rameshmukka 1,117 on at

Like (0)

Report
Copy link

Link copied!

@Drrickryp You are right! One job to many SiteLabor. Also the same case with my List C that I mentioned in my post. I actually have to join similar lists into a single collection. Just wanted a path for one list so I can replicate the same with other lists.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@RameshMukka

The best way to work with this kind of data, where you want to show all of the crew for a single job is to use the GroupBy() function on the Many side and to show the results with a Gallery/Subgallery. In the example below a table of fake contacts is grouped on state and the rest of the data is shown in the subgallery. In your case, you would group the crew members by "jobID" and show the names in the subgallery. You would show additional information about the Job using labels with a text property of Lookup(Job, ID=jobID, siteaddress), etc for items you want to show related to the Job itself in the outer gallery. That is the way you can show the items you selected by ShowColumns() in your original post.

Was this reply helpful? Yes No
rameshmukka 1,117 on at

Like (0)

Report
Copy link

Link copied!

@Drrickryp The actual target is to get the csv from collection by sending it to Power Automate flow. Showing on gallery was never part of my plan 😞.

Was this reply helpful? Yes No