Filtering choice choice dropdown, based on prior choice drop down decisions made by user

(0) Share

Report

Posted on by mcnemare1

214

Hello.

I have a powerapp connected to 1 SP list.

This list contains the fields

[Status](choice), [Branch](choice), [Biz_Pers](choice),[Product_Type](choice),[Product](choice), [URL](text)

I need the drop downs from top down to filter one another in case certain areas do no have certain products.

For example, let's say our Dublin Branch does not offer Certificates, but our Grove City Branch does.

I want the future drop downs results to be affected by the end-user's choices at the beginning.

Here are the items property for each of the drop downs

Branches

Choices([@'PURL_LINKS_BETA-fbccd597-30be-4015-a0c1-53a998f29b39'].Branch)

Personal or Business

Choices([@'PURL_LINKS_BETA-fbccd597-30be-4015-a0c1-53a998f29b39'].Biz_Pers)

Product Type

Choices([@'PURL_LINKS_BETA-fbccd597-30be-4015-a0c1-53a998f29b39'].Product_Type)

Product

Choices([@'PURL_LINKS_BETA-fbccd597-30be-4015-a0c1-53a998f29b39'].Product)

I tried a few methods of filtering before with no luck! any advice appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Sort by

Verified answer

WarrenBelz 155,949 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @mcnemare1 ,
That is because the Choices have no relationship in their Table with anything else. You can to this, but will have Delegation issues on larger lists - the data fields can revert to Text (a good idea anyway) - Note you will need to replace the drop-down names from my markers.
Branch

Sort( Distinct( [@'PURL_LINKS_BETA-fbccd597-30be-4015-a0c1-53a998f29b39'], Branch ), Result );

Personal or Business Items

Sort( Distinct( Filter( [@'PURL_LINKS_BETA-fbccd597-30be-4015-a0c1-53a998f29b39'], Branch = ddBranch.Selected.Result ), Biz_Pers ), Result );

Product Type

Sort( Distinct( Filter( [@'PURL_LINKS_BETA-fbccd597-30be-4015-a0c1-53a998f29b39'], Branch = ddBranch.Selected.Result && Biz_Pers = ddBizPers.Selected.Result ), Product_Type ), Result );

Product

Sort( ShowColumns( Filter( [@'PURL_LINKS_BETA-fbccd597-30be-4015-a0c1-53a998f29b39'], Branch = ddBranch.Selected.Result && Biz_Pers = DDBizPers.Selected.Result && Product_Type = ddProduct_Type.Selected.Result ), "Product" ), Product );

OR you can do what I do and have a "Master" list containing the Choice possibilities, and replace the list name in the above with this.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Sahar-Kalif 20 on at

Like
a
(0)

Report
Copy link

Link copied!

HI,
It a problem because you don't have Key to compare one choice column to another
What I do is using Single line of text in SP and create new list that will contains the connection between columns

You can see explanation in these articles:
https://www.matthewdevaney.com/create-cascading-dependent-dropdowns-in-power-apps/
https://evolvous.com/learn-from-expert/cascading-dropdowns-in-powerapps/

ect.

Was this reply helpful? Yes No
mcnemare1 214 on at

Like
a
(0)

Report
Copy link

Link copied!

@Sahar-Kalif and @WarrenBelz

thanks for your help, the list I am using, I did create a key value (more or less an index) to separate all of the products (see below)

The PURL ID would be the unique key per product, how does that change my approach?

Was this reply helpful? Yes No
WarrenBelz 155,949 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

@mcnemare1 ,

Is this a static list and if so, how many records are in it ?

The point I was making is that (for a number of reasons) hard-coded Choice columns are possible to use in some cases, but are far from the best solution to what you are trying to do. Did you try the code I posted ?

Was this reply helpful? Yes No
mcnemare1 214 on at

Like
a
(0)

Report
Copy link

Link copied!

It's static. It'll be a list of each product available at each branch location. Those don't change that often.

Right now there are 60 records since I am using 2 branches as test subjects.

potentially 600 records at full release.

Was this reply helpful? Yes No
mcnemare1 214 on at

Like
a
(0)

Report
Copy link

Link copied!

It's static. It'll be a list of each product available at each branch location. Those don't change that often.

Right now there are 60 records since I am using 2 branches as test subjects.

potentially 600 records at full release.

let me change the code to what you posted

Was this reply helpful? Yes No
WarrenBelz 155,949 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @mcnemare1 ,

Then as long as you set your Delegation limit (standard 500 but can be increased to up to 2,000) over the list record number, then the code I posted should work for you. Simply put the Choice fields back to Single Line of text and use the code on drop-downs or combo boxes. You will need to change the Update of the Data Card to reflect the output of the new control.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
mcnemare1 214 on at

Like
a
(0)

Report
Copy link

Link copied!
This worked for me, for Product: I had to use "Distinct", instead of "Choose Column" though.

Sort( Distinct( Filter( [@'PURL_LINKS_BETA-fbccd597-30be-4015-a0c1-53a998f29b39'], Branch = BranchDropDown.Selected.Result && Biz_Pers = BusinessOrPersonalDropDown.Selected.Result && Product_Type = ProductTypeDropdown.Selected.Result ), Product ), Result )

Was this reply helpful? Yes No