cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
OtisZeca
Level I

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

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 SOLUTIONS

Accepted Solutions
gzmorgan0
Super User (Alumni)

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

@OtisZeca,

 

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

 

image.png

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);

 

View solution in original post

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

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.

 

NumEditCalendar.png

 

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

View solution in original post

4 REPLIES 4
gzmorgan0
Super User (Alumni)

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

@OtisZeca,

 

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

 

image.png

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);

 

OtisZeca
Level I

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

ahh icic.. thanks for your help! 

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

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

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.

 

NumEditCalendar.png

 

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
gzmorgan0
Super User (Alumni)

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

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.

 

image.png