Filter a dropdown

[McMonty]

Hi,

I would like to filter a dropdown on a form where the column in SharePoint is a lookup to another SharePoint list.

The datasource for the dropdown is the CostDepartment field in the Expenses list, which in turn is a lookup to the Department list.

(See images below)

And this is the related Departments SharePoint list

What I need is to filter the Departments list items based on the value in the TeamAccessCode column. 

Thanks in advance 🙂

[eka24]

Watch this video tutorial:
https://youtu.be/URe94EYCSz8?t=185

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[McMonty]

Thanks, but that's not really what I mean, unless I'm totally mistaken.

The form is bound to the Expenses list with the dropdown getting items from the CostDepartment Choice column which is a lookup to the Departments list. I need to filter the data that populates the lookup in Expenses that populates the dropdown. 

[v-yutliu-msft]

Hi @McMonty ,

Do you want to filter Departments list based on CostDepartment field selected in the drop down?

Is  CostDepartment field related to Department field in Departments SharePoint list?

The steps are:

1)choose CostDepartment value

2)get related Department field value

3)get related TeamAccessCode column value

4)filter Departments list

If so, try this:

1)the drop down's Items:
Choices(Expenses.CostDepartment)

2）set the gallery's Items:

Filter(Departments,TeamAccessCode in  
                   Filter(Departments,Departments=DropDown1.Selected.Value).TeamAccessCode
       )

Best regards,

[McMonty]

The CostDepartment in the Expenses list is a lookup field to the Department list

This is how the dropdown is populated

I need to filter what goes in to the dropdown.

I can't directly filter the CostDepartment column because I need the filter to be based on the TeamAccessCode column in the Departments list. 

[v-yutliu-msft]

Hi @McMonty ,

I think my formula is exactly what you want.

Have you tried it?

Best regards,

[McMonty]

Hi,

Thanks, but I'm not trying to filter a gallery or a secondary dropdown (cascading). I want to be able to filter the lookup column that populates a single dropdown.

[v-yutliu-msft]

Hi @McMonty ,

Do you want to filter CostDepartment field in the Expenses list based on TeamAccessCode column in Departments list?

Could you tell me how do you get this  TeamAccessCode column value in Departments list?

I assume that you use drop down to display TeamAccessCode column value and choose value in this drop down.

The drop down's Items for CostDepartment field will filter based on this selection

If so, try this:
1)drop down for TeamAccessCode:

set its Items:

Distinct(Departments,TeamAccessCode)

2)drop down for  CostDepartment:

Items:

Filter(Choices(Expenses.CostDepartment),
       Value in Filter(Departments,TeamAccessCode=DropDown1.Selected.Result).Title
       )

Then the value of this drop down will filter based on the selection of  TeamAccessCode.

Best regards,

[McMonty]

Hi,

I'm trying the CostDepartment field in the Expenses list is a lookup to the Departments list.

The TeamAccessCode field is in the Department list.

I have only 1 (one) dropdown in powerapps.

[v-yutliu-msft]

Hi @McMonty ,

Ok. Let me explain the feather that you want, do I understand right?

1)the CostDepartment field in the Expenses list is a lookup to the Title field in Departments list

2)a drop down for CostDepartment field in the Expenses list

3)choose one value in the drop down

4)the gallery will filter Departments list based on the selection value of the drop down( based on TeamAccessCode field)

If so, try this:

1)drop down's Items:

Choices(Expenses.CostDepartment)

2)gallery's Items:

Filter(Departments,TeamAccessCode in  
                   Filter(Departments,Title=DropDown1.Selected.Value).TeamAccessCode
       )

Best regards,