Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
steven_gong
Level II

How to replace the text in JSL thru User Interface(a dialog window)

Hi,

I've been using SQL in my JSL, and declared 4 variables and set required value.(see below script)

What I want is to create a new window as user interface.. when run this JSL, pop up the window first to allow user to input different values for the variables.

For example: 

1st run JSL, user input: x,y,z,n for the 4 variables, to achieve the effect like below:

set @spec = 'x'
set @opn = 'y'
set @Loss = 'z'

set @day = n

2nd run JSL, user input: a,b,c,m for the 4 variables, to achieve the effect like below:

set @spec = 'a'
set @opn = 'b'
set @Loss = 'c'

set @day = m

=========================================

Script:

Names Default To Here( 1 );
ODBC = Create Database Connection( "___" );
SQL ="
declare @spec varchar(50)
declare @opn varchar(50)

declare @Loss varchar(50)
declare @day int

set @spec = 'xxx'
set @opn = 'yyy'
set @Loss = 'zzz'

set @day = n

 

select * from dt

''

 

I feel it difficult to me since I looked up the community, no found any clue on this.

So I'm make new discussion to ask for help.

Any help will be appreciated.

Looking forward to your help.

Thanks.

 

3 REPLIES 3
Highlighted
txnelson
Super User

Re: How to replace the text in JSL thru User Interface(a dialog window)

There are an unlimited ways to accomplish what you want.  Below is one simple interface, using your pseudo code.  You really need to read the Scripting Guide's sections on using Display Objects

Names Default To Here( 1 );
nw = New Window( "Set Values",
	Lineup Box( N Col( 2 ),
		Text Box( "Set Spec" ),
		specCB = Combo Box( {"x", "y", "z", "n"}, specVal = specCB << get Selected() ),
		Text Box( "Set opn" ),
		opnCB = Combo Box( {"x", "y", "z", "n"}, opnVal = opnCB << get Selected() ),
		Text Box( "Set Loss" ),
		lossCB = Combo Box( {"x", "y", "z", "n"}, lossVal = lossCB << get Selected() ),
		Text Box( "Set day" ),
		dayCB = Combo Box( {"x", "y", "z", "n"}, dayVal = dayCB << get Selected() )
	),
	theOK = Button Box( "OK", 
		// Build the statements to be executed
		theExpr = "ODBC = Create Database Connection( \!"___\!" );
		SQL =\!"
		declare " || specVal || " varchar(50)
		declare " || opnVal || " varchar(50)
		declare " || lossVal || " varchar(50)
		declare " || dayVal || " int

		set " || specVal || " = 'xxx'
		set " ||  opnVal || " = 'yyy'
		set " || lossVal || " = 'zzz'
		set " || dayVal || " = n
		 
		select * from dt
		";
		show(theExpr);
		// run the expression (uncomment when code is real)
		//Eval( Parse( theExpr ) );
		
		nw << close window;
	)
);

 

Jim
Highlighted
steven_gong
Level II

Re: How to replace the text in JSL thru User Interface(a dialog window)

Hi txnelson,

Thanks very much for the guidance with complete script.

I've learned the new window with combo box setting to create the user interface with defined variable.

The tricky thing is how to use JSL variable to replace the SQL variable.

I learned similar thing in below link: How to get content of text edit box into "variable"? 

Thanks to @pmroz.

 

and I make my script work as expected.

Here is my final scrip for information.

==============================

Names Default To Here( 1 );
specVal = "";
NW = New Window( "Input Spec",
	<<Modal, 
	<<Return Result,
	
	Text Box( "Set Spec" ),
	specCB = Combo Box( {"DA", "WB", "MOLD"} ), 
	//specCB = Text Edit Box( "WB" ),
	
	
	Button Box( "OK", specVal = specCB << get selected() )
	//Button Box( "OK", specVal = specCB << get text() )
				
);

If( specVal != "", 

	(ODBC = Create Database Connection( "___" ) ;
	SQL = Eval Insert(
		"
DECLARE @SPEC VARCHAR(50)
SET @SPEC = '^specVal^'
SELECT WL.ContainerName,WL.ProductName, WL.SPECNAME
   FROM A_WIPLOT WL
WHERE WL.SPECNAME LIKE CONCAT('%',@SPEC,'%')
ORDER BY WL.SPECNAME
"
	) ; 
	//show(SQL); to see the log to confirm if SQL statement meet the requirement
	DT = Execute SQL( ODBC, SQL, "SPEC_WIP" ) << Maximize window ; 

	SUMMARY = DT << Summary( Group( :SPECNAME ), Freq( "None" ), Weight( "None" ) ) ;
	Close Database Connection( ODBC ) ; 
	
	)
);

 

 

Highlighted
steven_gong
Level II

Re: How to replace the text in JSL thru User Interface(a dialog window)

I don't know if the subject name can be re-named.

Just for record: the subject should be - how to pass the JSL variable to SQL variable thru User Interface.

Article Labels

    There are no labels assigned to this post.