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

[debajitkiran]

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

[v-xida-msft]

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:

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

[debajitkiran]

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 - 

 

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 - 

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

Regards

Debajit

[debajitkiran · Accepted solution]

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

[basukunal001]

The formula worked for me. But I am unable to save the data. May I know what have you provided in Update property for the data cards?