Combo Boxes Depend on each other/Circural Reference

[Anonymous]

Good day, 

I am trying on combo boxes selections depend on all other combo boxes.

For example, 5 combo boxes: A,B, C ,D, E. Let's say we select combo box in the sequence below:

1. ComboBoxC
2. ComboBoxB

3. ComboBoxD

So the selection of B is depends on C, the selection of D is depends on C and B.

For the combo box is not selected, it should show whole table.

Each Combo Box OnChange property = 

Set(VariableName, ComboBox.SelectedItems.ColumnName)

Each Combo Box Item property =

Filter
(
TableName,
IF(isEmpty(ComboBoxA), true, VariableNameA in ColumnA),
IF(isEmpty(ComboBoxB), true, VariableNameB in ColumnB),
.
.
.
)

In some Item Property, I use "ComboBox.SelectedItems" and some I use VariableName, because I got the error of circular reference.

Although it doesn't show any error, but all combo boxes are empty selections now.

Any idea on making all combo boxes able to depend on each other?

Thank you so much!

[RusselThomas]

Hi @Anonymous ,

Firstly, let me say that I wish we had an updated Gallery or Data Grid Control that supported dynamically nested filter columns.

Creating one manually is quite literally a massive pain and, for some use cases - just plain impossible.

If I'm right, you're asking for any multiple combinations of multiple filters to ultimately filter a Gallery result.  This is an exercise in dynamic exponential logic, so be prepared a) for long and nested Filter functions and potentially b) a slow app.

Consider this:

• The Filter() function processes each logical test using the AND logical function - meaning ALL the tests must be true for a record before it is displayed.  If, for example, your second combo selection is empty and the first is not, only those records that have the first combo column value AND an empty second column value would match.
• To avoid this, you have to nest your filters - so the second Filter check is done against the result of the first Filter.  Here, you get locked into the order in which you evalute each them, which is not ideal from a user experience perspective.
• To avoid that, you need to have a user-selected order to evaluate against - and to do that, you need to record the order in which the user selects their filters and then execute those filters in that order - which just adds more complexity and pain.
• The only way (I know of anyway) to achieve this with any degree of success is to statically tie filter buttons to create entries in a collection for their specific filter against an indexed order of filters.  Would be very happy for someone to correct me here.
• Your actual Gallery filter result then would have to statically check each filter entry in the dynamic filter list against every single column in the full data set to figure out which column you're trying to filter, and then apply the filter to that.
• The end result is a very, very long and cumbersome formula which is massively painful to maintain - against which I usually ask myself - does the user really need this functionality, or can I present the data in a better way? - and the answer here is usually "No" and "Yes" respectively.

Add to that, every column you add as a potential filter exponentially increases your formula code as it needs to include all previous filters - so there's that.

Maye someone else has a better approach  - but now that I've hopefully convinced you not to do this - here's how I would do it - 

1. Limit number of concurrent active filters (let's say three in this instance) - that way you're only ever trying to figure out if a filter has been applied to one, two or three of these columns - you can exceed this if you're brave, but you'll run out of formula space eventually.  You can consider collecting each filter result into a separate collection to go full hog - but eugh.
2. Collect the user selected filters into a collection called collectFilters.
3. Dynamically filter the output Gallery by 1, 2 or 3 filters respectively - in any combination of filter, but in the specific order they occur in collectFilters.

The full data set is in myTable as follows;

The order of the columns doesn't matter, just the highlighted ones will be filtered in whichever order the user creates their filters.  The order of the filters the user chooses will be stored in collectFilters and looks like this;

• The filterColumn is a text reference to the column name we'll use to test and get the actual column object.
• The filterStrings are the selected values to filter from that specific combobox
• The filterIndex is the order in which the filter was selected by the user.

Combo Box 1 filters "Column1" of the data.  Create the first ComboBox and call it ComboFilterColumn1 - Set Items: to the data source (in this case, myTable) and the DisplayFields: and SearchFields: properties to ["Column1"], then set it's OnChange: property to 

If(!IsEmpty(ComboFilterColumn1.SelectedItems), //if this combobox is empty, just remove all entries related to it from the collection - if not.......
    If(
        IsEmpty(Filter(collectFilters, filterColumn="Column1")), //if there is currently no line for this combobox in the collection, add it
        Collect(collectFilters, {
            filterColumn: "Column1", 
            filterStrings: Concat(ComboFilterColumn1.SelectedItems, Column1, ";"), 
            filterIndex: Last(SortByColumns(collectFilters, "filterIndex", Ascending)).filterIndex + 1
            }),
        Patch(collectFilters, LookUp(collectFilters, filterColumn="Column1"), { //if there is a line for this combobox, then patch it
            filterStrings: Concat(ComboFilterColumn1.SelectedItems, Column1, ";") 
            })
    ),
    RemoveIf(collectFilters, filterColumn="Column1")
)    

Seems easy enough - except you need to do this for three comboboxes, so go ahead and add another two, call them ComboFilterColumn2 and ComboFilterColumn3 respectively and update their items to myTable and DisplayFields and SearchFields to their respective columns and set their Onchange: properties accordingly.

ComboFilterColumn2 Items: myTable

ComboFilterColumn2 DisplayFields & SearchFields: ["Column2"]

ComboFilterColumn2 Onchange:

If(!IsEmpty(ComboFilterColumn2.SelectedItems),
    If(
        IsEmpty(Filter(collectFilters, filterColumn="Column2")),
        Collect(collectFilters, {
            filterColumn: "Column2", 
            filterStrings: Concat(ComboFilterColumn2.SelectedItems, Column2, ";"), 
            filterIndex: Last(SortByColumns(collectFilters, "filterIndex", Ascending)).filterIndex + 1
            }),
        Patch(collectFilters, LookUp(collectFilters, filterColumn="Column2"), {
            filterStrings: Concat(ComboFilterColumn2.SelectedItems, Column2, ";") 
            })
    ),
    RemoveIf(collectFilters, filterColumn="Column2")
)

ComboFilterColumn3 Items: myTable

ComboFilterColumn3 DisplayFields & SearchFields: ["Column3"]

ComboFilterColumn3 OnChange:

If(!IsEmpty(ComboFilterColumn3.SelectedItems),
    If(
        IsEmpty(Filter(collectFilters, filterColumn="Column3")),
        Collect(collectFilters, {
            filterColumn: "Column3", 
            filterStrings: Concat(ComboFilterColumn3.SelectedItems, Column3, ";"), 
            filterIndex: Last(SortByColumns(collectFilters, "filterIndex", Ascending)).filterIndex + 1
            }),
        Patch(collectFilters, LookUp(collectFilters, filterColumn="Column3"), {
            filterStrings: Concat(ComboFilterColumn3.SelectedItems, Column3, ";") 
            })
    ),
    RemoveIf(collectFilters, filterColumn="Column3")
)   

If you're still with me and haven't jumped out the window yet - this should have set up your filter collection.  If you like, you can pop a gallery onto your page and set it's items to collectFilters and see how it behaves when you add and remove filters.

If you thought this was painful, wait until you see the Gallery Items filter function.

Add your Gallery, set it's Items: property to;

If(!IsEmpty(collectFilters),
    Switch(CountRows(collectFilters),
    1, 
        Filter(myTable,
            Switch(
                First(collectFilters).filterColumn,
                    "Column1", Column1,
                    "Column2", Column2,
                    "Column3", Column3
            ) in Split(First(collectFilters).filterStrings, ";").Result),
    2, 
        Filter(
            Filter(myTable,
            Switch(
                First(collectFilters).filterColumn,
                    "Column1", Column1,
                    "Column2", Column2,
                    "Column3", Column3
            ) in Split(First(collectFilters).filterStrings, ";").Result), 
            Switch(
                Last(collectFilters).filterColumn,
                    "Column1", Column1,
                    "Column2", Column2,
                    "Column3", Column3
            ) in Split(Last(collectFilters).filterStrings, ";").Result),
    3, 
        Filter(
            Filter(
                Filter(myTable,
                Switch(
                    First(collectFilters).filterColumn,
                        "Column1", Column1,
                        "Column2", Column2,
                        "Column3", Column3
                ) in Split(First(collectFilters).filterStrings, ";").Result), 
                Switch(
                    Last(FirstN(collectFilters, 2)).filterColumn,
                        "Column1", Column1,
                        "Column2", Column2,
                        "Column3", Column3
                ) in Split(Last(FirstN(collectFilters, 2)).filterStrings, ";").Result),
        Switch(
                Last(collectFilters).filterColumn,
                    "Column1", Column1,
                    "Column2", Column2,
                    "Column3", Column3
            ) in Split(Last(collectFilters).filterStrings, ";").Result)
    )
            
        , myTable)

Three is also a handy limit for detecting how many filters to test for - using First() for 1, Last() for 2 and Last(FirstN,2) to pick up the middle one.  You can add more combinations to pick up more, but hopefully you've already decided to go another route, or just limit the filters to three.

As I said, each additional iteration of a filter column will exponentially increase this formula;

• 2 includes 1. 
• 3 includes 2 & 1. 
• 4 will include 3, 2 & 1. 
• 5 will include 4, 3, 2 & 1

so unless you start collecting the results into separate collections and filtering those, you'll run out of formula space pretty soon.

Hope this helps,

RT

[v-xida-msft · Accepted solution]

Hi @Anonymous ,

Could you please share a bit more about your scenario?

Do you want each ComboBox to be dependent on other 4 ComboBoxes in your app?

Further, could you please show more details about the 'Circural Reference' error within your app?

Based on the formula you provided, I think there is something wrong with it. I have made a test on my side, please take a try with the following workaround:

Set the OnChange property of the ComboBoxA to following:

ClearCollect(ComboBoxACol, ComboBoxA.SelectedItems.ColumnName)

Set the OnChange property of the ComboBoxB to following:

ClearCollect(ComboBoxBCol, ComboBoxB.SelectedItems.ColumnName)

Set the OnChange property of the ComboBoxC to following:

ClearCollect(ComboBoxCCol, ComboBoxC.SelectedItems.ColumnName)

Set the OnChange property of the ComboBoxD to following:

ClearCollect(ComboBoxDCol, ComboBoxD.SelectedItems.ColumnName)

Set the OnChange property of the ComboBoxE to following:

ClearCollect(ComboBoxECol, ComboBoxE.SelectedItems.ColumnName)

Set the Items property of above each ComboBox to following:

Filter(
       TableName,
       If(IsEmpty(ComboBoxACol), true, ColumnA in ComboBoxACol),              /* <-- Type  ColumnA in ComboBoxACol rather than  ComboBoxACol in ColumnA */
       If(IsEmpty(ComboBoxBCol), true, ColumnB in ComboBoxBCol),
       If(IsEmpty(ComboBoxCCol), true, ColumnC in ComboBoxCCol),
       If(IsEmpty(ComboBoxDCol), true, ColumnD in ComboBoxDCol),
       If(IsEmpty(ComboBoxECol), true, ColumnE in ComboBoxECol)
)

Please take a try with above solution, then check if the issue is solved.

Best regards,

[Anonymous]

@RusselThomas 

Thanks a lot on your idea, explaination and steps with guidances in detailed. I believe you are strong on PowerApps coding!

I found that I did some mistakes in my formula, so I referred to the answer from @v-xida-msft above and made the corrections. I managed to get the result I want now.

It should have similar result with your idea. It doesn't record the sequence of selected combo boxes but it can be selected without recording. It's my pleasure to read your ideas and coding. I learnt something! Thank you very much!

@v-xida-msft 

Thank you for your answer! I change Set() to ClearCollect() and some modification, it works now!

Except the below:

Set the OnChange property of the ComboBoxA to following:

ClearCollect(ComboBoxACol, ComboBoxA.SelectedItems.ColumnName)

I didn't write ".ColumnName", only add this part into the combo box Item property. Appreciate if you can advise which one is more efficient.

Thanks a lot!