I have SharePoint lists as shown below.
|ID||Job Number (Text)||Site Address (Text)||Supervisor (Text)|
|44||C20-65||PRINCE OF WALES AV, MILL PARK REHAB||Nick|
|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|
|ID||Job ID (number column reference to ID column of List A)||Machinery (Multi Select Choice Column)||Crew Member (Text)|
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.
Solved! Go to Solution.
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.
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)
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.
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.
I hope that helps.
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 ) ) )
@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 ) )
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
@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.
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.
@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.
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.
Check out the News & Announcements to learn more.
Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!
Check out the new Power Platform Community Connections gallery!
Congratulations, the new Super User Season 2 for 2021 has started!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.