How to Get Dropdowns to Cross-Filter Eachother Without Circular Reference Error

[dk2311]

Hello,

I currently have an app that features 10 Dropdowns and 1 Gallery, and the user can use any combination of those 10 Dropdowns to successfully filter the Gallery. I have received feedback from the users, however, that the Dropdowns are tough to use because there are so many values in some, and a selection in 1 Dropdown doesn't affect the options in subsequent Dropdowns.

For example: If I had 3 dropdowns only; 1 for CustomerID, 1 for SalesRep, and 1 for SaleDate, I would want the selection of a single SalesRep to filter the CustomerID Dropdown to only show CustomerID's where the selected Sales Rep was on the account.

I adjusted the "Items" formula to account for the selection of the other Dropdowns but that causes a Circular Reference error. Here is the formula I am using for the CustomerID Dropdown:
Distinct(Filter('Table',(SalesRep = 'SalesRepComboBox'.Selected.Value || 'SalesRepComboBox'.Selected.Value = Blank()) && (InstallDate = 'InstallDateComboBox'.Selected.Value || 'InstallDateComboBox'.Selected.Value = Blank())),CustomerID)

I understand in theory why I'm getting this error, just don't know how to work around it. I have looked up possible solutions and I see a lot of people recommending "Cascading" dropdowns where you require the user to select one after another in a specific order, but this doesn't work for my use case. The user needs the ability to use any Dropdown at any time, regardless of order. 

Thanks for the help!

[WarrenBelz]

Hi @dk2311 ,

Can you please supply (in Text), the Items of all three drop-downs. 

[Bartelli]

Reza Doranni has a GREAT video explaining how to set this up.

https://youtu.be/Qzpjq008cBY?si=erWvnsML7_g5N6R4

If this is helpful, please like this post and indicate if it resolved your issue.  Thanks!

[dk2311]

Hi @WarrenBelz 

The items in the SaleDate drop down would be 1 weeks worth of dates, but these would change over time. The items in the CustomerID dropdown would be 7 digit IDs (ex. 1234567). There would be hundreds, so wouldn’t make sense to paste here. The items in the SalesRep dropdown would be personal names and there are hundreds, so again, doesn’t make sense to paste them all here. 

[dk2311]

Hi @Bartelli 

Thank you for the response, however this is exactly what I was referring to when I mentioned I had seen possible solutions for “cascading” dropdowns. His video only shows you selecting values going down the list, not in reverse. My scenario requires all Dropdowns to be able to filter each other, regardless of order (so, in Reza’s example, a selected value in the City dropdown would filter the State dropdown). 

[dk2311]

Sorry @WarrenBelz , realizing now that I misinterpreted your comment. Here is what I have for the Items code:

comboBoxCustomerID: Distinct( Filter( 'table', Commission_or_Override = "Commission" && ('comboBoxSalesRep'.Selected.Value = Payroll_Employee || 'comboBoxSalesRep'.Selected.Value = Blank()) && ('comboBoxSaleDate'.Selected.Value = SaleDate || 'comboBoxSaleDate'.Selected.Value = Blank()) ), CustomerID )

comboBoxSalesRep: Distinct( Filter( 'table', Commission_or_Override = "Commission" && ('comboBoxSaleDate'.Selected.Value = SaleDate || 'comboBoxSaleDate'.Selected.Value = Blank()) && ('comboBoxCustomerID'.Selected.Value = CustomerID || 'comboBoxCustomerID'.Selected.Value = Blank()) ), Payroll_Employee )

comboBoxSaleDate: Distinct( Filter( 'table', Commission_or_Override = "Commission" && ('comboBoxSalesRep'.Selected.Value = Payroll_Employee || 'comboBoxSalesRep'.Selected.Value = Blank()) && ('comboBoxCustomerID'.Selected.Value = CustomerID || 'comboBoxCustomerID'.Selected.Value = Blank()) ), SaleDate )

[Bartelli]

10 dropdowns are an insane amount of work on your users and a space hog. Can any of them be eliminated or filtered based on the current user (like onStart)? 

I don't know of a way to cross-filter the dropdowns without establishing some sort of order of operations. CustomerID, SalesRep and Sales Date begins the process of reducing the population of results for the user to choose from in the subsequent dropdowns.

Something to think about.

[WarrenBelz · Accepted solution]

Hi @dk2311 ,

Every element of all the filters creates a circular reference as they are dependant on an value that ends up coming back to the same control. As you noted, this model simply will not work.
One path would be to set a Variable OnChange of each Combo Box then base the others on this value

comboBoxCustomerID:
OnChange

UpdateContext({varCustomer: Self.Selected.Value})

Items

Distinct( 
   Filter( 
      'table', 
      Commission_or_Override = "Commission" && 
      (
         Payroll_Employee = varSalesRep ||
         varSalesRep = Blank()
      ) && 
      (
         SaleDate = varSaleDate ||
         varSaleDate = Blank()
      ) 
   ), 
   CustomerID 
)

comboBoxSalesRep
OnChange

UpdateContext({varSalesRep: Self.Selected.Value})

Items

Distinct( 
   Filter( 
      'table', 
      Commission_or_Override = "Commission" && 
      (
         SaleDate = varSaleDate ||
         varSaleDate = Blank()
      ) && 
      (
         CustomerID = varCustomer || 
         varCustomer = Blank()
      ) 
   ), 
   Payroll_Employee 
)

comboBoxSaleDate:
OnChange

UpdateContext({varSaleDate: Self.Selected.Value})

 Items

Distinct( 
   Filter( 
      'table', 
      Commission_or_Override = "Commission" && 
      (
         Payroll_Employee = varSalesRep ||
         varSalesRep = Blank()
      ) && 
      (
         CustomerID = varCustomer || 
         varCustomer = Blank()
      ) 
   ), 
   SaleDate 
)

Also at Screen OnVisible (or anywhere else you need to reset this)

UpdateContext(
   {
      varCustomer: Blank(),
      varSalesRep: Blank(),
      varSaleDate: Blank()
   }
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[dk2311]

Sir, you are a genius. This works great, thank you!