Solved: Combo Boxes Depend on each other/Circural Reference

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!

Categories:

General Questions

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 -

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.
Collect the user selected filters into a collection called collectFilters.
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;