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
Solved! Go to 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
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
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
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
User | Count |
---|---|
260 | |
110 | |
97 | |
56 | |
39 |