Help, my script run OK in a Script Window but fail when run in a Column Formula

[#1]

Hello,

I created a script to summarize a data table by analyzing the individual statistical impact of M factors on N metrics.

The summary table show the matrix of N metrics (rows) with M factors (columns), with the specific impact: Mean shift, Cpk degradation, or both)

I want to add a feature to plot the distribution for each selected row with a column formula.

I got inspired by this post:

Linking a cell (in a JMP table) to a script? 

The problem is, my script works well in a New Script Window with a fixed selected row ([5] in this example), but then fail when I include the script in the :Graph column formula. I don’t understand why…

Could you please help me debug this?

Attached 2 tables: datatable_test.jmp with the raw data, Summary.jmp with the column formula. The idea is to click on the cell you want to plot in the :Graph column.

Currently the error shows: :”Illegal Reference (recursion) at row [ ]”

When I run the following script with both tables open, it works as expected (see below). But the script fails when run from the table directly (click on cell in Graph column).

Thanks a lot in advance,

-Voiz 

// this script works from a Script window
// All features related to the column formula are commented
// here the row selected is row 5 : rn1 = [5]

Names Default To Here( 1 );
dt = Data Table( "datatable_test" );
dt2 = Data Table( "Summary" );
//If( Selected( Row State() ),
	Eval(
		Parse(
			rn1 = [5]/*dt2 << get selected rows*/;
			rn = rn1[1];
			Factors = dt2 << get column names( "string" );
			show(rn1, rn);
			Eval(
				Eval Expr(
					For( i = N Items( Factors ), i >= 1, i--,
						CurrCol = Column( dt2, Factors[i] );
						If(Char( CurrCol[ rn ]   ) == "Ok" | Factors[i] == "Metric"| Factors[i] == "Graph",
							Remove From( Factors, i, 1 )
						);
					)
				)
			);
			FactorA = "X( :" || Char( Factors[1] ) || ", Position( 1 ))";
			FactorB = "X(1)";
			For( j = 2, j <= N Items( Factors ), j++,
				FactorA = Char( FactorA ) || ",X( :" || Char( Factors[j] ) ||
				", Position( 1 ))";
				FactorB = Char( FactorB ) || ",X( " || Char( j ) || " ) ";
			);
			Show( dt2:Metric[rn], FactorA, FactorB );
			Key = dt2:Metric[rn];
			Eval Insert(
				" dt << Graph Builder(
				Size( 534, 464 ),
				Show Control Panel( 0 ),
				Show Legend( 0 ),
				Variables(
				Y( ^Key^ ), ^FactorA^),
				Elements( Box Plot( Y, Legend( 18 ), ^FactorB^ ) ),
				SendToReport(
				Dispatch(
					{},
				
				ScaleBox,
				{Min( -0.485125992718803 ), Max( 5.53364268893602 ), Inc( 1 ),
				Minor Ticks( 0 )}
				)
				)
			) "
			);
		)
	//)
);

[#2 · Accepted solution]

Is there a specific reason to do this with a column formula? For example having custom a window with table box and button(s) would be much easier to build and manage. Or maybe using row state handler on summary table (though I would avoid that if possible).

[#3]

Hi Jarmo,

No specific reason, I just created my original script this way, using a data table to generate the summary.

I didn't think of that: opening a table box and add buttons seems a nice idea, I could modify my future version with that in mind.

But in the meantime, do you think making the column formula work is worth it? So I can have a working version faster before the next version? I am afraid changing the structure to generate a table box instead of a data table will take me some time, as I am not very familiar with display / table box yet.

The piece of script I want to include in the Column formula is working on its own, but fail when run in the column formula. I must do something wrong but not sure what ...

[#4]

Edit:

I actually found out that converting a table to a display box was easy:

nw = New Window("Report",
	dt << Get As Report()
);

Report generated below

I didn't expect the formula to work in the report, when click on row (and it does not...).

But now I am not sure how you would suggest to proceed with your suggestion. 

(again I am not familiar yet with Display box manipulation)

Thanks in advance 

[#5 · Accepted solution]

Edit 2:

I finally made the script to work, following the recommendation from Jarmo and this post:

JSL Help with: Get As Report & Table Box 

Now when I click on a row in the report, it run the script based on the selected row, which is exactly what I needed

Script below, it needs the 2 tables open that I joined in my first message

Thanks 

- voiz

Names Default To Here( 1 );

dt7 = Data Table("Summary");
dt = Data Table("datatable_test");

dt7 << clear select;
dt7 << Clear Column Selection();

RptWin = New Window( "Data Table as report",
	Outline Box("Data Table as Report",
		dt7_Rpt = dt7 << Get as Report(); 
	)
);

tb = dt7_Rpt[Table Box( 1 )];

tb << Set Heading Column Borders( 1 );
tb << Set Selectable Rows( 1 );
tb << Set Row Change Function(
Function( {this},
	{SelRows},
	SelRows = this << Get Selected Rows;
	If(
		N Items( SelRows ) > 1, Throw( "Too many items selected!\!rOnly one selection possible." ),
		N Items( SelRows ) == 1,
			SelRows = this << Get Selected Rows;
	);
	dt7 << Clear Select;
	rn = dt7 << Select rows( SelRows ) << get selected rows;
	show (rn[1], dt7:Metric[rn]);
		
	Factors = dt7 << get column names( "string" );
			
	Eval(
		Eval Expr(
			For( i = N Items( Factors ), i >= 1, i--,
				CurrCol = Column( dt7, Factors[i] );
				If(Char( CurrCol[ rn[1] ]   ) == "Ok" | Factors[i] == "Metric"| Factors[i] == "Graph",
					Remove From( Factors, i, 1 )
				);
			)
		)
	);
	show (Factors);
	Try(FactorA = "X( :" || Char( Factors[1] ) || ", Position( 1 ))");
	FactorB = "X(1)";
	For( j = 2, j <= N Items( Factors ), j++,
		Try(FactorA = Char( FactorA ) || ",X( :" || Char( Factors[j] ) || ", Position( 1 ))");
		FactorB = Char( FactorB ) || ",X( " || Char( j ) || " ) ";
	);
	Show( dt7:Metric[rn], FactorA, FactorB );
	Key = dt7:Metric[rn];
	If (Factors != {},
		Eval(Parse(Eval Insert(
			" dt << Graph Builder(
				Size( 534, 464 ),
				Show Control Panel( 0 ),
				Show Legend( 0 ),
				Variables(Y( ^Key^ ), ^FactorA^),
				Elements( 
					Box Plot( Y, Legend( 18 ), ^FactorB^ ), 
					Caption Box(X, Y, Legend(20), Summary Statistic( \!"N\!" ),Location( \!"Graph per factor\!" ))),
				SendToReport(
				Dispatch(
				{},
				ScaleBox,
				{Min( -0.485125992718803 ), Max( 5.53364268893602 ), Inc( 1 ),
				Minor Ticks( 0 )}
			)
			)
		) "
		))),
		Eval(Parse(Eval Insert(
			" dt << Graph Builder(
				Size( 534, 464 ),
				Show Control Panel( 0 ),
				Show Legend( 0 ),
				Variables(Y( ^Key^ )),
				Elements( 
					Box Plot( Y, Legend( 18 )), 
					Caption Box(Y, Legend(20), Summary Statistic( \!"N\!" ))),
				SendToReport(
				Dispatch(
				{},
				ScaleBox,
				{Min( -7.06199976676385 ), Max( 26.8097086791615 ), Inc( 5 ),
				Minor Ticks( 0 )}
			)
			)
		) "
		)))	
	)
));

[#6]

One more improvement I would try to do, is to get rid of Eval(Parse()) (it usually makes the script easier to debug and you don't learn bad habits). But it will take some effort and different techniques so you could work on it slowly.

I can also take a look in ~12hours so you can get an idea what you could potentially do.

[#7]

Agree. I'd go a step further and replace the Eval Insert() as well.

Concerning the Eval(Parse()) - you can just skip it:

Parse expects a string as argument.

An expression will be evaluated and the the resulting String is parsed and returned as an expression.

Parse(Print(1);"a"||"b")

So, 

Parse(Print(1);Eval Insert(Graph Builder());"a")

will print 1, create the graph and return a as an expression. If you are just interested in the center part, you can skip the Parse.

Wrapping Parse(Eval Insert()) with Eval() will still evaluate it.

On the other hand, Eval(Eval Expr()) is just needed to evaluate an Expr() which is inside.

Without an Expr(), you can remove it from the code without harm.

In general, I'd propose to post a code which is based on a data set from Help/Sample Data Folder.

Then the code can be execute right away without the need to download files via the internet.
The additional benefit:  adapting the code to different inputs often helps to understand the issue.

Often, when I have the right code to post the question - the question is gone : )

[#8]

This does look complicated (and it is) but this is how I would most likely approach this (it isn't the only way, you can also utilize << add variables/<< add elements)

Names Default To Here(1);

dt_summary = Open("$DOWNLOADS/Summary.jmp");
dt = Open("$DOWNLOADS/datatable_test.jmp");
dt << New Column("Ones", Numeric, Nominal, Set Each Value(1));

dt_summary << clear select;
dt_summary << Clear Column Selection();

possible_factors = dt_summary << get column names("string");
Remove From(possible_factors, Contains(possible_factors, "Metric"));
Remove From(possible_factors, Contains(possible_factors, "Graph"));



create_report = function({sel_row}, {Default Local},
	valid_factor_idx = Loc(dt_summary[sel_row, possible_factors], "Ok");
	valid_factors = possible_factors[valid_factor_idx];
	
	key = dt_summary[sel_row, "Metric"];

	If(N Items(valid_factors) == 0,
		valid_factors = {"Ones"};
	);

	variables_expr = Expr(Variables());
	bp_expr = Expr(Box Plot(Y, Legend(18)));
	
	dt = dt; // JMP being JMP
	For Each({valid_factor, idx}, valid_factors,
		x_expr = EvalExpr(X(Expr(Name Expr(AsColumn(dt, valid_factor))), Position(1)));
		Insert Into(variables_expr, Name Expr(x_expr));
		Insert Into(bp_expr, Eval Expr(X(Expr(idx))));
	);

	y_expr = EvalExpr(Y(Expr(Name Expr(AsColumn(dt, key)))));
	Insert Into(variables_expr, Name Expr(y_expr));
	
	gb_expr = Substitute(
		Expr(dt << Graph Builder(
			Size(534, 464),
			Show Control Panel(0),
			Show Legend(0),
			_variables_,
			Elements(
				_boxplot_,
				Caption Box(X, Y, Legend(20), Summary Statistic("N"), Location("Graph per factor"))
			),
			SendToReport(
				Dispatch({}, ScaleBox, {Min(-7.06199976676385), Max(26.8097086791615), Inc(5), Minor Ticks(0)})
			)
		)),
		Expr(_variables_), Name Expr(variables_expr),
		Expr(_boxplot_), Name Expr(bp_expr)
	);
	gb = Eval(gb_expr);
	
	return();
);

nw = New Window("Data Table as report",
	Outline Box("Data Table as Report",
		summary_report = dt_summary << Get as Report();
	)
);

report_tb = summary_report[Table Box(1)];
report_tb << Set Heading Column Borders(1);
report_tb << Set Selectable Rows(1);

report_tb << Set Row Change Function(Function({this},
	selrows = this << Get Selected Rows;
	If(N Items(selrows) > 1, 
		Throw("Too many items selected!\!rOnly one selection possible.")
	);
	create_report(selrows[1]);
));

Write();

After this is working, I would start thinking maybe about the user experience:

• Maybe I should use a button which would create reports for selected rows when pressed?
• Could I let user select more than one row?
• Should I create all the reports to same window in that case?
• If graph has already been created, do I need to re-create it?
• When user closes the window, should I also close the data tables?
• ..

View more...

Names Default To Here(1);

dt_summary = Open("$DOWNLOADS/Summary.jmp");
dt = Open("$DOWNLOADS/datatable_test.jmp");
dt << New Column("Ones", Numeric, Nominal, Set Each Value(1));

dt_summary << clear select;
dt_summary << Clear Column Selection();

possible_factors = dt_summary << get column names("string");
Remove From(possible_factors, Contains(possible_factors, "Metric"));
Remove From(possible_factors, Contains(possible_factors, "Graph"));



create_report = function({sel_row}, {Default Local},
	valid_factor_idx = Loc(dt_summary[sel_row, possible_factors], "Ok");
	valid_factors = possible_factors[valid_factor_idx];
	
	key = dt_summary[sel_row, "Metric"];

	If(N Items(valid_factors) == 0,
		valid_factors = {"Ones"};
	);

	variables_expr = Expr(Variables());
	bp_expr = Expr(Box Plot(Y, Legend(18)));
	
	dt = dt; // JMP being JMP
	For Each({valid_factor, idx}, valid_factors,
		x_expr = EvalExpr(X(Expr(Name Expr(AsColumn(dt, valid_factor))), Position(1)));
		Insert Into(variables_expr, Name Expr(x_expr));
		Insert Into(bp_expr, Eval Expr(X(Expr(idx))));
	);

	y_expr = EvalExpr(Y(Expr(Name Expr(AsColumn(dt, key)))));
	Insert Into(variables_expr, Name Expr(y_expr));
	
	gb_expr = Substitute(
		Expr(dt << Graph Builder(
			Size(534, 464),
			Show Control Panel(0),
			Show Legend(0),
			_variables_,
			Elements(
				_boxplot_,
				Caption Box(X, Y, Legend(20), Summary Statistic("N"), Location("Graph per factor"))
			),
			SendToReport(
				Dispatch({}, ScaleBox, {Min(-7.06199976676385), Max(26.8097086791615), Inc(5), Minor Ticks(0)})
			)
		)),
		Expr(_variables_), Name Expr(variables_expr),
		Expr(_boxplot_), Name Expr(bp_expr)
	);
	
	return(gb_expr);
);

nw = New Window("Data Table as report",
	H List Box(
		Outline Box("Data Table as Report",
			summary_report = dt_summary << Get as Report();
		),
		Outline Box("Actions",
			Button Box("Create Reports",
				sel_rows = report_tb << Get Selected Rows;
				lub_collector = Lineup Box(N Col(2));
				For Each({selrow}, sel_rows,
					lub_collector << Append(create_report(selrow));
				);
				nw_graphs = New Window("Graphs",
					lub_collector
				);
				wait(0);
			),
			Button Box("Close Window", summary_report << close window);
		)
	)
);

report_tb = summary_report[Table Box(1)];
report_tb << Set Heading Column Borders(1);
report_tb << Set Selectable Rows(1);

I will edit and add images here if JMP Community ever lets me do it...

Edit: Images