Answered

Pull data from Sharepoint List LookUp column with multiple value

(0) Share

Report

Posted on 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:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

NPPlatform 606 Moderator on at

Like (0)

Report
Copy link

Link copied!
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
)

Was this reply helpful? Yes No
BacKan 29 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

BacKan 29 on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No