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

OtisZeca

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

gzmorgan0

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