cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DavidWestgate
Level: Powered On

Cascading 3 Combo/Drop boxes with Multiple SQL Tables

Hi All
Great to be learning here with you all. I have a scenario I am trying to figure out. I have followed all of Shane Young and some of Darogael , all great content.
 
A Single Goal, will have multiple Strategies and a Strategy will have multiple Tactics. I would like to create progress updates for each Tactic in the records Table, with a timestamp so we can report on changes over a month, a quarter, a year, etc.
I have 3 main tables viz, Goals, Strategies, Tactics and then we have a Records Table.
Goals
Goal_Id (PrimaryKey)
Goal_Name
Goal_Description
Strategies
Strat_Id (PrimaryKey)
Strategy_Name
Strategy_Description
Goal_Id <--related to Goals.Goal_Id-->
Tactic_Id
Tactics
Tactic_Id
Tactic_Name
Tactic_Description
Workstream_Id (PrimaryKey)
Records
Record_Id (PrimaryKey)
Goal_ID
Strategy_ID <--related to Strategy.Strat_Id-->
Tactic_ID <--related to Tactics.Tactic_Id-->
WorkStream_ID
Comments
Strart_date
Update_date
Completed_date
Status_ID
 
I have 2 Comboboxes (and have tried with DropDownBoxes) named ComboBoxGoalFilter and ComboBoxStrategyFilter. ComboBoxGoalFilter Items is 'Goals' and ComboBoxStrategyFilter Items is Filter('Strategies', Goal_Id=ComboBoxGoalFilter.Selected.Goal_Id). The Strategies are filtered correctly according to the selected Goal_Id. so Cascading ComboBoxes are Working.
 
I then have a Gallery (GalleryTactics) that I would like to display the related Tactics to the Selected ComboBoxStrategyFilter (which is filtered by the related goals).
This works fine for the First Goal_Id, but as soon as I select the second Goal_Id, the results will appear for a second and then disappear.
The various code I have tried offers various results, but none are the desired result.
ShowColumns(Filter('[dbo].[Tactics]', Strategy_Id=ComboBoxStrategyFilter.Selected.Strategy_Id && Goal_Id=ComboBoxGoalFilter.Selected.Goal_Id), "Tactic_Name","Goal_Id","Strategy_Id", "Tactic_Id") - not Working - or
(Filter('[dbo].[Tactics]', Strategy_Id=ComboBoxStrategyFilter.Selected.Strategy_Id && Goal_Id=ComboBoxGoalFilter.Selected.Goal_Id) only works for Goal_Id=1 and Strat_Id=1-7. Goal_Id=2,3,4,5 does not work.
Please, can someone point me in the right direction?
Bonus Question - Do I use Patch Formulae or just a Form?
 
thanks
David
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Cascading 3 Combo/Drop boxes with Multiple SQL Tables

Filter('[dbo].[Tactics]', Strategy_Id=2)

 

Does that work? I think you have a data problem, not a logic problem at this point. 

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Cascading 3 Combo/Drop boxes with Multiple SQL Tables

Hey David - Do those formulas work to return items?

 

Filter('[dbo].[Tactics]', Strategy_Id=ComboBoxStrategyFilter.Selected.Strategy_Id) 


Filter('[dbo].[Tactics]', Goal_Id=ComboBoxGoalFilter.Selected.Goal_Id) 

 

 

 

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training
DavidWestgate
Level: Powered On

Re: Cascading 3 Combo/Drop boxes with Multiple SQL Tables

Hi Shane

Yes for Goal 1 no for Goal 2, 3,4,5. when selecting goal 1, the Strats are populated correctly, and the then the gallery is populated with the correct tactics. When I select goal 2, the strategies are populated correctly, but the gallery is void of any tactic data.

 

Goal 1 Strat 7.pngGoal 2 Strat 9.png

DavidWestgate
Level: Powered On

Re: Cascading 3 Combo/Drop boxes with Multiple SQL Tables

Apologies, misread that.

If I use Filter('[dbo].[Tactics]', Strategy_Id=ComboBoxStrategyFilter.Selected.Strategy_Id) It works for goal 1 no problem, but not the other goals.
 
If I use Filter('[dbo].[Tactics]', Goal_Id=ComboBoxGoalFilter.Selected.Goal_Id) it works for all the Goals correctly but obviously no filter on the Strategy. so I do get all the related tactics per Goal.

 

If have then tried to combine the two in the following;

(Filter('[dbo].[Tactics]', Goal_Id=ComboBoxGoalFilter.Selected.Goal_Id && Strategy_Id=ComboBoxStrategyFilter.Selected.Strategy_Id),Tactic_Name)

 

no joy though. 

 

Super User
Super User

Re: Cascading 3 Combo/Drop boxes with Multiple SQL Tables

Filter('[dbo].[Tactics]', Strategy_Id=2)

 

Does that work? I think you have a data problem, not a logic problem at this point. 

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training

View solution in original post

DavidWestgate
Level: Powered On

Re: Cascading 3 Combo/Drop boxes with Multiple SQL Tables

Data Problem!! I checked the DB and we were using a Strategy_Id (Unique) in the Tactics Table where it should have been the Strategy_Number (not unique). Changed that updated all rows and all working!  Apologies for wasting your time and thank you .

 

Thanks

David 

Super User
Super User

Re: Cascading 3 Combo/Drop boxes with Multiple SQL Tables

Awesome. Some times a second set of eyes is all it takes. 🙂 

 

Oh, and mark this as solved. 😝

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,638)