Add a new window for Start & End Calendar date selection box/ date entry box

[#1]

Hi JMP community, I usually use jmp preselected and plot graphs need a help to add selection date amend for my script below.

As per below, once I call out database, I have to amend the dates for start & end in order to query needed data on input dates.

dt = Open Database(
	"DSN=xxx ;Description=yyy;UID=zzz;PWD=zzz;APP=JMP;DATABASE=MATS;",
	"SELECT pen_parametric.pn_id, pen_parametric.equiplk_ky, parametric_dlk.paramlk_ky,
FROM MATS.parametric_dlk parametric_dlk, MATS.pen_parametric pen_parametric
WHERE parametric_dlk.paramlk_ky = parametric_ref_llk.paramlk_ky AND parametric_dlk.paramlk_ky = pen_parametric.paramlk_ky AND ((pen_parametric.part_dm Between {ts '2019-07-01 00:00:00'} And {ts '2019-07-02 00:00:00'}))"
);

dt << Set Name( "Data01" );

So, for (Between {ts '2019-07-01 00:00:00'} And {ts '2019-07-02 00:00:00'})

Is it possible for adding a script box that prompt up new window calendar box for selection of the calendar as per formatted date above so can replace the date to the ones i want/select and then run the script?

Thank you in advance!

BR, Otis

[#2 · Accepted solution]

@OtisZeca,

There are multiple methods to do this. Here is one example 

Names Default To Here( 1 );

tmpstr = "SELECT pen_parametric.pn_id, pen_parametric.equiplk_ky, parametric_dlk.paramlk_ky,
FROM MATS.parametric_dlk parametric_dlk, MATS.pen_parametric pen_parametric
WHERE parametric_dlk.paramlk_ky = parametric_ref_llk.paramlk_ky 
  AND parametric_dlk.paramlk_ky = pen_parametric.paramlk_ky 
   AND ((pen_parametric.part_dm Between {ts '^sdt^'} And {ts '^edt^'}))"
;

New Window( "Query Date", <<Modal,

sd=Today();
ed=Today();
 HListBox(
    PanelBox( "From Time",
      scal = Calendar Box(, SetFunction(Function({this}, sd=scal<< Get Date) ))
    ),
    spacerbox(Size(20,20)),
    PanelBox( "To Time",
    ecal = Calendar Box(, SetFunction(Function({this}, ed=ecal<< Get Date) ))
    )
));

sdt = Munger(Format Date(sd, "yyyy-mm-ddThh:mm:ss"), 1, "T", " "); 
edt = Munger(Format Date(ed, "yyyy-mm-ddThh:mm:ss"), 1, "T", " "); 


show(sdt, edt);
 
qstring = EvalInsert(tmpstr);
show(qstring);  //see log

//then run the line below
//dt = Open Database("DSN=xxx ;Description=yyy;UID=zzz;PWD=zzz;APP=JMP;DATABASE=MATS;", qstring);

[#3]

ahh icic.. thanks for your help! 

need more practice understanding what you've written. thank you!

[#4 · Accepted solution]

Here's a modified version of Georgia's script that uses Number Edit Box instead of directly using the Calendar Box.  Number Edit Box has a popup version of the calendar build-in if the numeric format is set to a date.  I set the format to only include the day, but you can include time-of-day as well if you need that level of granularity.

Names Default To Here( 1 );

tmpstr =
"SELECT pen_parametric.pn_id, pen_parametric.equiplk_ky, parametric_dlk.paramlk_ky,
FROM MATS.parametric_dlk parametric_dlk, MATS.pen_parametric pen_parametric
WHERE parametric_dlk.paramlk_ky = parametric_ref_llk.paramlk_ky 
  AND parametric_dlk.paramlk_ky = pen_parametric.paramlk_ky 
   AND ((pen_parametric.part_dm Between {ts '^sdt^'} And {ts '^edt^'}))";

New Window( "Query Date",
	<<Modal, 
	sd = Today();
	ed = Today();
	H List Box(
		Text Box( "From:" ),
		scal = Number Edit Box( sd, <<Set Format( Format( "yyyy-mm-dd" ) ), <<SetFunction( Function( {this}, sd = scal << Get ) ), <<Set Show Spin Box(1) ),
		Spacer Box( Size( 20, 20 ) ),
		Text Box( "To:" ),
		ecal = Number Edit Box( ed, <<Set Format( Format( "yyyy-mm-dd" ) ), <<SetFunction( Function( {this}, ed = ecal << Get ) ), <<Set Show Spin Box(1) ),
	);
);

sdt = Munger( Format Date( sd, "yyyy-mm-ddThh:mm:ss" ), 1, "T", " " );
edt = Munger( Format Date( ed, "yyyy-mm-ddThh:mm:ss" ), 1, "T", " " ); 


Show( sdt, edt );
 
qstring = Eval Insert( tmpstr );
Show( qstring );  //see log

[#5]

Dan, @danschikore , thank you.

I too like the date formatted NumberEditBox(). I was going to post an alternate script that produces the dialog shown below. However, I thought @OtisZeca might be new to JSL.  Very useful options of the NumberEditbox() includes, setting a minimum, a maximum and specifying integer only.  The attached script uses a function to create a bounded integer number edit box that returns the value to a user specified variable, which is vauable for non-Modal dialogs. I am posting it for the blog, since I know you don't need an example.