Hello PowerApps community,
I am building PowerApps with 4 combo boxes. 'cmb_Make', 'cmb_Model', 'cmb_Color', 'cmb_Year'.
Users can select those 4 combo boxes in any order and each combo box should filter out the available items to select based on the user selection on the other combo boxes. (Like excel filter function with multiple columns)
Those Make, Model, Color, Year's combo-box available items to select come from the single SharePoint with text property as below:
For example, users can select Make, Color, Model, and Year order. Or Year, Model, Make then Color order. Or it can be selected in any other way.
(For Example,
- if Model combo-boxes are chosen with the value 'Civic' first then Make should show 'Honda' only and Color should 'Red,Yellow,Blue' option available...etc
- if Make chosen first as 'Honda' then use should able to see the Model as 'Civic' or 'Accord', then color as 'Red, Yellow, Blue, Black'...etc)
I wrote the function below but seems like it is not working properly. Looks like it is working okay when I select 'Make' or 'Model' first but not working well if I select 'Color' or 'Year' first.
Can someone please help?
(*Assume that SharePoint data can grow up, I want to avoid any delegation limit & circular reference issue)
1. App's on start
ClearCollect(col_Make, Distinct(Test_SharePoint_List, Make));
ClearCollect(col_Model, Distinct(Test_SharePoint_List, Model));
ClearCollect(col_Color, Distinct(Test_SharePoint_List, Color));
ClearCollect(col_Year, Distinct(Test_SharePoint_List, Year))
2. Each combo-box's Item Property
cmb_Make: col_Make
cmb_Model: col_Model
cmb_Color: col_Color
cmb_Year: col_Year
3. cmb_Make OnChange:
ClearCollect(
col_Model,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Make.Selected.Value) || Make = cmb_Make.Selected.Value)
),
Model
)
);
ClearCollect(
col_Color,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Make.Selected.Value) || Make = cmb_Make.Selected.Value) && (IsBlank(cmb_Model.Selected.Value) || Model = cmb_Model.Selected.Value)
),
Color
)
);
ClearCollect(
col_Year,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Make.Selected.Value) || Make = cmb_Make.Selected.Value) && (IsBlank(cmb_Model.Selected.Value) || Model = cmb_Model.Selected.Value) && (IsBlank(cmb_Color.Selected.Value) || Color = cmb_Color.Selected.Value)
),
Year
)
)
4. cmb_model onChange:
ClearCollect(
col_Make,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Model.Selected.Value) || Model = cmb_Model.Selected.Value)
),
Make
)
);
ClearCollect(
col_Color,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Make.Selected.Value) || Make = cmb_Make.Selected.Value) && (IsBlank(cmb_Model.Selected.Value) || Model = cmb_Model.Selected.Value)
),
Color
)
);
ClearCollect(
col_Year,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Make.Selected.Value) || Make = cmb_Make.Selected.Value) && (IsBlank(cmb_Model.Selected.Value) || Model = cmb_Model.Selected.Value) && (IsBlank(cmb_Color.Selected.Value) || Color = cmb_Color.Selected.Value)
),
Year
)
)
5. cmb_Color onChange:
ClearCollect(
col_Make,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Color.Selected.Value) || Color = cmb_Color.Selected.Value)
),
Make
)
);
ClearCollect(
col_Model,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Make.Selected.Value) || Make = cmb_Make.Selected.Value) && (IsBlank(cmb_Color.Selected.Value) || Color = cmb_Color.Selected.Value)
),
Model
)
);
ClearCollect(
col_Year,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Make.Selected.Value) || Make = cmb_Make.Selected.Value) && (IsBlank(cmb_Model.Selected.Value) || Model = cmb_Model.Selected.Value) && (IsBlank(cmb_Color.Selected.Value) || Color = cmb_Color.Selected.Value)
),
Year
)
)
6. cmb_Year onChange:
ClearCollect(
col_Make,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Year.Selected.Value) || Year = cmb_Year.Selected.Value)
),
Make
)
);
ClearCollect(
col_Model,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Make.Selected.Value) || Make = cmb_Make.Selected.Value) && (IsBlank(cmb_Year.Selected.Value) || Year = cmb_Year.Selected.Value)
),
Model
)
);
ClearCollect(
col_Color,
Distinct(
Filter(
Test_SharePoint_List,
(IsBlank(cmb_Make.Selected.Value) || Make = cmb_Make.Selected.Value) && (IsBlank(cmb_Model.Selected.Value) || Model = cmb_Model.Selected.Value) && (IsBlank(cmb_Year.Selected.Value) || Year = cmb_Year.Selected.Value)
),
Color
)
);
