890270_en-US

liliconcom

JMP query builder path name issues when selecting tables through a dialogue box

I am using a dialogue box to select the primary and secondary tables but when the JMP query builder runs it fails to substitute the file path names correctly, even if I manually resolve this the next time the script needs to reference the tables it falls over
Error: Name Unresolved: PRIMARYNAME in access or evaluation of 'PRIMARYNAME' , PRIMARYNAME/*###*/

 

// Step 1: Get names of all open data tables
dtNames = {};
For(t = 1, t <= N Table(), t++,
	Insert Into(dtNames, Data Table(t) << Get Name)
);

// Step 2: Dialog to select two tables
nw2 = New Window("Data Table Selector - Note both data tables must be open in this JMP session",
	<<MODAL> "PRIMARYPATH", "SECONDARYNAME" => "SECONDARYPATH"]),
		QueryName(PRIMARYNAME || " join " || SECONDARYNAME),
		Select(__SELECTALLCOLUMNS__),
		From(
			Table(PRIMARYNAME, Alias("t1")),
			Table(
				SECONDARYNAME,
				Alias("t2"),
				Join(
					Type(Left Outer),
					LE(Column("TS", "t1"), Column("END TIME", "t2")) &
					GE(Column("TS", "t1"), Column("START TIME", "t2"))
				)
			)
		)
	) << Modify
);

// Step 7: Substitute table names, paths, and all columns
sqlExpr = Substitute(sqlExpr,
	"PRIMARYNAME", primaryTableName[1],
	"PRIMARYPATH", primaryPath,
	"SECONDARYNAME", secondaryTableName[1],
	"SECONDARYPATH", secondaryPath,
	Expr(__SELECTALLCOLUMNS__), Expr(selectExprList)
);

// Debug: Show the final SQL Query code (optional, for troubleshooting)
Write(Expr(sqlExpr));

// Step 8: Run the query
Eval(sqlExpr);</MODAL>

Edit (jthi): added JSL formatting

Automation and ScriptingRe: JMP query builder path name issues when selecting tables through a dialogue box

I would build the table name + link associative array outside of the New SQL Query and the evaluate that

Names Default To Here(1);

t1name = "Big Class";
t1path = "$SAMPLE_DATA/Big Class.jmp";

t2path = "$SAMPLE_DATA/Big Class Families.jmp";
t2name = "Big Class Families";

aa = Associative Array();
aa[t1name] = t1path;
aa[t2name] = t2path;

sqlExpr = Expr(
	New SQL Query(
		Version(130),
		Connection("JMP"),
		JMP Tables(_aa_),
		QueryName(_t1name_ || " join " || _t2name_),
		Select,
		From(
			Table(_t1name_, Alias("t1")),
			Table(
				_t2name_,
				Alias("t2"),
				Join(Type(Left Outer), EQ(Column("name", "t1"), Column("name", "t2")))
			)
		)
	) << Modify
);

q = Substitute(Name Expr(sqlExpr),
	Expr(_aa_), aa,
	Expr(_t1name_), t1name,
	Expr(_t2name_), t2name,
	Expr(_t1path_), t1path,
	Expr(_t2path_), t2path,
);

Eval(q);

Re: JMP query builder path name issues when selecting tables through a dialogue box

Thank you I used that concept and solved the problem.

Final code below in case of use to anyone else

<

// Step 1: Get names of all open data tables
dtNames = {};
For( t = 1, t <= N Table(), t++,
Insert Into( dtNames, Data Table( t ) << Get Name )
);

// Step 2: Dialog to select two tables
t1name = "";
t2name = "";

nw2 = New Window( "Data Table Selector - Note both data tables must be open in this JMP session",
<V List Box(
Text Box( "Select Primary Table (MES extract table with TS column):" ),
lb1 = List Box( dtNames, width( 600 ), nlines( 8 ), MaxItems( 1 ) ),
Spacer Box( Size( 10, 10 ) ),
Text Box( "Select Secondary Table (Data Table with START/END TIME in columns):" ),
lb2 = List Box( dtNames, width( 600 ), nlines( 8 ), MaxItems( 1 ) ),
Spacer Box( Size( 10, 10 ) ),
H List Box(
Button Box( "OK",
t1sel = lb1 << Get Selected;
t2sel = lb2 << Get Selected;
If( N Items( t1sel ) == 0 | N Items( t2sel ) == 0,
New Window( "Error", <Stop();
);
t1name = t1sel[1];
t2name = t2sel[1];
If( t1name == t2name,
New Window( "Error", <Stop();
);

),
Button Box( "Cancel", nw2 << Close Window )
)
)
);

// Now t1name and t2name are strings with the selected table names

// Example: Use in SQL Query (assuming both tables are open)
dtJoin = New SQL Query(
Version(130),
Connection("JMP"),
JMP Tables( Associative Array( {t1name, Data Table(t1name), t2name, Data Table(t2name)} ) ),
QueryName( t1name || " join " || t2name ),
<From(
Table( t1name, Alias( "t1" ) ),
Table(
t2name,
Alias( "t2" ),
Join(
Type( Left Outer ),
LE( Column( "TS", "t1" ), Column( "END TIME", "t2" ) ) &
GE( Column( "TS", "t1" ), Column( "START TIME", "t2" ) )
)
)
)
) << Run;>