cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PhilD
Level 8

Collecting a multi-select SharePoint person field into flat table

Need some help understanding what is possible. I have a standard SharePoint list with a person column named 'Attendees' set to accept multiple selections.

 

I would like to collect a flat table of all attendee's name.

 

So for example, list item #1 has 3 attendees and list item #2 has two attendees, I would like to return a collection like this instead of the nested collection I'm getting.

 

Name

Joe

Frank

Sally

Fred

Bob

 

This is my latest attempt that didn't work. All other combinations still resulted in a nested type collection (one row per list item rather than one row per attendee). Can anyone help point me in the right direction here, thanks so much as always!

 

ClearCollect(colAttendees,
    ForAll(Attendance.Attendees,   
        Table(   
                {'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser"},
                {Claims: Attendees.Claims},
                {Department:Attendees.Department},
                {DisplayName:Attendees.DisplayName},
                {Email:Attendees.Email},
                {JobTitle:Attendees.JobTitle},
                {Picture:Attendees.Picture}               
        )
    )
)

Yielded this... obviously not what I want but

 

CollectionMultiPersonSharePointColumn.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
strategery
Level 8

Re: Collecting a multi-select SharePoint person field into flat table

Not sure if you want only the names, but this should get you there; you can leave off .DisplayName to collect all fields

Clear(colAttendees);ForAll(Attendance,Collect(colAttendees,Attendees.DisplayName))

If you want to eliminate repeats, just use 'Distinct' function when showing colAttendees

Distinct(colAttendees,DisplayName)

OR create a temp collection up front to get the Attendees, then create the names collection using 'Distinct' on the temp collection

Clear(tempcol);ForAll(Attendance,Collect(tempcol,Attendees));ClearCollect(colAttendees,Distinct(tempcol,DisplayName))
3 REPLIES 3
strategery
Level 8

Re: Collecting a multi-select SharePoint person field into flat table

Not sure if you want only the names, but this should get you there; you can leave off .DisplayName to collect all fields

Clear(colAttendees);ForAll(Attendance,Collect(colAttendees,Attendees.DisplayName))

If you want to eliminate repeats, just use 'Distinct' function when showing colAttendees

Distinct(colAttendees,DisplayName)

OR create a temp collection up front to get the Attendees, then create the names collection using 'Distinct' on the temp collection

Clear(tempcol);ForAll(Attendance,Collect(tempcol,Attendees));ClearCollect(colAttendees,Distinct(tempcol,DisplayName))
PhilD
Level 8

Re: Collecting a multi-select SharePoint person field into flat table

Thanks so much @strategery! Exactly what I was looking for, works great.

PhilD
Level 8

Re: Collecting a multi-select SharePoint person field into flat table

In case anyone is following along at home (or I forget how to do this later), I was able to extend what @strategery provided to append fields from the "parent" item to each of the "children".

 

For example, in my SharePoint list, the column 'CouncilCommittee' is a column of the main list item entry. The code below appends the list item column 'CouncilCommittee' & 'MeetingDate' on each of the attendees. Not the fastest running but helped me understand how to manipulate this type of list. Thanks again @strategery!

 

 

ForAll(
    FirstN(Filter(Attendance,!IsBlank( CouncilCommittee)),10),
        Collect(
            colAttendees,AddColumns(Attendees,"CouncilCommittee",CouncilCommittee,"MeetingDate",MeetingDate)
        )
)