Answered

Create a collection with all of the records of SP List A and the matching values added from SP List B

(0) Share

Report

Posted on by ScottRMeredith

141

I have a SP List A (InserviceUnits) that only has a Title field in it, set to unique. It is a Single Line of Text (SLT). It stores a list of assignments for a combo box in my app of all possible assignments a person can work (150 records). I have a second SP List B (Unit Seniority Roster) with a SLT field name Assignment that is the stored value of the combo box when a new record is Patched to the list (also set to unique items). SP List B also has a person field for the name of the employee (130 records), among a few other un-needed fields for this purpose.

I want to create a collection so I can send it off to Automate to create an Excel file and email to a user group. I want all the records of the assignments from SP List A and when I get a match with an employee to have the display name of the employee in a second column. So the output collection would be a column "Assignments" (150 rows) and a column "EmployeeName" (120 rows) with 30 values in EmployeeName being blank. I can do it easily in Access, but would like to stay all in the Power Platform. I have spent the last 3 hours trying multiple variations of solutions I have found on these forums and articles and youtube videos. Please unscramble my brain.

Here is an example of how I would like the collection to be when the two lists are combined.

Thank you

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

WarrenBelz 156,566 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @ScottRMeredith ,

Assuming the second list also has the data in the Title field

ClearCollect( colMyCollection, InServiceUnits, Filter( 'Unit Seniority Roster', !(Title in InServiceUnits.Title) ) )

Note you will get a Delegation warning on this.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps
ScottRMeredith 141 on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry, but I don't see how this will show the display the Employee Name (display name), and all the records from the InserviceUnits List. If it does, the line with !(title in InServiceUnits.Title), confuses me. Here are my two lists in SP. SLT = Single Line Text. Thank you
Verified answer

WarrenBelz 156,566 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @ScottRMeredith ,

That is why I referenced the Title column assumption - your post was not clear on the column name presently in the second list. Also, I think this needs to be the other way around to my previous code (all from 'Unit Seniority Roster' and the missing ones from InServiceUnits). Try this

ClearCollect( colMyCollection, 'Unit Seniority Roster', Filter( RenameColumns( InServiceUnits, "Title", "Assignment" ), !(Assignment in 'Unit Seniority Roster'.Assignment) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps
ScottRMeredith 141 on at

Like
a
(0)

Report
Copy link

Link copied!

That did it....Thank you so much.