Answered

Pull data from Sharepoint List LookUp column with multiple value

Like (0) Share

Report

Posted on 27 Feb 2024 08:39:25 by BacKan

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:

Name	Surname	Substitute
Name1	Surname1
Name2	Surname2	Surname3
Name3	Surname3
Name4	Surname4	Surname1, 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:

General Questions

All responses (3)

Answers (1)

Verified answer

BacKan 25 on 29 Feb 2024 at 11:36:52

Like (0)

Report
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 25 on 27 Feb 2024 at 13:29:37

Like (0)

Report

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 606 Super User 2025 Season 1 on 27 Feb 2024 at 10:54:04

Like (0)

Report
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
)