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

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

NBrammer — Tue, 29 Jul 2025 11:47:51 GMT

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(),
	V List Box(
		Text Box("Select Primary Table (with TS):"),
		lb1 = List Box(dtNames, width(600), nlines(8), MaxItems(1)),
		Spacer Box(Size(10, 10)),
		Text Box("Select Secondary Table (with START/END TIME):"),
		lb2 = List Box(dtNames, width(600), nlines(8), MaxItems(1)),
		Spacer Box(Size(10, 10)),
		H List Box(
			Button Box("OK",
				primaryTableName = lb1 << Get Selected;
				secondaryTableName = lb2 << Get Selected;
				If(Is Missing(primaryTableName) | Is Missing(secondaryTableName),
					New Window("Error", <<Modal(), Text Box("Please select both tables."));
					Stop();
				);
				If(primaryTableName == secondaryTableName,
					New Window("Error",
						<<Modal(),
						Text Box("Please select two different tables.")
					);
					Stop();
				);
// Close dialog
				Window(
					"Data Table Selector - Note both data tables must be open in this JMP session"
				) << Close Window;
			),
			Button Box("Cancel",
				Window(
					"Data Table Selector - Note both data tables must be open in this JMP session"
				) << Close Window
			)
		)
	)
);

// Step 3: Get references and file paths
dtPrimary = Data Table(primaryTableName[1]);
dtSecondary = Data Table(secondaryTableName[1]);
primaryPath = dtPrimary << Get Path;
secondaryPath = dtSecondary << Get Path;

// Check if tables are saved
If(Is Missing(primaryPath) | Is Missing(secondaryPath),
	New Window("Error",
		<<Modal(),
		Text Box("Both tables must be saved JMP files. Please save and try again.")
	);
	Throw("Both tables must be saved JMP files. Please save and try again.");
);

// Step 4: Get all column names from both tables
primaryCols = dtPrimary << Get Column Names("String");
secondaryCols = dtSecondary << Get Column Names("String");

// Step 5: Build Select() clause for all columns
selectExprList = {};
For(i = 1, i <= N Items(primaryCols), i++,
	Insert Into(selectExprList, Expr(Column(COLNAME, t1)))
);
For(i = 1, i <= N Items(secondaryCols), i++,
	Insert Into(selectExprList, Expr(Column(COLNAME, t2)))
);

// Substitute actual column names into Select() expressions
For(i = 1, i <= N Items(primaryCols), i++,
	selectExprList[i] = Substitute(selectExprList[i], Expr(COLNAME), primaryCols[i])
);
For(i = 1, i <= N Items(secondaryCols), i++,
	selectExprList[N Items(primaryCols) + i] = Substitute(
			selectExprList[N Items(primaryCols) + i],
		Expr(COLNAME), secondaryCols[i]
	)
);

// Step 6: Build the SQL Query using unquoted Expr tokens for substitution
sqlExpr = Expr(
	New SQL Query(
		Version(130),
		Connection("JMP"),
		JMP Tables(["PRIMARYNAME" => "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);

Edit (jthi): added JSL formatting

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

jthi — Tue, 29 Jul 2025 12:01:04 GMT

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

NBrammer — Tue, 29 Jul 2025 16:20:35 GMT

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",
<<Modal(),
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", <<Modal(), Text Box( "Please select both tables." ) );
Stop();
);
t1name = t1sel[1];
t2name = t2sel[1];
If( t1name == t2name,
New Window( "Error", <<Modal(), Text Box( "Please select two different tables." ) );
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 ),
<<Select Columns( "All"),
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;>