Have List Box in Query Builder return empty list when no items selected

[#1]

I want to have a query where, if users do not select anything, then an empty list is returned. Here is an example modified from the following JMP Query Builder page.

https://www.jmp.com/support/help/14/query-and-join-data-tables-with-jmp-query-builde.shtml

Rather than filtering by scores, as on the web page, I modified it so users select states. When no states are selected, then I want an empty list or table returned.

New SQL Query(
   Version( 130 ), Connection( "JMP" ),
   JMP Tables(
      ["SAT" => "\C:\Program Files\SAS\JMPPRO\14\Samples\Data\SAT.jmp",
      "SATByYear" => "\C:\Program Files\SAS\JMPPRO\14\Samples\Data\SATByYear.jmp"]
   ),
   QueryName( "SATQuery" ),
   Select( Distinct,
      Column( "State", "t1" ),
      Column( "% Taking (2004)", "t1" ),
      Column( "2004 Verbal", "t2" ),
      Column( "2004 Math", "t2" )
   ),
   From(
      Table( "SATByYear", Alias( "t1" ) ), 
      Table( "SAT", Alias( "t2" ),
      Join( Type( Left Outer ),
            EQ( Column( "State", "t1" ), Column( "State", "t2" ) ) &
            EQ( Column( "% Taking (2004)", "t1" ),
                Column( "% Taking (2004)", "t2" ) ) 
            & EQ( Column( "X", "t1" ), Column( "X", "t2" ) ) 
            & EQ( Column( "Y", "t1" ), Column( "Y", "t2" ) ) 
            & EQ( Column( "Population", "t1" ), Column( "Population", "t2" ) )
            & EQ( Column( "Latitude", "t1" ), Column( "Latitude", "t2" ) )
            & EQ( Column( "Longitude", "t1" ), Column( "Longitude", "t2" ) )
         )
      )
   ),
   Where(
      In List( Column( "State", "t1" ),
         _AllRows_,
         UI( SelectListFilter(
               ListBox,
               Base( "Categorical",
                  Prompt( "State:", Character, PromptName( "t1.State_1" ),
                     ValueSource( Column( "State", Alias( "State" ), Table( "SATByYear" ) ) )
                  )
               )
            )
         )
      )
   )
) << Run;

If it is possible, I think it would happen the the Where clause. I have tried Not In List, Not Or, and replacing _AllRows_ with an empty list, {}. Whatever I do, if no states are selected from the list box, then all states are returned, or I get an error.

We are designing an app with several list boxes filtering choices for a SQL query. Some list boxes will have hundreds of choices. A Manual List is not good, because users will not know all of the choices. For our purposes, when no items are selected, it will be preferable to have an empty list returned. Is this possible? If so, can it be demonstrated with the SAT example I posted above?

Thanks for any suggestions or insights.

[#2]

The brute force way is to build each clause from the user as strings and then use dynamic JSL to run the combined strings. See related question: Dynamic-Filters-for-SQL-Query

[#3]

Thanks for replying, Stephen. I am having difficulty seeing how dynamic programming will solve my issue.

I know what the clauses are, except for returning empty lists if the user doesn't select anything. There are a handful of known columns that every user will need to filter anywhere from choosing nothing from that column to choosing everything, and anything in between. I want none to be the default if the user doesn't select anything in the list. In the SAT example I posted, the question is how to make zero states returned if the user doesn't select any.

I don't see how building clauses dynamically would help, though I may be missing something. In other words, even if I do build clauses dynamically, I don't see what to dynamically put into filters to return empty lists if nothing is selected. And, if it can be entered in dynamic filters, then why can't it be statically programmed into a known set of List Box filters?

[#4 · Accepted solution]

First, thanks for posting a clear example of what you're doing. Very helpful.

with the script as it is, try doing these two things.

1. run the script, don't select anything, click ok

2. run the script, select all (or some of the states)

Compare the source script in both resulting tables, especiall around line 57. 

when nothing is selected its really different from when everything is selected.

This is totally cheating but it works!

At the beginning of the script edit the first line like this:

dtq=New SQL Query(

Add these lines to the bottom of your script.

x=expr(dtq<<get script("Source"));
xx=char(eval(evalexpr(x)));
if (  contains(xx, "_AllRows_")  >0,
	dtq<<Select All Rows;
	dtq<<delete rows;,
	Print("All Good")
);

 The query retruns a data table, with dtq as the handle. Then the script gets the the Source Script from the table and turns it into a quoted string, with "xx" as its handle. The Contains argument checks to see if the whole table was included, which happens when nothing is selected in the dialog. In this case the text "_AllRows_" is includedd in the Source Script. If this is true, then all the rows in the table are selected and then deleted.

Like I said, its totally cheating, but it works in this case.

[#5]

Bryon, yes, I see. If no rows are selected, then _AllRows_ is written to the table script. Whereas, a list, e.g. {Alabama,Alaska}, is written if any or all states are selected. So, as long as _AllRows_ is only written when no rows are selected, then your fix will work.

For readers, here is what I added to the exact script in my original post.

selected_states = Data Table("SATQuery"); 
x=expr(selected_states<<get script("Source"));
xx=char(eval(evalexpr(x)));
if (  contains(xx, "_AllRows_") > 0,
    selected_states<<Select All Rows;
    selected_states<<delete rows;
    Print("All Good")
);

It works for me in JMP 14. The table is cleared. It should be easily modifiable for other situations calling for an empty list. And, you can leave out the Print line.

Thanks for this solution/hack. I'll wait to see if anybody posts something better. If not, it will be the accepted solution tomorrow.

[#6]

selected_states = Data Table("SATQuery"); 
x=expr(selected_states<<get script("Source"));
xx=char(eval(evalexpr(x)));
if (  contains(xx, "_AllRows_")  >0,
	selected_states<<Select All Rows;
	selected_states<<delete rows;
	Print("All Good")
);

I hope the line breaks showed up this time.