cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RameshMukka
Continued Contributor
Continued Contributor

Create a Collection from multiple related SharePoint lists

I have SharePoint lists as shown below.

 

List A

IDJob Number (Text)Site Address (Text)Supervisor (Text)
44C20-65PRINCE OF WALES AV, MILL PARK REHABNick

 

List B

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

 

List C

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

 

My resultant table or collection should look like below

Job Number Site AddressSupervisorMachinery List BCrew Member List BMachinery List CCrew Member List C
C20-65PRINCE OF WALES AV, MILL PARK REHABNickDPU, CIVIL Truck - 9DavidTipping-Truck-5, Tipping-Truck-6Ethan
C20-65PRINCE OF WALES AV, MILL PARK REHABNickBOBCAT-6, BOBCAT-5Benjamin  

 

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
Dual Super User III

Sorry, I posted a response a couple days ago with a full set of screenshots from my sample.  Not sure why it isn't here.  I must have forgotten to hit the button when I finished.  So lets try again.

1) Start with a Power Apps trigger, initialize 4 variables (1 array and 3 strings) to hold values while you build the array that will be converted to a CSV, then get all the items from List A.

image.png

2) Process each item in List A in a Loop.  In the Loop Get the item from List C that matches the item being processed from List A.  Save the Crew Member to a variable and process the multiselect machinery field to store those names in a string. Now get the items from List B that match the item in list A and process them.  (More on that in the next screenshot)image.png

3) Inside the loop that processes the List B items do the following.  Clear the List B machinery variable and use a Loop to create a string containing all the List B machinery from the record, Now append the entry you've built using variables to the Array.  Then clear the List C variables so they only show up in the first List A item they are associated with. Loop and process the rest of the List B entries.

image.png

4) Once you exit the List A Loop and everything has been recorded in the array you can now create a CSV out of the Array.

image.png

I hope that helps.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

22 REPLIES 22
Drrickryp
Super User
Super User

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
                                                         )
                             )
)
RameshMukka
Continued Contributor
Continued Contributor

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

 

RameshMukka_0-1609559808888.png

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

RameshMukka
Continued Contributor
Continued Contributor

@Drrickryp Yes JobID is the foreign key here.

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

RameshMukka
Continued Contributor
Continued Contributor

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

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

RameshMukka
Continued Contributor
Continued Contributor

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

@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.  _1.png

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,180)