Select User Based On Drop Down (Narrow Scope)

[amunoz0920]

I want to select my "user" based on a drop down in my Power Apps form that is attached to a SharePoint List. This I can do if I hard code the names into my app. The problem I run into is that my user list of "Appointing Authorities" will change. I need to narrow the scope of my user list to a group of 7 people that will change.  So if my user selects "IT" it will go the IT Manager, and If they select "Building" it will go the Building Manager. I need a way to reach back into my SharePoint list of Managers and look for the department, which then will select the Manager of that department. Could someone point me in the right direction.  Thank you.

[Anonymous]

LookUp(ManagersList,Department=Dropdown1.SelectedText,ManagerName)

Use this, where ManagersList is your SharePoint list, Department is the column where you will lookup the department name, Dropdown1 is your dropdown to select the department, and ManagerName is the column where the manager's name is stored.

[mdevaney]

@Anonymous
Just as an FYI the SelectedText property was deprecated. Here’s a link to the official doc showing the information https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/controls/control-drop-down

The current suggested way to do this is:
Department=Dropdown1.Selected.Value

Just trying to be helpful...

[v-xida-msft · Accepted solution]

Hi @amunoz0920 ,

Could you please share a bit more about the data structure of your SP List?

Which type column do you use to store the Manager in your SP List? Text type column or Person type column?

Based on the needs that you mentioned, I think the LookUp function could achieve your needs. I have made a test on my side, please consider take a try with the following workaround:

1. If you use Text type column to store the Manager in your SP List:

LookUp('YourSPList', Department = DepartDropdownbox.Selected.Value, ManagerColumn)

If the Department column is a Choice type column, please modify above formula as below:

LookUp('YourSPList', Department.Value = DepartDropdownbox.Selected.Value, ManagerColumn)

2. If you use Choice type column to store the Manager in your SP List:

LookUp('YourSPList', Department = DepartDropdownbox.Selected.Value, ManagerColumn.DisplayName)

If the Department column is a Choice type column, please modify above formula as below:

LookUp('YourSPList', Department.Value = DepartDropdownbox.Selected.Value, ManagerColumn.DisplayName)

In addition, if you also used a ComboBox to list the Manager info in your Edit form (Manager column is a Person column in your SP List), and you want populate the Manager ComboBox based on the selected Department value, please consider take a try with the following workaround:

Set the DefaultSelectedItems property of the Manager ComboBox to following formula:

{
  Claims: "i:0#.f|membership|" & Lower(LookUp('YourSPList', Department = DepartDropdownbox.Selected.Value, ManagerColumn.Email)),
  DisplayName: LookUp('YourSPList', Department = DepartDropdownbox.Selected.Value, ManagerColumn.DisplayName),
  Email: LookUp('YourSPList', Department = DepartDropdownbox.Selected.Value, ManagerColumn.Email),
  Department: "",
  JobTitle: "", 
  Picture: ""
}

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[amunoz0920]

I currently have two SharePoint sites. 

The first is the site that holds my list submissions.  This is also the list that hold the "Department" as a Choice column and "Approvers" as a Person or Group column.

My second site holds the "Managers" list by department. The "Managers" is a Person or Group column, the "Department" is a Choice column.

What I'm hoping for is that when a user selects Department as choice in the Power Apps form, the "Approvers" combo box Lookup on SharePoint #2 at the "Department" Choice and populate the "Approvers" combo box with the Manager. 

So for example: User selects "IT" on my Power Apps form. The "Approvers" combo box reaches back to SP#2 and looks for "IT" in the "Department" Choice box, then adds the "Manager" that is listed in that SharePoint item as a person. 

I really appreciate you help, I tired to be as thorough as possible in my explanation. I'm happy to change my workflow if there is an more simple way to do that same job. Thank you!

[amunoz0920 · Accepted solution]

Thank you! This is what ended up working for me:

{
  Claims: "i:0#.f|membership|" & Lower(LookUp('YourSPList', Department.Value = DepartDropdownbox.Selected.Value, ManagerColumn.Email)),
  DisplayName: LookUp('YourSPList', Department.Value = DepartDropdownbox.Selected.Value, ManagerColumn.DisplayName),
  Email: LookUp('YourSPList', Department.Value = DepartDropdownbox.Selected.Value, ManagerColumn.Email),
  Department: "",
  JobTitle: "",
  Picture: ""
}

[v-xida-msft]

Hi @amunoz0920 ,

Is it the solution I provided above helpful in your scenario?

If the solution I provided above is helpful in your scenario, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

Best regards,