Interactive Window before a SQL pata pull

[#1]

Hello 

I have the following Question:

how can I generate an addition to the script that I use to pull data from a SQL database, in which I would like to replace the  Advance search function “Where” by an interactive Window in which the user types what he actually wants to pull, here eg 06C3020 so that only data is pulled that has this expression in a specific column (here HighestLevelOrder_Material)?

Here the resulting script for the data pull looks somthong like this:

Open Database(

"Description=Tracking Test5;DRIVER=SQL Server;SERVER=CWA565656P\SQLEXPRESS;UID=SVC-Tech-R;PWD=TestPW;APP=JMP;WSID=ADDDE1L5HS573;DATABASE=Tracking_and_Trending;",

"SELECT * FROM [dbo].[vw_TechOps_DashboardData_MB56TopDowns] WHERE dbo.vw_TechOps_DashboardData_MB56TopDowns.HighestLevelOrder_Material = '06C3020’”

)

Thank you and best regards,

Matthias

[#2]

Introduce a dialog window in your script before the pull. There are a variety of user interface objects (display boxes) to help with the construction of this dialog. The choices will depend on the user experience you want to have. There are three methods for unloading the dialog after the user dismisses it to get their selection. Use string substitution to replace a token in the Where clause of the SQL string with the values the user selects. Then use the string as the second argument when you call the Open Database() function.

[#3]

Hello Mark 

Thank you for your help. Do you have an example script that I might try to customize for me?

Thank you and best regards,

Matthias

[#4]

Hello Mark,

as I am rather a JMP user than a SQL user could you give me an scripted example? Thank you and best regards

Matthias

[#5]

Your request is vague, but this simple example illustrates my suggested approach.

Names Default to Here( 1 );

// dialog to ask user for parameter and value for query
dialog = New Window( "Custom Query", << Modal,
	Panel Box( "Parameter and Value",
		Line Up Box( N Col( 2 ),
			Text Box( "Enter Parameter Name" ),
			nameTEB = Text Edit Box( "HighestLevelOrder_Material" ),
			Text Box( "Enter Parameter Value" ),
			valueTEB = Text Edit Box( "06C3020" )
		)
	),
	H List Box(
		Button Box( "OK",
			name  = nameTEB << Get Text;
			value = valueTEB << Get Text;
		),
		Button Box( "Cancel" )
	)
);

If( dialog["Button"] != 1, Throw( "User cancelled" ) );

// modify query by splicing name and value into second argument
Substitute(
	"SELECT * FROM [dbo].[vw_TechOps_DashboardData_MB56TopDowns] WHERE dbo.vw_TechOps_DashboardData_MB56TopDowns.NNNNN = 'VVVVV'",
	"NNNNN", name,
	"VVVVV", value
);
Open Database(
	"Description=Tracking Test5;DRIVER=SQL Server;SERVER=CWA565656P\SQLEXPRESS;UID=SVC-Tech-R;PWD=TestPW;APP=JMP;WSID=ADDDE1L5HS573;DATABASE=Tracking_and_Trending;",
	query
);