Dropdown menu Issue

So I have a data table and one column is shift column and i have A and B in there. I made my drop down then created a String in the custom property of the dropdown then i binded it to the table using this expression

SELECT distinct
WHERE {Root Container.Pick Shift.Shifts}

then added this expression for the Shift string value

if({Root Container.Pick Shift.selectedValue} = -1, "1=1",
	"twelve_hour_Shifts = '" + escapeSQL ({Root Container.Pick Shift.selectedStringValue}) +"'")

but for some reason all i see in the drop down menu is A, why doesn’t include shift B? and also why doesn’t the table only show data for shift = A ?

Any help is appreciated.

Looks like you have some circular logic going on. The binding is on the dropdown data, not the tables below it.

Try removing the WHERE CLAUSE on the SQL query on the dropdown, and moving it to your tables. :slight_smile:

that seemed to work but now another issue comes up, I originally had this on my Table SQL

 t_stamp BETWEEN '{Root Container.Group 3.Start.date}' AND '{Root Container.Group 3.End.date}' ;

So i can pick the date range for the table, i removed that and added the other one and it would show all A shifts, now B shift was still missing. now is there anyway i can Mix these two? something like

 t_stamp BETWEEN '{Root Container.Group 3.Start.date}' AND '{Root Container.Group 3.End.date}' AND{Root Container.Pick Shift.Shifts} ;

Or is it better to do some sort of Subquery?

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. :smiley:

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.

Thanks for the reply, I keep getting and Error when i try

SELECT distinct

It says ‘All’ isn’t a valid column

Oops! My bad. :blush: Should be single quotes around All, not double:

SELECT distinct twelve_hour_Shifts FROM JK_LH_Monthly UNION ALL SELECT 'All'

Not enough caffeine today!

I can swear i tried that when the “all” didn’t then i got angry and deleted it lol, I’m gonna go try it again hoping it work, if i can at least get that to work then i can call it the day… :unamused:

OK, ‘all’ shows up find in the menu guess it didn’t do it lol, now for some reason if i pick A or B first of all, nothing happens and second I can’t make any other choices, it’s like it overrides everything and they all get deleted when one option gets picked.
Is it possible the issue is somewhere in the switch statement in the drop down expression?

switch({Root Container.Dropdown.selectedLabel},
      "AND twelve_hour_shifts = 'A'",
      "AND twelve_hour_shifts = 'B'",

Ah. Likely, the dataset keeps getting overwritten when the query refreshes. Try turning off polling.

tried it but no luck :frowning:
Can you think of another way to sort through the data based on shift A or B or All together? I guess this would be the absolute last resort, 3 windows with 3 identical tables, only difference would be in the WHERE clause i say show everything with A in shift column, the other B and last one show all. I totally would mind doing this BUT have to do the same thing for other machines and if I want to create 3 windows for every machine that would be a pain in the butt… lol I’m trying to find a more civilized and 21st century approach :mrgreen:

All right, we’ll back up a moment.

Does the bound property change as expected?

the two pop-up calendars works fine, but I have to do it manually. I think the picture will be easier to understand.

So i was thinking, since I can’t seem to figure out this drop down issue, how about this? put two check marks on top of the page one being “shift A” and the other “shift B”, so if i pick either then the table gets sorted based on that and if both selected then everything will be shown. but then how would that work. :scratch:

You probably have a very simple error someplace. When you select a different value in the dropdown you should see the Shift string value change. Does it?

I noticed that your Dropdown list is showing 4 rows, which is curious because there should only be 3 (A, B, All)? I also noticed that your Custom Property is called Shft, not Shift. I would double check your bindings and make sure everything is named correctly.

For some reason In my drop down properties when I go under dataset viewer, I see 4 rows, A,B, blank,All … not sure why

at this point I’m just trying everything. would this switch statement be wrong?

switch({Root Container.Dropdown.selectedLabel},
      "AND twelve_hour_shifts = 'A'",
      "AND twelve_hour_shifts = 'B'",
      "AND twelve_hour_shifts = 'A' && 'B'",

Also for some reason when i pull down the drop down list the first time, all is good, when I click on either B or All, then the menu gets stuck on that pick and won’t be able to see anything else. is there a reason for that?
Again as i mentioned in few posts ago with a picture, if i go in my custome property box under drop down property i and manually change

AND twelve_hour_shifts = 'A'

A to B i see the change in the table or from B to A. but the drop down gets stuck and i can’t seem to figure out why… :scratch: :scratch: :scratch:

Let’s try removing the query that populates the data property (No binding). Even if you remove the binding to the query, the data will stay (A/B/All)

What does the Shift property do now?

Are you talking about the the drop down data property? if so i deleted the Query and clicked on no binding and the drop down still acts funny. Now if i change the “selected string value” or “selected label” manually it changes. if i choose “A” then the drop down starts working just find and i can click on it and it shows me “B” and “All” but if i choose “B” or “All” the drop down text changes to one of the selected but then i can’t actually pick anything else, it gets stuck…

Huh. Okay, can you export the windows and post it? We can take a closer look at it. (Unless there’s some NDA thing going on here)

I’ll post that specific window, shouldn’t be an issue, you’ll just get a boat load of errors lol. hopefully you can see where the issue is.
test.proj (46.1 KB)