Building a Modern SharePoint Solution: Part 5 – Cascading Drop-down Lists and People Picker in PowerApps

This is going to be the last PowerApp based post in this series.  After this, I will be moving on to completing the solution with Microsoft Flow.  In this post, I will show you how to create cascading drop-down lists in PowerApps.  This corresponds to the requirement in my first post: “Ability to select from a list of approvers based on the project or event.”  This was something that could be done in InfoPath, but it was a LOT of steps.  In PowerApps, cascading drop-downs are actually pretty straightforward once you get the hang of it.  I, however, decided to do the hardest type of cascading drop-down there is with regards to SharePoint. Building a drop-down around a regular field is nice and easy; cascading drop-downs to select a person\group value is much more difficult.

Posts in this series:

• Preparing SharePoint
• Integrating PowerApps as a Custom List Form
• Customizations All List Form PowerApps Should Have
• Setting a SharePoint People Picker Field Programmatically in PowerApps
• Cascading Drop-down Lists and People Picker in PowerApps
• Create a Flow to be Started Manually
• Creating a Multiple Approver Microsoft Flow
• Sending a Tweet from Microsoft Flow
• Starting a Microsoft Flow from a PowerApp

 

Setup Cross Reference List(s)

If you are setting up the entire solution I would recommend placing the data in the same list.  I suggest this for a couple of reasons.  The first being that editing is super easy because everything is the same list and you don’t have to bounce back and forth to ensure things sync up (it just makes sense).  The second is that when you are creating a cascading dropdown you are usually doing it with a separate connection in your PowerApp.  If you don’t have the data contained in the same source, you can still do it, but you are now creating multiple sources that the app has to ping for updates.  To add to my complexity and to find out if I could actually do it, the fields I wanted are also complex.  In my list, I want to select the Approver based on the project\event.  Approver is a Person\Group field in SharePoint and the Project\Event is actually a lookup to another list entirely.  This is what my source list looks like:

Add New Source to Form

Because this PowerApp is being built from the Social Media Requests list it won’t have any connection to your source data.  Yes, the same fields exist in the request list, but the actual cross-reference data is not there.  You need to add the list as a separate connection to your PowerApp.

1. Click on View
2. Click on Data sources
3. Click on ‘+ New Connection
   “
4. Because we are adding from the same SharePoint connection you can select the one for your tenant
   
5. Enter the URL of your site
   
6. Select your source list and click Connect.

        

Populate Collection with Source Data

Next, you are going to want to edit the screen’s OnVisible property to create a collection that contains the contents of the source list.  I’ll explain later, but you can’t actually access the data from the field due to constraints within PowerApps.  So to create this value perform the following:

1. Click on the screen object of your form.
2. Select the OnVisible option from the property drop-down.
3. Add the following code to the property:

Clear(approverList);
Collect(approverList,'Social Media Approvers')

The code in #3 is saying: Clear (if exists) the collection “approverList, then update the collection and get the data from the connection “Social Media Approvers”

Update Data Card Configuration

Here’s where we are going to have to get fancy.  Unfortunately or fortunately depending on what you need to do, the fact that I want to filter on a Person\Group field made this much more complex and actually something I do not believe you could do in InfoPath.  When the filter is applied to the data card value field (combo box) it forces it to save it in a manner that is not compatible with the actual SharePoint field.  So to actually set the value of the data card properly we are going to mask it with another dropdown.  I’ll explain as we go along.

The first thing you want to do is modify the type of field the data card value is.  By default, a Person\Group field will show up as an “Edit Selection” style field on the form.  We actually want to modify it to an “Edit Lookup”.  This is because it will allow us to set the default value the same as we did with the approving manager (see part #4 of this series).  Edit Selection does not allow for that.

• Select the data card field and then click on Edit beside the “Field” property
• Beside the data card item in the field selections, click on the form editor button
• Select the Edit Lookup option.

 

Add and Customize Drop-Down for Selecting Approver

Next step is to add a separate drop down to the form.  This is going to be used on the form to select approver from the list.  In a nutshell what we are going to try to be doing is using this drop-down as the child in the cascading drop-down process.  We will then use the value of the drop-down to populate the People Picker data card so that when the form is submitted, the data is in the necessary format to upload to the list.

1. Add drop-down to your form
2. Temporarily move it to an open space on the form until we have it wired up properly (we will be moving it later)
3. Set the filter against the parent drop-down by adding the following code to the Items property:

(Distinct(Filter(approverList,ProjectEvent.Value=DataCardValue4.Selected.Value),Concatenate(SocialMediaApprover.DisplayName,"\",SocialMediaApprover.Email)))

Deconstructing the code we can see that we are filtering the contents of the Social Media Approvers created earlier where the parent drop-down has a value equal to the ProjectEvent field in the list.  We are then displaying a distinct set of values with the format of Display Name\Email Address.  The email address is going to be used to fill in the data card.  We need to have the email address in order set the value of the people picker, but I didn’t want just use the value of the email address in the drop-down in case the company uses less user-friendly email addresses.  So what you end up with is something like this (click picture to see video):

Setting the People Picker Field

Now that we are able to set the drop-down we need to update the People Picker data card.  To do this we need to set some values when the value of the drop-down changes.  In the OnChange property of the drop-down, we need to first get the email address from the value.  Once we have that we will be using it to load the user profile of the account attached to the email address.  The code to do this is:

UpdateContext({approverEmail:Last(Split(Dropdown1.Selected.Value,"\")).Result});
If(!IsBlank(DataCardValue4.Selected.Value),UpdateContext({projApprover:Office365Users.UserProfile(approverEmail)}),UpdateContext({projApprover:Blank()}))

The first command creates an array of the string in the drop-down by splitting it on the “\”.  We then grab the final value (using the Last function) of the array and pass it into the variable approverEmail.  Next, if the parent drop-down isn’t blank, we set a variable called projApprover to contain the profile of the user with the email address.  If it is blank, we clear the value so the other fields will also clear (you’ll see in a minute).

If you attempted this before reading the next step you will find that nothing happens when you test this.  This is because the OnChange event only fires when initiated by a user action.  If you had multiple selections and changed the value, it would then fire.  But, setting the value from another field won’t fire it.  So this means we also have to place the same code in the OnChange event of the parent drop-down.

To set the People Picker data card we simply have to do the same steps as in Part 4 of this series.  Add the following code to the Default property of the data card’s value object:

{
    '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
    Claims:Concatenate("i:0#.f|membership|",projApprover.UserPrincipalName),
    Department:projApprover.Department,
    DisplayName:projApprover.DisplayName,
    Email:projApprover.UserPrincipalName,
    JobTitle:projApprover.JobTitle,
    Picture:""
}

Testing things out will show you something like the following:

Finally, you simply need to place the drop-down you added over top of the Project Approver People Picker and then hid the People Picker value box.  This will give you the cascading drop-down for a person type field, but also allow you to upload the value to the list.

Food for Thought

Remember earlier when I said we had to use a collection?  Well here’s why.  Let’s say I didn’t want to use a collection but wanted to access the list directly.  So my code would look like this:

Distinct(Filter('Social Media Approvers',ProjectEvent.Value=DataCardValue2.Selected.Value),SocialMediaApprover.DisplayName)

While this code is correct you will receive the following message: “Suggestion: Part of this Filter formula cannot be evaluated remotely due to service limitations.  The local evaluation may produce suboptimal or partial results.  If possible, please simplify the formula.  For more information, please see the Filter function documentation”

I believe this is not allowed, because of the number of unknown hits to the data source it could cause so a limitation is put in place.

Clean Up

Now that you have the filter working, you want to make it look a little nice.  One of the things you want to ensure is that the child field can’t be active if the parent field is blank.  So to complete this you just:

1. Click on the child data card
2. Select the DisplayMode property
3. Add the following code that sets the field to edit only if there is data in the parent:

If(IsBlank(DataCardValue2.Selected.Value),DisplayMode.View,DisplayMode.Edit)

Finally the last item you should do is reset the child card should the parent card change.

1. Click on the Parent Data Card
2. Select the OnChange property
3. Add code to reset the child data card

Reset(DataCardValue5)

When all is complete it will look a little like this (click image to see video):

 

 

Thanks for reading!!