Filter grid data by dates on startup

Applications Grid / Reports
#1

Hi All,

I am new to Scriptcase, but so I am a frequent visitor to the forum finding answers to various things I come across that stump me.

I had a spot of bother trying to find out how to filter a grid on startup, using dates. So finally, with many hours of staring at the trees for the forest I got it working and I thought I would jot down some pointers for myself to refer back to during a seniors moment. If other newbies get some benefit then it is a bonus.

  1. I had a situation where I wanted the user to select a predefined date range (quarters as it happens) in order to filter a grid so it was ready for further reporting.

  2. I personally chose to create a table to house the description of the quarter, the start date and the finish date. An INT provided the id and Primary Key.

  3. I added a single field to the control app for an INT with the data type set to select. I then created an automatic SQL lookup for the description to be displayed in a drop down list.


SELECT bd_id, sc_concat(bd_desc,"   - ", bd_datefrom," to ", bd_dateto) 
FROM basdates 
ORDER BY bd_id
  1. In the onload event I added a lookup so that I could retrieve the quarter relating to the current date and set the value for the INT field on the window. This allows the list to start at a predefined spot in the list.

sc_lookup(listdata,"SELECT bd_id, bd_datefrom, bd_dateto FROM basdates WHERE bd_datefrom <= curdate() AND bd_dateto >= curdate()");
if (!empty({listdata[0][0]})){
	{basperiod} = {listdata[0][0]};
	[bas_startdate]={listdata[0][1]};
	[bas_finishdate]={listdata[0][2]};
}
  1. I also placed similar code in the onvalidate event to record the global variables for the start and finish dates of the predefined period.

sc_lookup(listdata,"SELECT bd_id, bd_datefrom, bd_dateto FROM basdates WHERE bd_id = {basperiod}");
if (!empty({listdata[0][0]})){
	{basperiod} = {listdata[0][0]};
	[bas_startdate]={listdata[0][1]};
	[bas_finishdate]={listdata[0][2]};
}

  1. I went to the Application section and selected the global variable item. I made sure that the two variables for the date range were set to OUT.

  2. I then created a link to the grid application and carefully set the global variables to match the start and finish dates that would be found in the grid application.

  3. In the grid application you must add the WHERE clause to record the date filter in the SQL section.


SELECT 
    ta_id,
    ta_tran,
    ta_date,
    ta_ref,
    ta_glcode,
    ta_amount,
    ta_gst,
    ta_net,
    ta_drcr,
    ta_bastype,
    ta_note,
    ta_type
FROM 
    allocations
WHERE
    ta_date BETWEEN ' [bas_startdate]' AND '[bas_finishdate]'
ORDER BY
    ta_bastype, ta_date

Take special note of the single quotes either side of the global variables, easy to miss (I know from experience) but vital for the proper syntax.

  1. The SELECT statement expects dates in the format ‘yyyy-mm-dd’ which is fine, but my users want to see the date range displayed in the header as ‘dd/mm/yyyy’

I place the following code in the onheader event to reformat the dates and also to replace the default header title.


{bas_start_disp} = sc_date_conv([bas_startdate],"aaaa-mm-dd","dd/mm/aaaa");
{bas_finish_disp} = sc_date_conv([bas_finishdate],"aaaa-mm-dd","dd/mm/aaaa");
{title_string} = "BAS records from ".{bas_start_disp}."  to  ".{bas_finish_disp};
{lang_othr_frmu_titl} = {title_string};

You need to add a couple of date fields to receive the formated dates as you do not wish to alter the original formats that SQL uses.

  1. I hope this may be of some use to others.

Tony

#2

Thanks a lot for share Tony. Great explanation. Felicidades.