cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
biterbit
Level: Powered On

How to achieve a filtered drop-down using SharePoint lookup fields

I have an app in development and I'd like to improve its usability, but can't work out if it's possible to do what I want.

I have a form based on a SharePoint list representing Clients. In the Clients list are several lookup fields referring to other SharePoint lists, including one for Staff and one for Locations (both single value fields). In the Staff list there is also a multi-value lookup field that refers to the Locations list to indicate which places a staff member works at. 

In the Client form I show a drop-down to select the client's location. I then have a drop-down to assign a staff member to the client, and I want that to show a filtered list of staff to include only those whose locations include that of the client.

 

I have tried various things but the closest I've got is a filter on the staff drop-down Items property that reads:

Filter(Staff, valueLocation.Selected.Id in Staff.Locations)

where valueLocation is the drop-down for the client's location, and Staff.Locations is the lookup field in the Staff list allowing sections of multiple locations. This highlights an error on the '.Id' portion of the formula saying "Can't convert this data type. PowerApps can't convert this number to a table."

Can anyone spot what I am doing wrong or advise if I'm using an inappropriate technique?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: How to achieve a filtered drop-down using SharePoint lookup fields

Hi @biterbit ,

Do you want to filter staff accrding to the selection of location?

I've made a similat test for your reference:

Insert two drop downs.

Set drop down1's Items:

Choices(client.clientlocation)

Set drop down2's Items:

Filter(Staff,Dropdown1.Selected.Value in stafflocation.Value).staff

Then the drop down will filter according to the selection of drop down1.

 

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User
Super User

Re: How to achieve a filtered drop-down using SharePoint lookup fields

It appears to me that you need to be using valuelocation.Selected.Value rather than valuelocation.Selected.Id.  

Community Support Team
Community Support Team

Re: How to achieve a filtered drop-down using SharePoint lookup fields

Hi @biterbit ,

Do you want to filter staff accrding to the selection of location?

I've made a similat test for your reference:

Insert two drop downs.

Set drop down1's Items:

Choices(client.clientlocation)

Set drop down2's Items:

Filter(Staff,Dropdown1.Selected.Value in stafflocation.Value).staff

Then the drop down will filter according to the selection of drop down1.

 

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
biterbit
Level: Powered On

Re: How to achieve a filtered drop-down using SharePoint lookup fields

Thanks for the replies. That example solution worked fine - although I did not include the '.staff' at the end of the Items formula as that seemed to be redundant.

I think my misunderstanding was thinking that a SharePoint lookup reference to records in another table would be stored and returned as (foreign key) IDs - presumably PowerApps presents them as text values.

Thanks again.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

Users Online
Currently online: 212 members 4,568 guests
Recent signins:
Please welcome our newest community members: