Multiple input for SQL data pull using JMP script.

ConfidenceOwl94

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.

jthi

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"];