cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Cascading Comboboxes with Office365Users Search filter Results by dependent Department wont save to SharePoint People Picker Columns

In a SharePoint List i have two People Picker Columns with Single value.

The SharePoint People Picker "Worker Department" is in PowerApps the ComboBox "cbWorker" 

The SharePoint People Picker "Worker" is in PowerApps the ComboBox "cbWorker" 

 

I have the requirement to search all Users by Department (AAD/User Profile Property) in a selection first and then in this pre-filtered department selection i want to choose a specific Users from this Department. All People Picker an ComboBoxes are Single Value.

In the PowerApps DataCardValues for "cbWorkerDepartment" i have set the Items-Property to

Items:

 

Distinct(Office365Users.SearchUser(),Department)

 

 

WorkerDepartment.png

 

In the PowerApps DataCardValues for "cbWorker" i have set the Items-Property to

Items:

 

Filter(Office365Users.SearchUser({searchTerm:"", top:500}),Department=cbWorkerDepartment.Selected.Result)

 

 

Worker.png

 

The depentend ComboBoxes works like a charm, in the first Combobox "cbWorkerDepartment" i get all Departments - the multiple values for Departments of all users are aggregated with the Distinct ()-Function - and if i choose one Department then the second ComboBox "cbWorker" gets the filtered Users by this Department and i can choose one User from this selection.

 

But now the Problem: 

Both Result-Values from the Office365Users.SearchUser are not saved to the SharePoint List. The Poeple Picker Columns remains empty.

WorkerMissing.png

 

When i Chose Dropdown-Cards instead of ComboBox-Cards then the Values will be saved to the SharePoint Columns. But these DropDowns are simple Text-Columns which i can not use for User-Notifications and Approvals. I need complex People Picker Columns for this purpose. But this ComboBoxes-Results/Values are not saved to SharePoint...

 

Whats wrong with my setup? 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Frequent Visitor

Re: Cascading Comboboxes with Office365Users Search filter Results by dependent Department wont save to SharePoint People Picker Columns

The Items saving (Update) only with the Items-Standard Values of the DataCardValues

Items:

 

Choices([@Incidents].bipWorker)

 

bipWorker is the internal Fieldname of the People Picker Column.

 

But i need a Filter in this Items, depending on the Department-Selection from the previous ComboBox.

Instead of Office365Users.SearchUser i have also tried a regular Managed Metadata Column on the Department-Termset of the Termstore-Management (People Termstore). This first MMS Column with ComboBox "cbDepartments"- left in Standard - will save to SharePoint.

 

But the second ComboBox "cbWorker", which filters the selected Departments in "cbDepartments" , wont save to SharePoint with these Filter in Items-Property of "cbWorker"

 

cbWorker Items:

 

Filter(Office365Users.SearchUser({searchTerm:"", top:500}),Department=cbDepartments.Selected.Label)

 

Hint: i have to use the Label-Value from the selected Item, because of complex Taxonomy Field Type (WSSID,'VAL|GUID), the Label-Value delivers the pure Text-Value of the Department-Term

... but this Filter does not save the selected Item to SharePoint!

 

only the Standard Items without Filter:

saves the Item to the SharePoint List.
 
Whats wrong with my Filter-Logic?

View solution in original post

Highlighted
Frequent Visitor

Re: Cascading Comboboxes with Office365Users Search filter Results by dependent Department wont save to SharePoint People Picker Columns

Sorry i have found the solution myself.

 

People Picker are complex datatypes, similar as Managed Metadata Columns.

Instead of using the default Update-Property of the DataCard for cbWorker

DataCardcbWorker Update:

cbworker.selected

i have to use the following Claims/Displayname Values from Selected Item to update a complex people picker column in SharePoint  

DataCardcbWorker Update:

{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",Claims:"i:0#.f|membership|"&cbWorker.Selected.Mail,DisplayName: cbWorker.Selected.DisplayName}

with this Update Statement in the DataCard, the SharePoint Columns are written with values.

 

View solution in original post

4 REPLIES 4
Highlighted
Frequent Visitor

Re: Cascading Comboboxes with Office365Users Search filter Results by dependent Department wont save to SharePoint People Picker Columns

The Items saving (Update) only with the Items-Standard Values of the DataCardValues

Items:

 

Choices([@Incidents].bipWorker)

 

bipWorker is the internal Fieldname of the People Picker Column.

 

But i need a Filter in this Items, depending on the Department-Selection from the previous ComboBox.

Instead of Office365Users.SearchUser i have also tried a regular Managed Metadata Column on the Department-Termset of the Termstore-Management (People Termstore). This first MMS Column with ComboBox "cbDepartments"- left in Standard - will save to SharePoint.

 

But the second ComboBox "cbWorker", which filters the selected Departments in "cbDepartments" , wont save to SharePoint with these Filter in Items-Property of "cbWorker"

 

cbWorker Items:

 

Filter(Office365Users.SearchUser({searchTerm:"", top:500}),Department=cbDepartments.Selected.Label)

 

Hint: i have to use the Label-Value from the selected Item, because of complex Taxonomy Field Type (WSSID,'VAL|GUID), the Label-Value delivers the pure Text-Value of the Department-Term

... but this Filter does not save the selected Item to SharePoint!

 

only the Standard Items without Filter:

saves the Item to the SharePoint List.
 
Whats wrong with my Filter-Logic?

View solution in original post

Highlighted
Frequent Visitor

Re: Cascading Comboboxes with Office365Users Search filter Results by dependent Department wont save to SharePoint People Picker Columns

Sorry i have found the solution myself.

 

People Picker are complex datatypes, similar as Managed Metadata Columns.

Instead of using the default Update-Property of the DataCard for cbWorker

DataCardcbWorker Update:

cbworker.selected

i have to use the following Claims/Displayname Values from Selected Item to update a complex people picker column in SharePoint  

DataCardcbWorker Update:

{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",Claims:"i:0#.f|membership|"&cbWorker.Selected.Mail,DisplayName: cbWorker.Selected.DisplayName}

with this Update Statement in the DataCard, the SharePoint Columns are written with values.

 

View solution in original post

Highlighted
Advocate II
Advocate II

Re: Cascading Comboboxes with Office365Users Search filter Results by dependent Department wont save to SharePoint People Picker Columns

the Office365Users.SearchUser is not reliable in Tenants with more then 1000 Users.

You have to filter the Users a better way.

You must prefetch the dependent Department-Label in a Text-Label named lblDepartment with the Text-Formula

lblDepartment.Text:

 

cbDepartments.Selected.Label

 

then refer to this lblDepartment.Text in your Filter-Formula:

cbWorker.Items:

 

Filter(Office365Users.SearchUser({searchTerm:cbWorker.SearchText,top:999}), IsBlank(Department)=false && AccountEnabled=true && Department=lblDepartment.Text)

 

This is working for me in a Tenant with many thousands of users... 

Highlighted
Frequent Visitor

Re: Cascading Comboboxes with Office365Users Search filter Results by dependent Department wont save to SharePoint People Picker Columns

This Department-Filters failing on Departments, which contains a "&"-Character (Ampersand) in its Department-Label. It seems like the Office365User.searchUser-Connector is not able to escape this special character in the Department-Property of the AAD. Seems to be a bug...

 

Also this Connector does not initially show all the users of the filtered Department in the Searchbox, some Users behind the Top:999 search and beyond the Connector-Limitation of the PowerApps (Standard:500) will not appear. Even if you extend the PowerApps Connector Limits in the App Settings from Standard 500 to the maximal possible 2000, it will not show some Users initially until you begin to Search for these Users with there Displayname. This is observable in Tenants with 2500 Users. With the input of the first character of the Displayname, the filtered user appears...   

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (4,115)