Skip to main content

Notifications

Power Apps - Building Power Apps
Answered

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

(0) ShareShare
ReportReport
Posted on by 37

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

bigfella_0-1704399440312.png

 

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.

bigfella_1-1704399466119.png

 

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.

bigfella_2-1704399517568.png

 

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

Categories:
  • WarrenBelz Profile Picture
    WarrenBelz 145,422 on at
    Re: Add column from an SP list to a collection, matching on a common column

    @bigfella ,

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

  • bigfella Profile Picture
    bigfella 37 on at
    Re: Add column from an SP list to a collection, matching on a common column

    @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 Profile Picture
    WarrenBelz 145,422 on at
    Re: Add column from an SP list to a collection, matching on a common column

    @bigfella ,

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

  • bigfella Profile Picture
    bigfella 37 on at
    Re: Add column from an SP list to a collection, matching on a common column

    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 Profile Picture
    WarrenBelz 145,422 on at
    Re: Add column from an SP list to a collection, matching on a common column

    @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 Profile Picture
    bigfella 37 on at
    Re: Add column from an SP list to a collection, matching on a common column

    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?

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 145,422 on at
    Re: Add column from an SP list to a collection, matching on a common column

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,422

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,711

Leaderboard