[PowerApps + SharePoint] Dropdown 2 Filters Based on Dropdown 1 Selection

[david_castillo]

Hello, I am creating an app based on a SharePoint list which will act as the form in PowerApps. Let's call it List1. The list contains a business unit (dropdown) and a cost center (Single text field).

I have another list within the same SharePoint site and let's call it List2. List 2 is the list of the Cost Center as the Title, and next column is a text field called Business Unit.

What I want to achieve in PowerApps is that when I choose the business unit dropdown from List1, the Cost Center should display a dropdown that's filtered to match the results in List2 of whatever the business unit that was chosen from the dropdown.

Here is List1: http://uploads.technograffy.com/043P1M3U1G3x

Here is List2: http://uploads.technograffy.com/0s0o3N0V1n1O

Here is a screenshot of the app I'm making and the issue:

http://uploads.technograffy.com/1j3z411i1t3m

Here is what's in my code on the Cost Center:

http://uploads.technograffy.com/1R30271X0z0k

Hope that helps, and please let me know if there are questions!

David

[Rick72]

Hi @david_castillo,

What about something like:

ShowColumns(Filter(DB_CompanyCode, "Business Unit" = DataCardValue57_1.Selected.Value), "Cost Center")

You should use the internal names of "Business Unit" and  "Cost Center".

Rick

[david_castillo]

Thanks @Rick72, will check that out!

[tchin-nin]

Hi!

I think the problem on your CostCenter dropdown list is that your first parameter is a property, not a Data Source.

This should work better : Filter(DB_CompanyCode,CostCenterColumn=DataCardValue57_1.Selected.value).Title

More over, the Business Unit property on List1 is a Choice column that choices reflect every distinct BU listed on List2, am I right ?

If I am, and if every Business Unit MUST have at least one CostCenter, may I suggest you to go for a Single line of text instead of Choice, and build on your PowerApps a drop-down list that will be populated by every distinct values of Business Unit in List2. 

It will help you maintaining the list of BU -No duplicate informations, if any change happens you just have to update your List2, not both List2 and Choice list-
Also it will provide data consistency as you will compare the Business Unit value from the same list, no mistake possible.  

The formulas should look like :
Drop-down list Business Unit :
Distinct(List2,"BusinessUnitColumn")
Drop-down list Cost center :
Filter(List2,BusinessUnitColumn=dropdownBU.selected.value).CostCenterColumn

Cheers,

Théo

[v-xida-msft · Accepted solution]

HI @david_castillo,

Could you please share a bit more about the DataCardValue57_1 within the formula filled in the Cost Center drop down control? Does it represent the Business Unit drop down within your form?

I think there is something wrong with the formula that you provided within the Items property of the Cost Center drop down. I have made a test on my side, please modify your formula as below:

1. If the Title column has been renamed as "Cost Center":

Filter(DB_CompanyCode,Business_x0020_Unit=DataCardValue57_1.Selected.Value).Cost_x0020_Center

     2. If the Title column has not been renamed as "Cost Center":

Filter(DB_CompanyCode,Business_x0020_Unit=DataCardValue57_1.Selected.Value).Title

Note: I assume that DataCardValue57_1 represent the Business Unit drop down within your form.

More details about the Filter function in PowerApps, please check the following article:

Filter function

Best regards,

Kris