Limit choices and allow only one selection in DB query filter

[#1]

I want a query filter that will only show a portion of the entries in the database. I would also like to limit users to one choice. Here is example code that I have used in a previous post.

New SQL Query(
   Version( 130 ), Connection( "JMP" ),
   JMP Tables(
      ["SAT" => "$SAMPLE_DATA\SAT.jmp",
      "SATByYear" => "$SAMPLE_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;

For example, what if I only want to show users Alaska, California, Hawaii, Oregon and Washington?

I can replace _AllRows_, with {"Alaska", "California", "Hawaii", "Oregon", "Washington"}. However, that still shows all of the states. It just preselects the 5 states in the list.

My real application is a big DB with multiple filters, so I don't want to use intermediate tables.

Also, I would like to allow users to only select one state. Is it possible? I can change the filter type.

[#2]

I deleted my first response. The Manual List did provide the expected prompts, but did not filter based upon the user selection.

Look for an update, soon.

[#3 · Accepted solution]

I tried 3 options:

1. A simple modification to your script, would list all of the states, but pre-select the list of Pacific states. The user sees the highlighted list, but needs to use CTRL click to select or unselect.
2. The deleted post contained a script that used a Manual List. It would prompt with a list of Pacific states. However, it did not filter based upon the user selections. I tried Ands and other syntax, but the query always returned the full set of Pacific states.
3. The only way I could get the smaller list, was to open SATByYear, make a subset using query, then used that subsetted table in New SQL Query. Note  << Set Dirty(0) sets the table as unedited and SQL Query will close it once teh query runs.

Names Default to Here(1);
dt0 = Open("$Sample_Data\SATByYear.jmp", "Invisible");
satby_dt = query(Table(dt0, "t1"),"Invisible", "SELECT DISTINCT * FROM t1 
        WHERE  t1.State IN  ( 'Alaska' ,  'California' ,  'Hawaii' ,  'Oregon' ,  'Washington' ) ");
close(dt0,NoSave);        
satby_dt << Set Name("SATByYear");  
satby_dt << set dirty(0);
wait(1);      
New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		[ "SATByYear" => "Source(satby_dt)", "SAT" => "$Sample_Data\SAT.jmp" 	]
	),
	QueryName( "SQLQuery" ),
	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", "t2" ), Column( "State", "t1" ) ) &
				EQ(
					Column( "% Taking (2004)", "t2" ),
					Column( "% Taking (2004)", "t1" )
				) & EQ( Column( "X", "t2" ), Column( "X", "t1" ) ) &
				EQ( Column( "Y", "t2" ), Column( "Y", "t1" ) ) &
				EQ( Column( "Population", "t2" ), Column( "Population", "t1" ) ) &
				EQ( Column( "Latitude", "t2" ), Column( "Latitude", "t1" ) ) &
				EQ( Column( "Longitude", "t2" ), Column( "Longitude", "t1" ) )
			)
		)
	),
	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;

[#4]

Thanks, gzmorgan0, Your effort is helpful and appreciated. It doesn't fit my hope to avoid intermediate tables, but I can see how using Invisible and Set Dirty might make it work for my case with multiple filters. I wasn't aware of that solution. I'll mark your answer as a solution and hope that somebody will post an improvement.

I have been trying to do things in Where and In List, most notably with ValueSource. As far as I can tell, ValueSource is not doing anything in my scripts. I don't see it in the scripting index.

Maybe I'm spoiled by online forms. Still I want to request for any JMP developers reading, Can you add the following functionality to DB query filters.

- Return an empty list when nothing is selected (my previous post)

- Allow posting of subset of a column in a ListBox filter box, perhaps using a list somewhere in Where or In List

- Allow for situations to limit users to choosing only one item.

[#5]

It can be hard for a general tool like JMP's query builder to do everything.  Have you considered creating your own custom interface for the query?  It will be more work in the beginning, but then you'll have exactly the features that you want.

[#6]

Thanks for managing my expectations. One correction is that I did not ask for it to do everything. Returning empty lists is as easy as returning full lists when nothing is selected. Limiting users to one choice is common for many languages and APIs. Exposing only a subset of potential choices might be the most demanding request. Developers might be interested in knowing that there is interest in those particular features.

I understand that things take time, and don't expect any of these features very soon. In the meantime, suggested work arounds have been mostly helpful. This forum is appreciated.

If anybody can reccommend resources for building custom DB query filter interfaces within JMP, I am very interested.

I am also exploring other options, such as JavaScript, Rails, R, etc. Pushing people to other languages doesn't seem like an appropriate use of a JMP discussion forum, though. So, if I do design something else, I won't post it here.

[#7]

One option is to produce a prompt for user selection to pass those selected values to a SQL statement for execution. The script can use the Create Database Connection() and Execute SQL() to interact with your database. The below is a simpler example that can be used for building a more robust script, so it will require more work to make this work for your use-case. Hopefully, this will help you obtain the results you desire.

//clearing all symbols.
Delete Symbols ();

//defining an empty value for selection so it can handle when nothing is selected.

selection = {};

//Prompt providing values available for said user

nwp = New Window( "States",
	<<modal,
	b = List Box(
		{"Alaska", "California", "Hawaii", "Oregon", "Washington"},
		width( 200 ),
		nlines( 6 ),
		Button Box( "Ok" ),
		Button Box( "Cancel" ),
		selection = b << GetSelected()
	), 
);

//setting up rule for the empty selection or when npw is closed.

if(selection == {} | nwp ["button"] == -1 , throw() );

//call of ODBC administrator connection dialog to select source

SQLConnection = Create Database Connection(
	"connect dialog"
);

//defining string for sql statement

sqlStr = "SELECT * FROM [dbo].SAT2 WHERE State in (";

//looping through selection to add values to the sql statement.

For( i = 1, i <= N Items( selection ), i++,
	If( i == N Items( selection ),
		sqlstr = sqlstr || "'" || Parse( selection[i] ) || "' )",
		sqlstr = sqlstr || "'" || Parse( selection[i] ) || "', "
	)
);

//executing the database connection and sql statement.

Execute SQL( SQLConnection, sqlStr );