cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
NightKing
Level: Powered On

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
NightKing
Level: Powered On

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

NightKing
Level: Powered On

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
NightKing
Level: Powered On

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

NightKing
Level: Powered On

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

SchneidAIR
Level: Powered On

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... 

IPIGmbHbyTeGut
Level: Powered On

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
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (4,938)