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

[bigfella]

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!

[WarrenBelz · Accepted solution]

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

[bigfella]

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?

[WarrenBelz]

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

[bigfella]

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
)

[WarrenBelz]

@bigfella ,

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

[bigfella]

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

[WarrenBelz]

@bigfella ,

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