topic How to prompt user to name SQL query output data table and add date and time to filename? in Discussions

How to prompt user to name SQL query output data table and add date and time to filename?

FabioSaragoni — Sat, 10 Jun 2023 23:31:39 GMT

Dear all,

I am asking for your expert advice on the following topic, since I am really new to JMP scripting.

I made a SQL query (invisible) through the Query builder but I need help to:

- prompt the user to insert data table file name (pop-up window);

- add date and time (ddmonyyyy:h:m:s) to the output data table

What I managed to script is here below:

Names Default To Here( 1 );
dt=New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:DSN=DB001.00.G_FE_JMP15;DBQ=Y:\OFFICIAL_DATABASES\QA_Admin\Stability\DB001.00.G.StabilityStudies_FE_StudiesCompiler_JMP15.accdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"
	),
	QueryName( "Query_DB001.00.G.StabilityStudies_FE_StudiesCompiler_10Jun2021" ),
	Select(
Column( "StudyID", "t1" ),
		Column( "TimePointID", "t1" ),
		Column( "StabilityClimaticConditions", "t1" ),
		Column( "TestMonth", "t1" ),
		Column( "ProgramComments", "t1" ),
		Column( "Test1", "t1" ),
		Column( "Test1Specification", "t1" ),
		Column( "Test1Units", "t1" ),
		Column( "Test 01", "t1" ),
		Column( "intTest1", "t1" ),
		Column( "Test2", "t1" ),
		Column( "Test2Specification", "t1" ),
		Column( "Test2Units", "t1" ),
		Column( "Test 02", "t1" ),
		Column( "intTest2", "t1" ),
		Column( "Test3", "t1" ),
		Column( "Test3Specification", "t1" ),
		Column( "Test3Units", "t1" ),
		Column( "Test 03", "t1" ),
		Column( "intTest3", "t1" ),
		Column( "Test4", "t1" ),
		Column( "Test4Specification", "t1" ),
		Column( "Test4Units", "t1" ),
		Column( "Test 04", "t1" ),
		Column( "intTest4", "t1" ),
		Column( "Test5", "t1" ),
		Column( "Test5Specification", "t1" ),
		Column( "Test5Units", "t1" ),
		Column( "Test 05", "t1" ),
		Column( "intTest5", "t1" ),
From( Table( "qry_ResultsUnion01_35", Alias( "t1" ) ) ),
	Where(
		EQ(
			Column( "StudyID", "t1" ),
			_AllRows_,
			UI(
				Comparison(
					Base(
						"Continuous",
						Prompt(
							"StudyID:",
							Character,
							PromptName( "t1.StudyID_1" )
						)
					)
				)
			)
		) & In List(
			Column( "StabilityClimaticConditions", "t1" ),
			_AllRows_,
			UI(
				SelectListFilter(
					ListBox,
					Base(
						"Categorical",
						Prompt(
							"StabilityClimaticConditions:",
							Character,
							PromptName( "t1.StabilityClimaticConditions_1" )
						)
					)
				)
			)
		)
	)
	) << Run Background(invisible);


nw = New Window( "Name data table",
	<<Modal,
	<<Return Result,
	Text Box( "Insert data table file name" ),
	tableName = Text Edit Box( " ", <<set width( 400 ) )
);
If( nw["button"] == 1 & nw["tableName"] != "",
	dt << set name( nw["tableName"] ),
	dt << save as("tableName"||Format Date( Today(), "ddmonyyyy:h:m:s" )||".jmp");
	);

Many thanks,

Re: How to prompt user to name SQL query output data table and add date and time to filename?

ih — Fri, 11 Jun 2021 21:32:28 GMT

Try changing your last if statement to this, it both fixes your tableName reference and removes the slashes and colons that were probably causing your issue:

//ask where to save the file (could skip this or define it in code)
fldr = pick directory( "Select where to save the file", "$Temp" );

//Save the file
If( nw["button"] == 1 & nw["tableName"] != "",
	dt << set name( nw["tableName"] );
	dt << save as( fldr || nw["tableName"] || Format Date( Today(), "yyyymmdd" ) || "-" || Char( Hour( Today() ) ) || Char( Minute( Today() ) ) || Char( Second( Today() ) ) || ".jmp");
);

//Open directory where file was saved (not needed)
Open(fldr);

Re: How to prompt user to name SQL query output data table and add date and time to filename?

FabioSaragoni — Mon, 14 Jun 2021 08:31:19 GMT

Thanks for your reply!

I still have an issue at this level:

dt << set name( nw["tableName"] );

The error message I get is here below

Could you please help me solve it?

Thanks again,

Re: How to prompt user to name SQL query output data table and add date and time to filename?

jthi — Mon, 14 Jun 2021 08:56:10 GMT

Most likely execution of your script finishes before the New Query(.

Try running the query in foreground instead of background OR use modal before query to get values from user and use postquery script to change the name after query finishes.

Re: How to prompt user to name SQL query output data table and add date and time to filename?

FabioSaragoni — Mon, 14 Jun 2021 11:59:12 GMT

Executing the query in foreground solved the issue!

Thank you so much,
Fabio