I'm looking for a JSL script that does the following: Gets the currently selected columns in the active data table. Takes the first selected column. Prompts the user to enter a regular expression. Selects only the rows in the table where the value in that column matches the regex. Opens a Data View window showing only those matching rows. Is this possible with JSL?

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Quickly show Data View with subset of rows that match regex

BHarris

I'm looking for a JSL script that does the following:

Gets the currently selected columns in the active data table.
Takes the first selected column.
Prompts the user to enter a regular expression.
Selects only the rows in the table where the value in that column matches the regex.
Opens a Data View window showing only those matching rows.

Is this possible with JSL?

Accepted answers

BHarris

Ok, I think I've figured this out -- this script is basically the tabular equivalent of the unix/linux/mac "egrep" command, i.e. select/show just the rows that match a user-entered regular expression (search string):

Names Default To Here( 1 ); 

If(
	ex = New Window( "Enter search regex",
		<<modal,
		<<return result,
		V List Box(
			H List Box( userinput = Text Edit Box( "", <<Set script( okBtn << Click() ), <<set width( 400 ) ) ),
			H List Box( okBtn = Button Box( "OK" ), Button Box( "Cancel" ) )
		)
	);
	ex["button"] != 1;	// did the user not press button #1?
,
	Stop()	// then get out
);
inputRegex = ex["userInput"];

dt = Current Data Table();
selCols = dt << Get Selected Columns( "String" );
If( N Items( selCols ) == 0,
	selCols = dt << Get Column Names( "String" )
);
col = selCols[1];
dt << Select Where( !Is Missing( Regex( Column( dt, col )[Row()], inputRegex, "1", IGNORECASE ) ) );
dt2 = dt << Data View;
Column(dt2,col) << Set Display Width( 0 );

All of the buttons work correctly, and it's reasonably fast even with many thousands of rows. Personally, I made an Addin of it and tied it to cmd-shift-e (egrep) and now use it all the time. Of course I'm open to suggestions on how to improve it. Hope this helps someone!

(And big thanks to @jthi for his patience and insight!)

All comments

jthi

It is possible. What have you tried?

This is very basic example missing all checks which could (and most likely should) be implemented

Names Default To Here(1); 

dt = Current Data Table();

col = (dt << Get Selected Columns("String"))[1]; // I suggest dropping non-character columns

run_expr = Expr(
	rgx_pattern = teb << Get Text; // You could check if the pattern is valid regex
	dt << Select Where(!Is Missing(Regex(Column(dt, col)[Row()], rgx_pattern)));
	// add check if any rows have been selected
	dt << Data View; // If linked subset is fine, that is most likely better as it can avoid using select where
	dt << Clear Select;
	wait(0);
);

nw = New Window("",
	H List Box(
		Lineup Box(N Col(2),
			Text Box("Enter regex pattern"),
			teb = Text Edit Box("")
		),
		Lineup Box(N Col(1),
			Button Box("OK",
				run_expr
			),
			Button Box("Cancel")
		)
	)
);

Write();

BHarris

That works, thanks.

To be honest, I didn't include any code because my attempt wasn't very close, and exposes some fundamental misunderstandings about JSL that probably come from me learning GUI scripting from AppleScript and Matlab.

I would've gotten the list of selected column names, picked the first one, but then I expected some kind of "display dialog" function which returns a string, but that apparently doesn't exist, and forces you into this new-window/list-box/button-expression syntax, which was fairly unintuitive to me, but makes more sense now that I see what it's doing. I tried ChatGPT, but it created long scripts that didn't run at all.

A few questions about your solution, if you have time:

Why doesn't the "Cancel" button do anything? I presume because you have no 2nd argument to its Button Box() call, but what expression would close the current window? Close()? I tried "Button Box("Cancel", Close())" but it errors out with an error I don't understand. I mimicked your approach and tried "Close Window" which seems to work, but I expect there's a cleaner way to do it.
Your "Get Selected Columns()" call has "String" as an argument, but the documentation shows that function takes no arguments. What does "String" mean here, and how did you know it would be accepted or what it does?
I'd like to start typing immediately after running the script, but the focus isn't in the right place. I tried "teb << SetFocus;" after the "nw=..." expression, but JMP says text edit boxes don't understand that message.
Is it possible to make Regex be case insensitive, and if so, how? (I tried adding "<IGNORECASE>" to the Regex arguments per the documentation, but it errors out.)
Is it possible to make return/enter trigger the "Ok" script with the teb box focused?
If the user has no selected columns, I'd like to use the first character column, but after about 20 different attempts I gave up trying to figure out which column that is.
I would prefer the data view subset be linked, does that change the approach?

Here's what I have so far:

Names Default To Here(1); 

run_expr = Expr(
	dt = Current Data Table();
	selCols = dt << Get Selected Columns("String");
	if(N Items(selCols)==0, selCols = dt << get column names("String") );
	col = selCols[1];
	rgx_pattern = teb << Get Text;
	dt << Select Where(!Is Missing(Regex(Column(dt, col)[Row()], rgx_pattern)));
	dt << Data View; // I prefer a linked subset, what changes here?
	dt << Clear Select;
	wait(0);
	nw << Close Window;
);

close_expr = Expr(nw << Close Window);

nw = New Window("",
	H List Box(
		Lineup Box(N Col(2),
			Text Box("Enter regex pattern"),
			teb = Text Edit Box("", <<Set Width(400))
		),
		Lineup Box(N Col(1),
			Button Box("OK",
				run_expr
			),
			Button Box("Cancel", close_expr)
		)
	)
);

teb << Set Focus();

Write();

I'd love to open Big Class, run the script, then type "ob.+t<enter>" and have it bring up a Data View of just the two Roberts. Right now it's close -- after running the script, I have to press tab, then OB.+T, then enter twice.

jthi

I created as minimal example as I could, so Cancel doesn't contain expression to close the window
I just tried if "String" would work like it does with << get column names() and it does. Test it out without the "String" value and see what you get (references instead of strings). In my opinion, working with the column references is a nightmare so I always use strings of column names and convert them as needed. But it really is up to you if you prefer using references (or whatever that colon syntax is)
I think you are stuck with tab unless you wish to use complicated workaroundsAllow SetFocus to be used with interactive display elements (TextEditBox, Button...)
You could Lowercase() user input and wrap the column reference with Lowercase(). Or, I think you could add the default capturing group to Regex() when using INGORECASE: Regex("ABCD", "d", "\0", IGNORECASE)
You could try of << Set Function would fit your needs. It will execute the script on commit and enter should trigger that (as does clicking outside of the text edit box).

<< Get Column Names(Character, String)[1]

r = dt << Get Rows Where(!Is Missing(Regex(Column(dt, col)[Row()], rgx_pattern)));
dt_subset = dt << Subset(Rows(dt), Selected Columns(0), Link to original data table(1));

There are plenty of improvements which could be done but what those could be depends on the real use case.

Edit: Added link to original data table(1) to subset

BHarris

Excellent, thank you!

Just one more question: If multiple columns are selected, I'd like to perform the regex on the column values concatenated together as strings, and if no columns are selected, I'd like to do the same thing on *all* columns. This effectively mimics the behavior of egrep on the table. Is that reasonably doable? See commented line below for my initial attempt at this, but it doesn't pull just the values from the selected columns, so it's not there yet (and it doesn't work).

Names Default To Here( 1 ); 

run_expr = Expr(
	dt = Current Data Table();
	selCols = dt << Get Selected Columns( "String" );
	If( N Items( selCols ) == 0,
		selCols = dt << Get Column Names( "String" )
	);
	col = selCols[1];
	rgx_pattern = teb << Get Text;
	dt << Select Where( !Is Missing( Regex( Column( dt, col )[Row()], rgx_pattern, "1", IGNORECASE ) ) );
//	dt << Select Where( !Is Missing( Regex( Concat Items( dt << get Rows(Row())," "), rgx_pattern, "1", IGNORECASE ) ) );
	dt << Data View;
	Wait( 0 );
);

close_expr = Expr( nw << Close Window; Wait( 0 ); );

nw = New Window( "Table Egrep",
	H List Box(
		lb1 = Lineup Box( N Col( 2 ),
			Text Box( "Select column to Enter regex pattern" ),
			teb = Text Edit Box( "", Set Script( run_expr ), <<Set Width( 400 ) )
		),
		Lineup Box( N Col( 1 ), Button Box( "OK", close_expr ), Button Box( "Cancel", close_expr ) )
	)
);

FYI, the "Set Script" above handles the immediate execute on "enter", which works great. The only missing desired behavior is the one above, and the need to hit "tab" to get the focus into the text edit box.

BHarris

Names Default To Here( 1 ); 

If(
	ex = New Window( "Enter search regex",
		<<modal,
		<<return result,
		V List Box(
			H List Box( userinput = Text Edit Box( "", <<Set script( okBtn << Click() ), <<set width( 400 ) ) ),
			H List Box( okBtn = Button Box( "OK" ), Button Box( "Cancel" ) )
		)
	);
	ex["button"] != 1;	// did the user not press button #1?
,
	Stop()	// then get out
);
inputRegex = ex["userInput"];

dt = Current Data Table();
selCols = dt << Get Selected Columns( "String" );
If( N Items( selCols ) == 0,
	selCols = dt << Get Column Names( "String" )
);
col = selCols[1];
dt << Select Where( !Is Missing( Regex( Column( dt, col )[Row()], inputRegex, "1", IGNORECASE ) ) );
dt2 = dt << Data View;
Column(dt2,col) << Set Display Width( 0 );

(And big thanks to @jthi for his patience and insight!)

jthi

Looks good! I would make few small changes:

Before opening the New Window check that there is a table open and if there isn't one, run stop();
If there are no matches, the last row will throw an error. You could either check selected row count and use stop() if it is 0 or show information to the user that there was no matches
<< Get Rows Where is mos likely faster than << Select Where
- Some additional speed-up can most likely be gained by evaluating the column reference
<< On Validate could also be used if you wish to let user input new regex if the first doesn't have any matches

Names Default To Here(1); 

If(N Table() == 0,
	stop(); // display error message to user if necessary
);

dt = Current Data Table();

nw = New Window("Enter search regex",
	<<modal,
	<<return result,
	V List Box(align("center"),
		Panel Box("Input search regex", 
			userinput = Text Edit Box("", 
				<<Set Script(
					okBtn << Click()
				)
				, <<set width(400)
				, << Set Hint("search regex...")
			)
		),
		Lineup Box(N Col(2),
			okBtn = Button Box("OK"),
			Button Box("Cancel")
		)
	)
);

If(nw["Button"] != 1,
	stop();
);

inputRegex = nw["userInput"];
If(IsMissing(inputRegex),
	stop(); // empty regex
);

selCols = dt << Get Selected Columns("String");
If(N Items(selCols) > 0,
	col = selCols[1];
,
	col = Column(dt, 1) << get name;
);

selrows = Eval(EvalExpr(
	dt << Get Rows Where(!Is Missing(Regex(Expr(NameExpr(As Column(dt, col))), inputRegex, "1", IGNORECASE)));
));

If(N Rows(selrows) == 0,
	stop(); // display error message if necessary, this could also be done already within modal window (<< On Validate), if you wish to let user perform new search
);

dt2 = dt << Subset(Rows(selrows), Selected Columns(0), Link to original data table(1), Output Table(""));
// dt2 << Select Rows(1::NRows(dt2)); // to select rows if necessary

Names Default To Here(1); If(N Table() == 0, stop(); // display error message to user if necessary ); dt = Current Data Table(); nw = New Window("Enter search regex", <<modal, <<return result, V List Box(align("center"), Panel Box("Input search regex", userinput = Text Edit Box("", <<Set Script( okBtn << Click() ) , <<set width(400) , << Set Hint("search regex...") ) ), Lineup Box(N Col(2), okBtn = Button Box("OK"), Button Box("Cancel") ) ) ); If(nw["Button"] != 1, stop(); ); inputRegex = nw["userInput"]; If(IsMissing(inputRegex), stop(); // empty regex ); selCols = dt << Get Selected Columns("String"); If(N Items(selCols) > 0, col = selCols[1]; , col = Column(dt, 1) << get name; ); selrows = Eval(EvalExpr( dt << Get Rows Where(!Is Missing(Regex(Expr(NameExpr(As Column(dt, col))), inputRegex, "1", IGNORECASE))); )); If(N Rows(selrows) == 0, stop(); // display error message if necessary, this could also be done already within modal window (<< On Validate), if you wish to let user perform new search ); dt2 = dt << Subset(Rows(selrows), Selected Columns(0), Link to original data table(1), Output Table("")); // dt2 << Select Rows(1::NRows(dt2)); // to select rows if necessary

Quick Links