Recordings and files: Scripters Club on Data Access, JMP Roasting and Tips and Tricks

[#1]

Video 1 Data Access from @Georg 

Comments: 

• Even though the documentation is still lacking and it can be a bit messy to use... I would still suggest you use it as it is the preferred method and will (and does) support connectors
• There is also queryResult "symbol" you can access on run complete which should be the reference to the resulting table https://www.jmp.com/support/help/en/18.1/#page/jmp/sql-functions.shtml
• Always use either <<Run Background or << Run Foreground and do not rely on << Run which is determined by user preferences

Video 2: JMP Roasting fro, @jthi 

Comments: 

• I would recommend to look in scripting index, how the new window command works well in different modes, there are examples for most variants (modal, report, ...)
• Agree, there examples there. Not always as useful as I would prefer. Pretty much every time I use the NewWindow(), I want to do something for which there is no similar example. I have found really helpful examples for NewWindow() while searching the community.
• Please also add a possibility to add examples. There are very useful examples in the community. I think it doesn't make sense to flood the Scripting Index with all the examples. But it will help us a lot I we get the possibility to add useful examples to OUR scripting index.

• Here is the start of the wish list entry: https://community.jmp.com/t5/JMP-Wish-List/possibility-to-add-Examples-to-the-Scriptiing-Index/idi-p...

Video 3: Tips and Tricks from @jthi 

Post for suggestions: https://community.jmp.com/t5/JMP-Scripters-Club-Discussions/Collection-of-good-valuable-scripting-ex...

[#2]

My material attached

[#3]

Thank you @jthi for the very useful presentation! Inspired me to rewrite a dynamically created modal window to avoid using strings and to use your XPath method for extracting results.

I did find one thing where I couldn't avoid parsing strings though - I want to include XPath commands inside the modal window but it needs to be dynamically created.

I want to create a check box that controls whether a text edit box is enabled or not, however, I don't know how many text edit box/check box pairs there will be:

rc_list = Lineup Box( N Col( 3 ) );
rc_list << append( Text Box( "Current", <<set font( "Arial Black" ) ) );
rc_list << append( Text Box( "New", <<set font( "Arial Black" ), <<Justify Text( "center" ) ) );
rc_list << append( Text Box( "Change", <<set font( "Arial Black" ) ) );
For( i = 1, i <= N Items( orig_vals ), i++,
	rc_list << append( Text Box( Char( orig_vals[i] ) ) );
	rc_list << append( Text Edit Box( Char( orig_vals[i] ), <<enabled( 0 ) ) );
	// needs to be constructed as a string so that the index i can be inserted into the XPath("(//TextEditBox)[i])
	Eval(
		Parse(
			"\[rc_list << append( check box( "", <<set function( function( {this}, (lub << XPath("(//TextEditBox)[]\" || Char( i )
			 || "\[]")) << enabled( this << get ) ) ) ) );]\"
		)
	);
);

Is there a way of doing this so I don't need to parse JSL strings? Or is there a better way to do this modal recode?

My complete code is here:

View more...

Names Default To Here( 1 );

// Function to do a simple in-place column recode. 
// Uses a modal dialog so can be used as part of a larger script or workflow.
modal_recode = Function( {dt, col},
	{default local}, 
	
	Summarize( dt, orig_vals = By( col ) );

	rc_list = Lineup Box( N Col( 3 ) );
	rc_list << append( Text Box( "Current", <<set font( "Arial Black" ) ) );
	rc_list << append( Text Box( "New", <<set font( "Arial Black" ), <<Justify Text( "center" ) ) );
	rc_list << append( Text Box( "Change", <<set font( "Arial Black" ) ) );
	For( i = 1, i <= N Items( orig_vals ), i++,
		rc_list << append( Text Box( Char( orig_vals[i] ) ) );
		rc_list << append( Text Edit Box( Char( orig_vals[i] ), <<enabled( 0 ) ) );
		// needs to be constructed as a string so that the index i can be inserted into the XPath("(//TextEditBox)[i])
		Eval(
			Parse(
				"\[rc_list << append( check box( "", <<set function( function( {this}, (lub << XPath("(//TextEditBox)[]\" || Char( i )
				 || "\[]")) << enabled( this << get ) ) ) ) );]\"
			)
		);
	);

	get_results = Expr(
		res_tb = (lub << XPath( "//TextBox" )) << get text;
		res_check = (lub << XPath( "//CheckBoxBox" )) << get;
		res_teb = (lub << XPath( "//TextEditBox" )) << get text;
	);

	If( !Is Empty( col << get property( "List Check" ) ),
		valid_vals = "List Check is set for this column.\!NValid values are: " || Char( Arg( col << get property( "List Check" ), 1 ) ),
		valid_vals = "List Check not set for this column."
	);

	nw = New Window( "Simple Modal Recode",
		<<modal,
		<<returnresult,
		H List Box(
			Panel Box( "Select entries to recode:",
				Text Box( "Column name: " || Char( col << get name ) ),
				Border Box( Left( 5 ), Right( 5 ), bottom( 5 ), top( 5 ), sides( 0 ), lub = rc_list ),
				Text Box( valid_vals, <<set wrap( 200 ) )
			),
			Lineup Box( N Col( 1 ), Button Box( "OK", get_results ), Button Box( "Cancel" ) )
		)
	);

	If( nw["Button"] == -1 | Sum( res_check ) == 0,
		Print( "Recode cancelled by user." );
		Return();
	);

	// first three text boxes in the line up box are the column headings so remove from original values list
	Remove From( res_tb, 1, 3 );

	recode_array = {};
	// create array of replacement values: { old_value1, new_value1, old_value2, new_value2, ... }
	For( j = 1, j <= N Items( res_check ), j++,
		If( res_check[j] == 1,
			Insert Into( recode_array, res_tb[j] );
			Insert Into( recode_array, res_teb[j] );
		)
	);

	dt << Begin Data Update;
	Try(
		For Each Row( dt, dt:col[] = Map Value( dt:col, Eval List( recode_array ), Unmatched( dt:col ) ) ),
		If( !Is Empty( col << get property( "List Check" ) ),
			New Window( "Recode Failed",
				<<modal,
				Text Box( "Recode of column " || Char( col << get name ) || " Failed." ),
				Text Box( "The list check property is set and values are limited to the following: " ),
				Text Box( Char( Arg( col << get property( "List Check" ), 1 ) ) )
			),
			Print( "Recode failed!" )
		)
	);
	dt << End Data Update;
	dt << show window( 1 );
	Return();

);

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
modal_recode( dt, Column( dt, "Sex" ) );

[#4]

I wouldn't use << XPath in this case but rather this << prev sib. I assume this does what you are looking for:

So instead of

Eval(
	Parse(
		"\[rc_list << append( check box( "", <<set function( function( {this}, (lub << XPath("(//TextEditBox)[]\" || Char( i )
		 || "\[]")) << enabled( this << get ) ) ) ) );]\"
	)
);

you could do something like this

rc_list << Append(Check Box("", << Set Function(function({this},
	(this << prev sib) << Enabled(this << get);
))));

Full code

View more...

Names Default To Here(1);

// Function to do a simple in-place column recode. 
// Uses a modal dialog so can be used as part of a larger script or workflow.
modal_recode = Function({dt, col},
	{default local}, 
	
	Summarize(dt, orig_vals = By(col));

	rc_list = Lineup Box(N Col(3));
	rc_list << append(Text Box("Current", <<set font("Arial Black")));
	rc_list << append(Text Box("New", <<set font("Arial Black"), <<Justify Text("center")));
	rc_list << append(Text Box("Change", <<set font("Arial Black")));
	For(i = 1, i <= N Items(orig_vals), i++,
		rc_list << append(Text Box(Char(orig_vals[i])));
		rc_list << append(Text Edit Box(Char(orig_vals[i]), <<enabled(0)));
		// needs to be constructed as a string so that the index i can be inserted into the XPath("(//TextEditBox)[i])
		rc_list << Append(Check Box("", << Set Function(function({this},
			(this << prev sib) << Enabled(this << get);
		))));
	);

	get_results = Expr(
		res_tb = (lub << XPath("//TextBox")) << get text;
		res_check = (lub << XPath("//CheckBoxBox")) << get;
		res_teb = (lub << XPath("//TextEditBox")) << get text;
	);

	If(!Is Empty(col << get property("List Check")),
		valid_vals = "List Check is set for this column.\!NValid values are: " || Char(Arg(col << get property("List Check"), 1)),
		valid_vals = "List Check not set for this column."
	);

	nw = New Window("Simple Modal Recode",
		<<modal,
		<<returnresult,
		H List Box(
			Panel Box("Select entries to recode:",
				Text Box("Column name: " || Char(col << get name)),
				Border Box(Left(5), Right(5), bottom(5), top(5), sides(0), lub = rc_list),
				Text Box(valid_vals, <<set wrap(200))
			),
			Lineup Box(N Col(1), Button Box("OK", get_results), Button Box("Cancel"))
		)
	);

	If(nw["Button"] == -1 | Sum(res_check) == 0,
		Print("Recode cancelled by user.");
		Return();
	);

	// first three text boxes in the line up box are the column headings so remove from original values list
	Remove From(res_tb, 1, 3);

	recode_array = {};
	// create array of replacement values: { old_value1, new_value1, old_value2, new_value2, ... }
	For(j = 1, j <= N Items(res_check), j++,
		If(res_check[j] == 1,
			Insert Into(recode_array, res_tb[j]);
			Insert Into(recode_array, res_teb[j]);
		)
	);

	dt << Begin Data Update;
	Try(
		For Each Row(dt, dt:col[] = Map Value(dt:col, Eval List(recode_array), Unmatched(dt:col))),
		If(!Is Empty(col << get property("List Check")),
			New Window("Recode Failed",
				<<modal,
				Text Box("Recode of column " || Char(col << get name) || " Failed."),
				Text Box("The list check property is set and values are limited to the following: "),
				Text Box(Char(Arg(col << get property("List Check"), 1)))
			),
			Print("Recode failed!")
		)
	);
	dt << End Data Update;
	dt << show window(1);
	Return();

);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
modal_recode(dt, Column(dt, "Sex"));

If you really want to use XPath for this, you can dynamically calculate the index. You can for example get all the CheckBoxBoxes and then use Contains with this to get the index and then use that index

idx = Contains(rc_list << XPath("//CheckBoxBox"), this);

[#5]

Thank you @jthi! The prev sib approach works for me. I need to change the way I naturally think about how JMP windows are constructed and how they can be manipulated.

[#6]

I have changed my thinking many times and I still do from time to time. I think I generally use four different methods (annd combination(s) of these) depending on situation:

• XPath
  • If I want to collect results from multiple things on button press
• Collect the references to list/associative array
  • Sometimes it just is easy to collect the reference to an object
• << Set Function(function(this()))
  • Usually to control some actions, like in your case the enable(0/1). I don't usually update any variable values with this
• Reference evaluation
  • No specific examples, maybe when for some reason building the "path" with XPath or using this gets annoying and I still need the reference (sometimes in classes or functions which return new window)

Reference evaluation example

rc_list << append(teb = Text Edit Box(Char(orig_vals[i]), <<enabled(0)));
// needs to be constructed as a string so that the index i can be inserted into the XPath("(//TextEditBox)[i])
Eval(EvalExpr(
	rc_list << Append(Check Box("", <<Set Function(Function({this}, Expr(teb) << Enabled(this << get)))));
));

[#7]

Bravo! It's amazing how these sessions continue being sources of immediately useful knowledge since their inception in 2023.

I am wondering whether there's an opinion on SQL inside the Open Database() JSL function? We are using an implementation of Google's BigQuery for data warehousing. Inside BigQuery, it is possible to develop and debug SQL queries that can be copy-pasted directly into Open Database() like shown below. The possibility to concatenate SQL keywords or timestamps into the SQL appears very useful too.

Names Default to Here(1);

start = "'2025-02-18 00:00:00'"; 
stop = "'2025-02-18 01:00:00'";

dt = Open Database( "DSN_name", "

	SELECT 
		time,
		tagname,
		value
	  
	FROM 
		`bq_project.dataset.table`

	WHERE 
		time BETWEEN TIMESTAMP("|| start ||") AND TIMESTAMP("|| stop ||")
	  
	AND tagname IN(
		'tag_1',
		'tag_2' 
		)"
);

[#8]

Use New SQL Query (I know it is quite horrible... but it is the preferred method) and utilize sql template and then evaluate it using Eval Insert would be my suggestion. Example below:

Names Default to Here(1);

MY_CONNECTOR = "ODBC:...";

start = "'2025-02-18 00:00:00'"; 
stop = "'2025-02-18 01:00:00'";

sql_template = "\[
SELECT 
	time,
	tagname,
	value  
FROM 
	`bq_project.dataset.table`
WHERE 
	time BETWEEN TIMESTAMP(¤start¤) AND TIMESTAMP(¤stop¤)
AND tagname IN(
	'tag_1',
	'tag_2' 
	)
]\";

sql_str = Eval Insert(sql_template, "¤");

// Write("\!N", sql_str);

obj = New SQL Query(
	Connection(MY_CONNECTOR),
	Custom SQL(sql_str),
	Query Name("mytable")
);
dt = obj << Run Foreground();

Write();

You can use similar template idea for other methods than New SQL Query (like the Open Database()).

I use special character "¤" in my Eval Insert() because the default "^" is commonly found for example from Regex and it will cause issues during evaluation.

Edit: Fixed few typos, improved language and added comment about "¤"

[#9]

There's only so many kudos I can give for a single reply. Thanks, @jthi, really.