Skip to main content

Notifications

Community site session details

Community site session details

Session Id : XDsWivIRp6ZeKCk+40feMy
Power Apps - Building Power Apps
Answered

Pull data from Sharepoint List LookUp column with multiple value

Like (0) ShareShare
ReportReport
Posted on 27 Feb 2024 08:39:25 by 25

As in the topic, I need to retrieve data from a lookup sharepoint list column that can have from zero to three selected values.

I want to create a collection from this data so that I can then create a gallery based on it.

 

SharepointList:

NameSurnameSubstitute
Name1Surname1 
Name2Surname2Surname3
Name3Surname3 
Name4Surname4Surname1, Surname3

 

Lookup column is relative to the same Sharepoint List.

 

For now I could not even filter my sharepoint list to show recods with choosed substitute, but I was able to pull out all values from Substitute column into gallery (per record)

 

What I want to achieve:

Distinct collection of Substitute column - [Surname1, Surname3]

Filtered list of people who choosed their substitute.

Categories:
  • Verified answer
    BacKan Profile Picture
    25 on 29 Feb 2024 at 11:36:52
    Re: Pull data from Sharepoint List LookUp column with multiple value

    I found the solution. I used ForAll function to append my values to the collection.

     

    With code below I was able to create the list of people who choosed their substitute.

     

     

    ClearCollect(varSubstitute;Blank());;
    ForAll(
     Filter('SHAREPOINT_LIST_NAME';YOUR_FILTERS);
     If(CountRows(substitute #column name here) > 0;Collect(varSubstitute;{NAME:ThisRecord.first_name;SURNAME:ThisRecord.surname;ID:Index(substitute;1).Id}));;
     If(CountRows(substitute) > 1;Collect(varSuccessors;{NAME:ThisRecord.first_name;SURNAME:ThisRecord.surname;ID:Index(substitute;2).Id}));;
     If(CountRows(substitute) > 2;Collect(varSuccessors;{NAME:ThisRecord.first_name;SURNAME:ThisRecord.surname;ID:Index(substitute;3).Id}));;
    )

     


    To achieve my second goal I could easily collect ID only and then create another collection with distinct values, but this solution gives me more flexibility.

  • BacKan Profile Picture
    25 on 27 Feb 2024 at 13:29:37
    Re: Pull data from Sharepoint List LookUp column with multiple value

    Nothing from this works in my app. Everything returns empty list.

     

    It may be important - my source list has a lot of records, about 2k.

  • NPPlatform Profile Picture
    606 Super User 2025 Season 1 on 27 Feb 2024 at 10:54:04
    Re: Pull data from Sharepoint List LookUp column with multiple value

    Hi @BacKan , you can create a distinct collection of the Substitute column with the following formula:

     

    ClearCollect(
    SubstituteDistinct,
    Distinct(
    Filter(
    ShowColumns(
    Ungroup(
    SharePointListName,
    "Substitute"
    ),
    "Value"
    ),
    Value <> Blank()
    ),
    Value
    )
    )

    You can create a list of all people with a substitute with the following formula:

    Filter(
    Ungroup(
    SharePointListName,
    "Substitute"
    ),
    Value in SubstituteDistinct
    )


    Edit: if your end goal is only to see which people have chosen a substitute you can do this with one formula:

     

    Distinct(
    Filter(
    Ungroup(
    SharePointListName,
    "Substitute"
    ),
    "Value",
    Value <> Blank()
    ),
    Surname
    )

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

Thomas Rice – Community Spotlight

We are honored to recognize Thomas Rice as our March 2025 Community…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

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

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,508 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,440 Most Valuable Professional

Leaderboard
Loading started
Loading complete