Answered

Add column from an SP list to a collection, matching on a common column

(0) Share

Report

Posted on by CU03032103-0

I have a collection that's comprised of specific columns from Office365DirectReports. It is defined on the first screen.

I have a sharepoint list that contains all employees who have been evaluated. The list contains a choice column with an evaluation rating for each employee.

I want to append that evaluation column to my collection when the user navigates to the Evaluation Screen in my app. Both the collection and list contain the employee's email, so that can be used to match the rating to the appropriate employee when doing the join.

The goal is to use the updated collection as the source for my gallery. Thank you!

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Verified answer

WarrenBelz 155,240 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @bigfella ,

It should be something like this

AddColumns( Collection, "Eval", LookUp( SPList As _List, _List.Email = Email ).Eval.Value )

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.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
CU03032103-0 37 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the quick response Warren!

I plugged your formula in for my OnVisible portion of my gallery screen. Powerapps seems happy with it, no errors.

However if I navigate to Variables in the left pane and look at the collection, I don't see the column added. I plugged the collection in as the source of the gallery anyway and tried to set the title to ThisItem.Status.Value but Status doesn't appear in the list of options, I can only see the columns that are part of the original collection.

I wasn't familiar with using "as" so I popped that out and made adjustments accordingly, but no that didn't resolve it. I also tried to do =[Mail] but then Powerapps barked that it couldn't compare text to table. Any other thoughts on what to try next? Do I have to create a new collection possibly?

Was this reply helpful? Yes No
WarrenBelz 155,240 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@bigfella ,

The new field should be in the collection and the As statement is only there for disambiguation as your two reference fields have the same name in both data sets. Also try putting the code on a button to test. Can you also please post (in Text) the actual code you ended up using.

Was this reply helpful? Yes No
CU03032103-0 37 on at

Like (0)

Report
Copy link

Link copied!
That's a good trick to note for disambiguation. In my case the names are actually different, I was just trying to simplify things for the sake of the example.

Here's the actual values:
Collection Name=MyReports
Collection Email Column Name = Mail
SP List Name=Goals List
SP Email Column Name = Employee's Email
SP Evaluation Column Name = Status

So my actual formula looks like this:
AddColumns( MyReports, "Status", LookUp( 'Goals List', 'Employee''s Email'=Mail ).Status.Value )

Was this reply helpful? Yes No
WarrenBelz 155,240 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

@bigfella ,

I will revise what I posted before - the new field will not be in the collection, but will be in the gallery.

Was this reply helpful? Yes No
CU03032103-0 37 on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz,
I had been putting your formula into the OnVisible portion of the screen, and then using the MyReports collection as the gallery source when I mentioned it wasn't working. Using your formula in my Gallery Items directly did the trick!

Was this reply helpful? Yes No
WarrenBelz 155,240 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@bigfella ,

Yep - that is what I meant, but probably was not clear enough.

Was this reply helpful? Yes No