Collection from Multiple Person column

(0) Share

Report

Posted on by mikzee11

Collection from Multiple Person colum

Hey everyone!

I've been stumped and sorta figuring out some roundabout ways to accomplish this but I thought I'd ask here for some help and advice! Anything is appreciated and if you need clarification please ask!

So basically, I have a SharePoint list that has a multiple select person column called "PSLead".

All I want to do is create a collection based on PSLead that gets all of the DisplayName values and puts them into a column called PSName.

The trouble is that because it is a multiple person column, I am having trouble doing this for all the records in each row, when multiple people are selected.

So far I've only been able to "extract" the first DisplayName, but if the row has multiple people selected I can't extract any susbequent DisplayNames.

What I am trying to accomplish is essentially "expand to new rows" in PowerBi.

Any ideas?? I hope I explained well. Thanks everyone!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

AaronKnox 514 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi, @mikzee11 ,

The SharePoint list is a table, but so is each record in the multiple person column. That is a table of user records. So we have tables within a table.

The solution is to evaluate each record, i.e. 'loop through', the SharePoint list, the outer table if you will, and then loop through each inner table record, the table of user records, collecting as we go.

The way we evaluate a table is the ForAll() function, which returns a table based on our formula. We have to fetch each DisplayName, with the inner ForAll(), then continue to the next record with the outer ForAll().

That formula is below, however there is a problem:

ForAll( 'SharePointList' As _OuterLoop, ForAll( _OuterLoop.PSLead As _InnerLoop, Collect( colPSLeads, {PSName: _InnerLoop.DisplayName} ) ) )

This will work but will generate an ugly list of unsorted names and duplicates. To fix that, wrap the collection with a Sort( Distinct() or GroupBy() )

Sort(Distinct(colPSLeads,PSName),ThisRecord.Value)
Sort( GroupBy(colPSLeads,"PSName","_myGroup"),PSName, SortOrder.Ascending )

Hope this helps!

Aaron

Was this reply helpful? Yes No
mikzee11 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @AaronKnox

Wow! Thanks for your quick response!

I had never seen _Innerloop or _Outerloop before - so this was very helpful!

And it worked as well! Extracting all the DisplayNames from my column into a list!
Thank you!

I have an additional question.
My PSLead column may have some blank values in some instances. Is it possible to output "Unassigned" to the blank values?

The current code you supplied skips the blank values (which is fine and I'll be able to work with) - but seeing if this is something else that can be done as the intention from this collection is to count the DisplayNames and enter them into a column chart.

Thanks so much for your help!

Was this reply helpful? Yes No
AaronKnox 514 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @mikzee11 ,

I can look into/test this later, but at a glance I think this can be done within the Collect() function, something like:

Collect( colPSLeads, If(IsBlank(_InnerLoop.DisplayName), {PSName: "Unassigned"} {PSName: _InnerLoop.DisplayName} ) )

Please mark Accept as Solution if this works. This will help others find this solution.

Thanks!

Aaron

Was this reply helpful? Yes No
mikzee11 4 on at

Like
a
(0)

Report
Copy link

Link copied!

@AaronKnox

Thanks again Aaron

That iteration of code did not seem to work. Please if you are able to troubleshoot more to solve this it would be appreciated!

Thanks for your help!

Was this reply helpful? Yes No