Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[John Doe]

How to restrict the people displayed in the “To be assigned to“ column of type person or group of a SharePoint list, I want you to only show me the list of people associated with a specific department that I am getting in another field called “Department“ of type A line of text from Sharepoint, the information shown in the “Department” field I get using the Office365 Users connection where the Default property I have the following instruction to get the department of the user who started the application: “Office365Users.MyProfileV2().department“.

I‘m doing this with PowerApps in a form from a SharePoint list, for the “To be assigned to” field I’m using a Combo Box and for the “Department“ field I‘m using a text entry.

I would like to know if there is a way to filter the options in the column “To be assigned to” according to the department shown in the “Department“ field, which in the end that department is the same as the user who started the application.

 

I tried the following in the Items property of the Combo box “To be assigned to“, but it didn‘t work

 

Filter(
[@'List_Sharepoint'].' To be assigned to';
ThisItem.Departament = DataCardValue3.Text
)

Any other suggestions for something I can do?

——————————
John Doe
——————————

[Rachel Davis]

What you are describing is called a cascading field. The typical example is choosing a state and only seeing cities within that state. The trick is setting up the relationship to tie a city to a state, or in your case a person to a department. Obvs you could just manually maintain the list of People+Department but ick. 

So make a list in SharePoint with your people and use a People field. Add a column for Department. The O365 profile for each user contains information about them. So I would run a workflow on the list such that when you add/update a person in the list, Power Automate updates the Department column with the person’s department. 

Then in your app, use a distinct filtered list of the Department column the initial dropdown for your user to choose a department. This will in turn allow you to filter the list to show only the people in that department.

The workflow will only work when a list item is updated. You will still need to manually maintain the list. 

Theoretically, you might also be able to create a dynamic O365 group. So people are automatically added to the group when they meet certain conditions, like having a specific job role or report to a specific manager. I’m assuming Power Apps can look up people in a group? If yes, then maybe you could do the look up against the list of group members, report the departments in the Department dropdown and select from the remaining people.  

NO idea if that would work or how to actually do it, but theoretically it’s possible and you wouldn’t have to manually maintain a list.

——————————
Rachel Davis
Project Manager, OPSS Operations
Rockwell Automation
——————————
——————————————-

[John Doe]

Hi @Rachel Davis

Thank you for your answer but it is impossible in my case, as you tell me to make a Sharepoint list with people, I can not make a Sharepoint list with more than 1,000 people that I have in O365 users, every day new users are created as well as users are deleted. How can I keep that list up to date every time a user I have in O365 is created or deleted? How do I upload my current list of people in O365 to my SharePoint list?

Is there another way I can achieve this?

——————————
John Doe
——————————
——————————————-

[Rachel Davis]

I’m not sure what your app is doing, so hard to say what the best course is. But there are 2 main ways that people fields are used in my experience.

1.  Tagging ANYONE in the company: Where the item could be sent to anyone in the company AND the user knows who that person is. In this case, I would skip the department filter and just let the user type in the name of the person they want the thing to go to. At this point the dept filter isn’t really adding much value and could actually be slowing the performance as it tries to filter every person in your company.

2. A defined list of people: This is where you have a defined list of people and the user may not know who the receiver is. You typically see this when you need a purchase approved or something – you know your Department and the purchase type/amount, but maybe don’t know who is responsible for approving it. In this case, you will have to have a way to define that list and make an equivalence between what the user knows (department, purchase amount, etc.) and the person responsible for each category. I use a SharePoint list and make the business responsible for maintaining the list. When the responsible person changes, they have to update it.

Or you can look into another option for a defined list, such as a dynamic O365 group if the parameters are based on something in their O365 profile, like department, job role, etc.

In either case, you’ll want to use something based on their O365 profile. I know how to do this in Power Automate – look up the parameter, grab the UPN from the associated user profile. I assume a similar step is possible in a Power App. 

——————————
Rachel Davis
Project Manager, OPSS Operations
Rockwell Automation
——————————
——————————————-

[Larry Burgess]

Try this.syntax…
Filter(list name, criteria, value to be returned in dropdown)

Filter([@’List_Sharepoint’];ThisItem.Department = DataCardValue3,Text;’To be assigned to’)

——————————
Larry Burgess
Developer
——————————
——————————————-

[John Doe]

Hi  

With the previous syntax he shared with me it does not show me any results in the ComboBox, plus I receive a delegation warning. The warning states the following:

“Delegation warning: The “Filter“ part of this formula may not work correctly on large data sets. A Boolean value was expected. We expect a Boolean (true/false) value at this place in the formula.”

Is there anything else that needs to be changed in the formula you shared with me, or that requires more information from me?

——————————
John Doe
——————————
——————————————-

[Larry Burgess]

That should work.  I copied it directly from a working app.  Maybe I need more information about your situation.  From the error message you provided, one of the values is a Boolean but I don’t know which one.

——————————
Larry Burgess
Developer
——————————
——————————————-

[John Doe]

Hi  

Returning again to the syntax that you shared with me, the Boolean value that is indicating the warning is the field ‘To be assigned to’, this column in the Sharepoint list is of type “Person or group” which performs the search in the O365 users, this same field in PowerApps is a ComboBox element, I hope this description is helpful to correct it because it generates this warning and does not perform any search.

——————————
John Doe
——————————
——————————————-