Limit choices and allow only one selection in DB query filterI 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",<BR /> "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.
Re: Limit choices and allow only one selection in DB query filterOne 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></MODAL>Re: Limit choices and allow only one selection in DB query filterThanks 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.
Re: Limit choices and allow only one selection in DB query filterIt 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.
Re: Limit choices and allow only one selection in DB query filterThanks, 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.
Re: Limit choices and allow only one selection in DB query filterI tried 3 options:
- 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.
- 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.
- 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;
Re: Limit choices and allow only one selection in DB query filterI 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.
