Cascading distinct dropdowns in large SharePoint list that have assigned default values (Collections vs. column filter)

[DublinOH_User]

First - thank you for your time.

These columns are all in the same a single SharePoint table that has 8000 rows - so delegation is a factor

We have a gallery that we need to 3 dropdowns to filter a gallery in any combination

1. Each should filter based on the other two's selected value
• (Column 1 sets distinct/filtered choices for Column 2 & Column 3,  - Column 2 sets distinct/filtered choices for Column 1 & Column 3, and so on & so on)
2. There needs to be a default value in each dropdown list so the gallery can show all results 
3. There also needs to be a condition that if only one of the dropdowns is selected the other two dropdowns filter and are distinct but, no action in the gallery except the selected value filter

All three collections for dropdowns are set similar (currently on app start)

ClearCollect(collectPlantsOvNx,{Result: "ALL PLANTS"});Collect(collectPlantsOvNx,Sort((Distinct('PF_PM_ Compliance_OverNext30','PLANT CODE')),"PLANT_x0020_CODE",Ascending));

'DrpdwnPlantCd_PM' items are set to collectPlantsOvNx

This gallery items works:

//Default - All dropdowns set to CLEAR or default

Filter('PF_PM_ Compliance_OverNext30',

'SERVICE COMPLETED DATE' = Blank(),

//Selected Plant with selected PM and selected Units

Or('PLANT CODE'=DrpdwnPlantCd_PM.Selected.Result,DrpdwnPlantCd_PM.Selected.Result="ALL PLANTS"),

Or('UNIT#'=DrpdwnUNIT_PM.Selected.Result,DrpdwnUNIT_PM.Selected.Result="ALL UNITS"),

Or('NAME OF PM'=DrpdwnName_PM.Selected.Result,DrpdwnName_PM.Selected.Result="ALL PM TYPES"))

ISSUE

When collections are used so we can establish default values, we can't filter or create the distinct cascade in the other dropdowns based on the first selection.

So when the First dropdown for Plants is selected  - the other two dropdowns are cascading. The choices in the remaining dropdowns do not make sense and cause blank results in the form connected to the gallery confusing the end-user.

When I tried filtering the table column - I did not know how add a default value so when the 'clear' dropdowns was selected the gallery shows all items

Is there are better approach?

Or

How do we make the dropdown collection results dependent on one another?

[Drrickryp]

@DublinOH_User 

The screenshot below shows how to filter a large SharePoint list by using 3 separate dropdowns and  a textinput box for month.  No collections are used and there are no delegation warnings.  The Items property of one of the dropdowns is shown in the upper left side.  A blank is included in each dropdown and when that is chosen, that part of the filter is ignored.  If all three are left blank, then the entire list is shown in the datatable. 

[DublinOH_User]

Thank you for the reply.
The drop-down items property did not quite work. I was perplexed by the table name in the 'Distinct' section of the formula in the example provided above.  

[Drrickryp]

@DublinOH_User 

Here is another way to obtain an Items property for a dropdown function with a blank as the first item:

With(
         {
          _items: Distinct(
                                  YourListNamehere,
                                   YourColumnNameHere
                       )
            },
  ForAll(
           Sequence(CountRows(_items) + 1),
                    {
                        Value: If(
                                     Value = 1,
                                       Blank(),
                                      Last(FirstN(_items,Value-1)).Result
                                  )
                      }
    )
)

[Drrickryp]

@DublinOH_User 

Here is another way to obtain an Items property for a dropdown function with a blank as the first item:

With(
         {
          _items: Distinct(
                                  YourListNamehere,
                                   YourColumnNameHere
                       )
            },
  ForAll(
           Sequence(CountRows(_items) + 1),
                    {
                        Value: If(
                                     Value = 1,
                                       Blank(),
                                      Last(FirstN(_items,Value-1)).Result
                                  )
                      }
    )
)

For the original formula only change the highlighted text and replace it with your list and column names.  The rest should be left as it is written. MyTables is just a construct to be created and ungrouped inside the formula. 

Ungroup(
             Table(
                        {MyTables: Table({Result: "All"})},
          {
            MyTables: RenameColumns(
                                                     Distinct(
                                                           YourListHere,
                                                           YourColumnHere
                                                        ),
                                                         "Result",
                                                          "Value"
                                   )
           }
       ),
          "MyTables"
)

Once you have the dropdowns created, the real key to filtering the gallery (or datatable) is here

Filter(
       YourList,
       IsBlank(Dropdown1.Selected.Value) || Segment = Dropdown1.Selected.Value,
       IsBlank(Dropdown2.Selected.Value) || Product = Dropdown2.Selected.Value,
       IsBlank(Dropdown3.Selected.Value) || Country = Dropdown3.Selected.Value,
       IsBlank(TextInput1.Text) || StartsWith(YourColumnName,TextInput1.Text
 )

In the example, Segment, Product and Country are column names. You can add a text box to further filter the list using a StartsWith() function.

[DublinOH_User]

I was able to obtain the default value, but then there were no other items returned in the dropdown list.
There is another factor we hope to achieve: These combo boxes need to cascade.
Each combo box selected value should filter the results available in the other two dropdowns

1. When a Plant code or codes are selected- the Unit# and PM Name should filter to only those for that plant code
2. When a Unit# or numbers are selected- the Plant code and PM Name should filter to only those for that Unit# or numbers
3. When a PM Name/s are selected- the Unit# and Plant codes should filter to only those for that plant code

Appreciate your help

[Drrickryp]

@DublinOH_User 

When you are using a combobox you need to code it differently than when you use dropdown control.  This format is required for filtering the datasource using a multiselect combobox.  It accounts for the default empty property as well as the result of choosing items and then removing them from the combobox. 

 Filter(
        YourTable,
        IsEmpty(ComboBox1.SelectedItems) ||
        IsBlank(ComboBox1.SelectedItems)|| 
        YourColumn in ComboBox1.SelectedItems
 )

You cannot avoid a delegation warning though since the "in" operator is required. 

[DublinOH_User]

That makes sense for the gallery items.
 What is the formula for the other two dropdowns to filter to only the options for the selection present in the first?
How do we filter the dropdowns options?

[DublinOH_User]

After talking it over - we have decided it is best to use one selection dropdowns and not offer the end users combo boxes
We only need to filter each dropdowns based on the selection of the other two, and still maintain the option for a blank or default value (which we have from the above suggestions)

[DublinOH_User]

How does this look?

Ungroup(
Table(
{MyTables: Table({Result: "ALL UNITS"})},
{
MyTables: RenameColumns(
Distinct(
Filter(
'PF_PM_ Compliance_OverNext30',
'PLANT CODE' = DrpdwnPlantCd_PM_1.Selected.Result
),
'UNIT#'
),
"Result",
"Value"
)
}
),
"MyTables"
)