topic Re: Multiple input for SQL data pull using JMP script. in Discussions

Multiple input for SQL data pull using JMP script.

ConfidenceOwl94 — Wed, 18 Jun 2025 01:47:23 GMT

I have a JSL script that pulls data from SQL. Currently, I have a list of order numbers in a table, and I am using that to pull data for all unique shop orders.

I would like to add a popup function to enter multiple shop orders. I was able to do that for one order. How can add multiple shop order in a pop up box?

Below is the script I came up with.

Names Default To Here(1);
Clear Globals();
//dt = current data table();
//Summarize(dt, uniq = By(:Column 1));
//sql_in = ConcatItems(uniq,",");

//****************************** Enter Leakage current below****************
Shop Order = New Window( "Enter Shop order",
	<<Modal,
	<<Return Result,
	Text Box( " " ),
	variablebox = Number Edit Box( ),
	Button Box( "OK" ),
	Button Box( "Cancel" )
);

Write( Shop Order["variablebox"] );

sql_in = Shop Order["variablebox"];

/********************Connection Strings*******************************/
TestDataConnString = 			"DRIVER=SQL Server; SERVER=X ; UID=X; PWD=X;				DATABASE=X";


eval(parse(evalinsert("\[ 

	A = Open Database(TestDataConnString, "SELECT 
	a.[ShopOrderId] ,[BuildSerialNumber] ,[DateCode] ,a.[ItemNumber], t4.ItemNumber AS PartNumber
    	,string_agg(a.[LotNumber],', ') AS Lots
	FROM [Employee].[dbo].[vw_LotQuery] a
	JOIN Employee.dbo.MFGTravelerShopOrders t2 with (NOLOCK) ON t2.ShopOrderID=a.ShopOrderId
    JOIN Employee.dbo.MFG_ItemMasters t4 with (NOLOCK) ON t4.ItemNumber=t2.ItemNumber
 
	WHERE 
		(a.[ShopOrderId] IN (^sql_in^))

		AND
		  (a.[ItemNumber] LIKE '%PM%' OR a.[ItemNumber] LIKE '%PW%')
	Group by a.[ShopOrderId] ,[BuildSerialNumber] ,[DateCode] ,a.[ItemNumber], t4.ItemNumber
	Order by [BuildSerialNumber]

	","Wafer Information")
	
]\")));;

list.

Re: Multiple input for SQL data pull using JMP script.

jthi — Wed, 18 Jun 2025 04:49:10 GMT

You could use text edit box and then parse the user input (use either << set function or parse it after you have the result)

Names Default To Here(1);

nw = New Window("Enter Shop order",
	<<Modal,
	<<Return Result,
	V List Box(
		Text Box(" "),
		variablebox = Text Edit Box("", << Set N Lines(2), << Set Width(200)),
		Spacer Box(Size(0, 10)),
		Button Box("OK"),
		Button Box("Cancel")
	)
);

Or you could add + Button to add more number edit boxes

Names Default To Here(1);

nw = New Window("Enter Shop order",
	<<Modal,
	<<Return Result,
	V List Box(
		Text Box(" "),
		H List Box(align("top"),
			Button Box("+",
				lub << Append(Number Edit Box())
			),
			lub = Lineup Box(N Col(1),
				Number Edit Box()
			)			
		),
		Spacer Box(Size(0, 10)),
		Button Box("OK",
			inputs = (lub << XPath("//NumberEditBox")) << get;
		),
		Button Box("Cancel")
	)
);

or utilize number col edit box

Names Default To Here(1);

nw = New Window("Enter Shop order",
	<<Modal,
	<<Return Result,
	V List Box(
		Text Box(" "),
		H List Box(align("top"),
			Button Box("+",
				tb << Add Row({"Number", .})
			),
			tb = Table Box(
				ncb = Number Col Edit Box("Number", [.])
			)
		),
		Spacer Box(Size(0, 10)),
		Button Box("OK"),
		Button Box("Cancel")
	)
);

Re: Multiple input for SQL data pull using JMP script.

ConfidenceOwl94 — Wed, 18 Jun 2025 17:04:01 GMT

Thanks Jarmo,

Text edit box function worked great.

When I enter multiple order number in a same raw and replace space with "," it worked fine.

I want to make it robust. How can I handle the input when it is entered vertically.(I need this when I copy list from excel.)

Names Default To Here(1);

nw = New Window("Enter Shop order",
	<<Modal,
	<<Return Result,
	V List Box(
		Text Box(" "),
		variablebox = Text Edit Box("", << Set N Lines(5), << Set Width(200)),
		Spacer Box(Size(0, 10)),
		Button Box("OK"),
		Button Box("Cancel")
	)
);

Write( nw["variablebox"] );

unique = nw["variablebox"];

sql_in = Substitute( unique, " ", "," );

Re: Multiple input for SQL data pull using JMP script.

jthi — Wed, 18 Jun 2025 17:26:52 GMT

Best method I have come up with has generally been Regex but the pattern depends on your use case. In this case you could maybe just use Collapse Whitespace + Words

Names Default To Here(1);

nw = New Window("Enter Shop order",
	<<Modal,
	<<Return Result,
	V List Box(
		Text Box(" "),
		variablebox = Text Edit Box("", <<Set N Lines(5), <<Set Width(200)),
		Spacer Box(Size(0, 10)),
		Button Box("OK"),
		Button Box("Cancel")
	)
);

unique = Collapse Whitespace(nw["variablebox"]);
sql_in = Associative Array(Words(unique, ", ")) << get keys;
sql_in = Concat Items(sql_in, ", ");

You could also clean the input in such a way that user will see it when they commit the change

Names Default To Here(1);

nw = New Window("Enter Shop order",
	<<Modal,
	<<Return Result,
	V List Box(
		Text Box(" "),
		variablebox = Text Edit Box("", 
			<< set function(function({this},
				unique = Collapse Whitespace(this << get text);
				sql_in = Concat Items(Associative Array(Words(unique, ", ")) << get keys, ", ");
				this << set text(sql_in);
			))
			, << Set N Lines(5)
			, <<Set Width(200)
		),
		Spacer Box(Size(0, 10)),
		Button Box("OK"),
		Button Box("Cancel")
	)
);

unique = nw["variablebox"];