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)
In the PowerApps DataCardValues for "cbWorker" i have set the Items-Property to
Items:
Filter(Office365Users.SearchUser({searchTerm:"", top:500}),Department=cbWorkerDepartment.Selected.Result)
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.
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?
Solved! Go to Solution.
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:
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.
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:
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.
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...
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...
User | Count |
---|---|
256 | |
106 | |
92 | |
47 | |
37 |