cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
keyboardWrench
Helper III
Helper III

Retrieve Values in People & Group Column

Hi, I have a People & Group column that allow multiple values for my Dropdown and Combo Box. I found out the it appears as table for each row.

 

What should by my Function & Syntax to retrieve the values within People or Group column for

1. Look Up

2. Filter

3. Search

 

Any ideas will be appreciated

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @VijayTailor ,

 

OK, I got you. I assumed the combo box only supports three options at a time. Then use the FirstN/Last combination to check whether each selected user is in the Person column of the same record.

Modify the Filter part code as follows.

Filter('SP list',
If(IsBlank(Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,1)).Result),true, Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,1)).Result in Person&GroupColumn_1.DisplayName) &&
If(IsBlank(Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,2)).Result),true, Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,2)).Result in Person&GroupColumn_1.DisplayName) &&
If(IsBlank(Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,3)).Result),true, Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,3)).Result in Person&GroupColumn_1.DisplayName) &&
'Department' = DeparmentDropDown_1.SelectedText.Result
)

Sik

View solution in original post

8 REPLIES 8
VijayTailor
Resident Rockstar
Resident Rockstar

Hi @keyboardWrench ,

You can use like below.

 

 

AddColumns(TestAkshay,"UserDIsplayName",Concat(Candidate,DisplayName,";"))

 

 

VijayTailor_0-1601873874420.png

People Field - Using filter/LookUp/Search.

LookUp(TestAkshay,MyUser.Email=User().Email)

Filter(TestAkshay,MyUser.Email=User().Email)

VijayTailor_0-1601874315955.png

 


Thanks,
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

v-siky-msft
Community Support
Community Support

Hi @keyboardWrench ,

 

Could you please share a more detailed application scenario about this?

As you mentioned, the multi-selected Person field value is actually a table, each rows stores the info of each selected user. 

If we want to retrieve the data with in the Person field, We just need to treat it as a regular table, for example:

First(First(SPList).PersonColumn).Claims //return the claims of first user
LookUp(First(SPList).PersonColumn,DisplayName="test").Claims //return the claims of the user named test
Filter(First(SPList).PersonColumn,"test" in DisplayName) //Filter users with "test" in the name

Hope this helps.

Sik

Hi @v-siky-msft  & @VijayTailor 

 

Thanks you for your responses.

 

What I am tryin to achieve is to use this on my filters or dropdown box. I have tried @VijayTailor  approach on my dropdown.

If my Person & Group column has a single value rows will appear with the selected Person. However since I have concat & applied distinct on the Column if there are 2 to 3 person I have selected any of the names from the dropdown my rows will be blank. 

 

Below is my grouping filter. If only one Person is in the row it works but it 2-3 person the Gallery returns blank.

 

Person&GroupColumn_1.Selected.Result <> "All Person" And DeparmentDropDown_1.SelectedText.Result <> "All Department",
GroupBy(
Filter(
'SharePoin_List',
Person&GroupColumn_1 = Person&GroupColumn_1.Selected.Result And 'Department' = DeparmentDropDown_1.SelectedText.Result
),
"Division",
"DivisionGroupDetail"

 

Any thoughts?

Hi @keyboardWrench ,

 

Does the Person column in SharePoint list also allow multiple selection?

What is the Items property of the person combo box?

 

In general, you need to do this with the in operator, not the = operator. Please try this:

Filter(
'SharePoin_List',
Person&GroupColumn_1.DisplayName in Person&GroupColumn_1.SelectedItems.Result And 'Department' = DeparmentDropDown_1.SelectedText.Result
)

Hope this helps.

Sik

Hi @v-siky-msft , I tried this but it goes into and the SelectedItems.Result is being underlined in red.

Hi @keyboardWrench ,

 

Please reply on my first two questions, and post more snapshots on your app.

Sik

hi @v-siky-msft,  

 

The Person or group column allows multiple values.

Here's my formula in items column, this is read by the formula bar as Data type: Table

Sort(Distinct(PersonGroupColumnCollection, DisplayName),Result)

 

In my App start I have this

ClearCollect(PersonGroupColumnCollection,{DisplayName:"All Person"});
ForAll('Division SharePoin List'.AnalystPersonColumn, Collect(PersonGroupColumnCollection, Person&GroupColumn_1.DisplayName));

 

Let me know if this information will suffice

 

 

 

Hi @VijayTailor ,

 

OK, I got you. I assumed the combo box only supports three options at a time. Then use the FirstN/Last combination to check whether each selected user is in the Person column of the same record.

Modify the Filter part code as follows.

Filter('SP list',
If(IsBlank(Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,1)).Result),true, Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,1)).Result in Person&GroupColumn_1.DisplayName) &&
If(IsBlank(Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,2)).Result),true, Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,2)).Result in Person&GroupColumn_1.DisplayName) &&
If(IsBlank(Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,3)).Result),true, Last(FirstN(Person&GroupColumn_1.SelectedItems.Result,3)).Result in Person&GroupColumn_1.DisplayName) &&
'Department' = DeparmentDropDown_1.SelectedText.Result
)

Sik

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,126)