cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidWestgate
Frequent Visitor

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

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

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

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

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. 

 

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

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 

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
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,358)