Cascading 3 Combo/Drop boxes with Multiple SQL Tables

[DavidWestgate]

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

[Shanescows]

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) 

[DavidWestgate]

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.

[DavidWestgate]

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. 

[Shanescows · Accepted solution]

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

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

[DavidWestgate]

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 

[Shanescows]

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

Oh, and mark this as solved. 😝