cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Filtering one combo-box items, based on another combo-box selected items

Hello 

 

I have a powerapps application connected to different Sharepoint lists. 

I have 2 master lists lists (Regions & Departments) & another list (Contacts) using these 2 master lists.

 

List Region

Column1 - Title (type: text)

 

List Department

Column1 - Title (type: text)

Column2 - Region (type:lookup, multisection)

 

List Contacts

Column1 - Title (type: text)

Column2 - Region (type:lookup, multisection)

Column3 - Department (type:lookup, multisection)

 

One Contact may have multiple Region & Deparement.

Now in contact search screen I want to load departlents based on selected region.

 

In Region combo-box I have Items = Choices(Contacts.Region).Value, which is laoding values properly 

In Department combo-box I have Items = Filter(Choices(Contacts.Departement),Value in cmb_Rgn.SelectedItems).Value

 

But no items are displayed in Department combo-box. It would be great help if anyone can help me in this issue.

Many thanks in advance. 

 

Regards

Debajit

1 ACCEPTED SOLUTION

Accepted Solutions

Hello Kris,

 

Your solution gave me pretty good idea how Table value is working as lookup. By twicking your solution a little bit , it worked perfectly for me - 

 

cmb_Region
Items : Choices([@Départements].Région)

OnChnage :
Clear(AvailableDepts);
Clear(FinalResult);
ForAll(cmb_Region.SelectedItems, Collect(AvailableDepts, Filter(Départements, Value in Région.Value)));
ForAll(AvailableDepts.Title,Collect(FinalResult, Title))

 

cmb_Dept
Items : FinalResult

 

Thanks

Debajit

 

 

View solution in original post

3 REPLIES 3
Community Support
Community Support

Hi @debajitkiran,

Could you please share a bit more about the Region column in your Contacts list? Is it a LookUp column which references values from Region list?

Why do you add a Region column in your Department SP list?

Do you want to limit the available options within the Department Combo Box based on the selected region?

Based on the formula that you provided, I think there is something wrong with it. The result the cmb_Rgn.SelectedItems formula returned is a Table value consists of Region, but the result the Choices(Contacts.Departement) formula returned is a Table value consists of Department, there is no direct way to compare Department table value with a Region Table value.

I have made a test on my side, please take a try with the following workaround:9.JPG

 

10.JPG

Set the Items property of the Region Combo Box (ComboBox1) to following:

Choices('20181211_case9_Contacts'.Region).Value

On your side, you should type:

Choices(Contacts.Region).Value

Set the OnChange property of the Region Combo Box (ComboBox1) to following:

Clear(AvailableDepts);
Clear(FinalResult);
ForAll(ComboBox1.SelectedItems,Collect(AvailableDepts, Filter('20181211_case9_Contacts', Value in Region.Value).Department));
ForAll(AvailableDepts.Department,Collect(FinalResult, Department.Value))

On your side, you should type:

Clear(AvailableDepts);
Clear(FinalResult);
ForAll(cmb_Rgn.SelectedItems, Collect(AvailableDepts, Filter(Contacts, Value in Region.Value).Department));
ForAll(AvailableDepts.Department,Collect(FinalResult, Department.Value))

Set the Items property of the Department Combo Box (ComboBox2) to following:

Filter(
Choices('20181211_case9_Contacts'.Department),
Value in Distinct(FinalResult,Value)
)

On your side, you should type:

Filter(
Choices(Contacts.Department),
Value in Distinct(FinalResult,Value)
)

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Kris,

 

Thanks a lot for your detailed reply. I would like to provide you the answers - 

 

Q1 : Could you please share a bit more about the Region column in your Contacts list? Is it a LookUp column which references values from Region list? 

Ans1 : Yes in Contacts list I have used 2 lookup columns Region & Department. Structure is like below - 

 list_details.PNG

 

Q2 : Why do you add a Region column in your Department SP list?

Ans1 : Because its like Continent & Country relation. 

For example - In Regions list I have different regions of France - Alsace, Bretagne, Normandie etc. 

In Department list we have different departments & their region mapping , like - 

 

region_dept_mapping.PNG

 

 

I am now going to try your solution & let you know if this is working for me.

 

Regards

Debajit

Hello Kris,

 

Your solution gave me pretty good idea how Table value is working as lookup. By twicking your solution a little bit , it worked perfectly for me - 

 

cmb_Region
Items : Choices([@Départements].Région)

OnChnage :
Clear(AvailableDepts);
Clear(FinalResult);
ForAll(cmb_Region.SelectedItems, Collect(AvailableDepts, Filter(Départements, Value in Région.Value)));
ForAll(AvailableDepts.Title,Collect(FinalResult, Title))

 

cmb_Dept
Items : FinalResult

 

Thanks

Debajit

 

 

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Solution Authors
Top Kudoed Authors
Users online (93,786)