Nope, you’re on the right track. We just need to get that portion of the WHERE clause to be more consistent.
Let’s try making that entire portion of the clause (including the AND) as part of our binding. Time to introduce you to a new expression: SWITCH
switch(value, case1, ...caseN, return1, ...returnN, returnDefault)
SWITCH is like an if expression on steroids.
The first argument in a switch function is the value. This is what gets compared to the next set of arguments, the cases. The cases are all the items you want to match up. The returns are what it give back on a match. for every case, there must be a return. The last one, returnDefault, is what it returns if there are no matches.
So for the expression, we can use:
switch({Root Container.Pick Shift.selectedLabel},
"A","B",
"AND twelve_hour_shifts = 'A'",
"AND twelve_hour_shifts = 'B'",
"")
Notice that I’m using the selectedLabel property. The switch will filter out and use just what we need.
Now, in the table query, the WHERE clause can be simplified to:
t_stamp BETWEEN '{Root Container.Group 3.Start.date}' AND '{Root Container.Group 3.End.date}' {Root Container.Pick Shift.Shifts}
Remember, the AND is now part of the binding. If “A” or “B” are not picked, that part of the query “disappears”.
One more thing you may not have though of: once you pick a shift, there is no way to select All shifts again. Consider changing the pick shift query to:
SELECT distinct
twelve_hour_Shifts
FROM
JK_LH_Monthly
UNION ALL
SELECT "All"
This will put an “All” entry in the dropdown list. And, since “All” is not in the switch, it will also return a blank string.