Populating Drop Down with Single Field Multiple Choice Column in Sharepoint List

[BeardBro]

I think I have been dancing around the solution but cannot seem to solve this myself.

I have an app that will allow a user to view their budget items from a Sharepoint List called 'Budget'. The user is authenticated via another Sharepoint List called 'Approvers List' that has a single choice People Picker Column and a multiple choice Departments column. Some users will have access to many departments, meaning they should be able to see many budgets. This needs to be scalable so I really need to keep the Departments column as multiple selection choice and not break them out into Department 1 Column, Department 2 Column, etc. It has to be a single column.

In the app, I have a gallery that is viewing the contents of the 'Budget'. I have a dropdown that should allow the user to select which department they are viewing in the gallery. This is where I am having trouble. I cannot figure out how to populate the dropdown with the 'Departments' from the 'Departments' multiple selection choice column in the 'Approvers List'.

This is in my OnStart at the App level to scope in on the user in the Approvers List:

// Obtain username and email of the logged-in user
Set(varLoggedInUser, User());

// Find the user with a matching email in the App Users list
With(
    {wUserRecord: LookUp('Approvers List', Person.Email=varLoggedInUser.Email)},
    Set(varUser, wUserRecord.Person);

And this is what I currently have in the dropdown that is not working:

With(
    {wUserRecord: LookUp('Approvers List', Person.Email=varLoggedInUser.Email)},
    Concat( wUserRecord.Departments,Value,",")

I've tried messing around with some Split and ForAll functions but I'm having trouble seeing the solution. Once I can populate the dropdown, filtering the gallery will be a breeze. Thank you in advance!

[BeardBro]

Still experiencing issues with this.

[v-liwei-msft]

Hi @BeardBro 

Could you please tell me:

Do you want to display multiple departments corresponding to the current user in the dropdown?

I have made a test for your reference:

LookUp(DataSource,Person.Email=User().Email).Department

Best Regards,

Levi

[BeardBro]

Hi Levi,

Thank you for the reply. I should have clarified that these are not Azure Directory departments, rather the 'Departments' is a column from the 'Approvers List' Sharepoint List that allows multiple selection. Here is an example:

Approvers List (Sharepoint List)

Person                   Departments

John Doe               (Business) (Education) (Community Relations)

Now in my PowerApp, I am comparing the signed in user to the user from the 'Person' column in the Approvers List. Let's say John Doe is using the PowerApp. I need a dropdown that shows all of the available Departments from the 'Departments' column in his row in the Approvers List. So if John Doe selects this dropdown, he should see the following choices:

Business
Education
Community Relations

I hope that clarifies.

[BeardBro]

Progress but still not solved. In the above example of John Doe, I can now see populated content in my dropdown. However, I am only seeing a single option/string *without* any commas, rather than multiple options delimited by the commas in the original string. Here is the code in my Items property of the dropdown:

Split(Concat(varDepartments,Value),",")

The string from the Concat is "Department1,Department2,". When I go to use the dropdown, I am only seeing a single option that reads "Department1Department2".

[v-liwei-msft]

Hi @BeardBro 

I think you need to modify the dropdown's items property to:

LookUp(DataSource,Person.Email=User().Email).Department

[BeardBro · Accepted solution]

Thank you but that did not work within the context of my app.

I did get it working and the solution turned out to be this:

In App OnStart:

// Obtain username and email of the logged-in user
Set(varLoggedInUser, User());

// Find the user with a matching email in the App Users list
With(
    {wUserRecord: LookUp('Approvers List', Person.Email=varLoggedInUser.Email)},
    Set(varUser, wUserRecord.Person);
    Set(VarUserEmail,wUserRecord.Person.Email);
    Set(varDepartments,wUserRecord.Departments.Value));

In the Items property of the dropdown:

Split(Concat(varDepartments,Value,","),",")

And one step further, how I am sorting the Gallery from the dropdown:

Filter('SharepointList',Department= Dropdown.Selected.Result)