Filtering a datatable with a custom filtering application

[#1]

Hello All,

I am building an application/dashboard with a bunch of reports inside it. At the end I would like to give users the option to make a "query" of the main datatable and save it if they have a use for it later on.

Currently, I have a button which opens another window and prompts the user to select filtering columns (from a preselected list).
Once they select them, they press another button which should then create a list box for each of the filtering columns with all available distinct values. My thought is from here the user would select the values they want the filtering to occur for in each list box and then press a final button to save those values and then a filter/where clause would be build which will subset the main datatable and save it for them in a known location.

I believe I am halfway there. I get the first window and filter column selection to work. Then I can display the values in list boxes but am unable to create the right button to save those selected values. Also, I believe I am running into the issue of not knowing how many filter columns the users will create therefore I have to have an expression to define an unknown number of columns. I saw some examples with Eval(Parse(Eval Into statements but have not gotten it yet.

Any help will be appreciated. Maybe there is a much more streamlined way to do it.

Here is some of the code I have so far

filter_col_append_expr = Expr(
	lub1 << Append(List Box(column(dt1,Char(col_filter[count_i])) <<GetAsMatrix));
	vlb3 << Append(Spacer Box(size(1,10))););

query_expr = Expr(
	query_window = New Window("Database Query",
		Spacer Box( size (1,5)),
		vlb3 = V List Box(Text Box("Select All Filter Columns"),
		H List Box(Spacer Box( size (1,5)),
		lb3 = List Box({"XXX","YYY","ZZZ","AAA"},
		max selected(8),On Change( col_filter = lb3 << Get Selected;))),
		Spacer Box( size(1,10)),
		Button Box("Select Filter Columns",query_window<<Append(lub1=Lineup Box(N Col(4)));
			For( count_i=1, count_i<=NItems(col_filter),count_i++,filter_col_append_expr;)))));


//The button which will run the whole thing is here. Some code is ommited
Panel Box("Database Query", Button Box("Query",query_expr;query_window << Append(bb3 = Button Box("Select Filter Values",query_values = lub1<<Get Selected));)))

[#2 · Accepted solution]

Here are some options

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

// Build using Select Where (simplest)
// Note that we convert to Char due to using Summary and because we can have numeric values
// This could (and should) be handled earlier in some other method
user_selections = ["age" => {"13", "14"}, "sex" => {"F"}];
For Each({{col, values}, idx}, user_selections,
	If(idx == 1,
		dt << Select Where(Contains(values, Char(Column(dt, col)[])));
	);
	dt << Select Where(Contains(values, Char(Column(dt, col)[])), Current Selection("restrict")); // AND
	// dt << Select Where(Contains(values, Char(Column(dt, col)[])), Current Selection("extend")); // OR
);
show(dt << get selected rows);
wait(1); // demo purposes

dt << Clear Select;
// A bit more complicated is to build expression and use that
user_selections = ["age" => {13, 14}, "sex" => {"F"}]; // simplified to use numeric value for numeric column
filter_expr = Expr(And()); // optionally use Or()
For Each({{col, values}, idx}, user_selections,
	cur_expr = Substitute(
		Expr(Contains(_list_, _column_)),
		Expr(_list_), values,
		Expr(_column_), Name Expr(AsColumn(dt, col))
	);
	Insert Into(filter_expr, Name Expr(cur_expr));
);
// Show(Name Expr(filter_expr));

my_rows = dt << Get Rows Where(filter_expr);

show(my_rows);