Query within date range
Date range is one of the very common requirements across project. It eases out lot of tasks for end user, one can easily generate report or export data items within given date range. Recently we were asked to implement similar functionality by inputting From and To date from the end user and resulting in all Service Requests between the given date range. There could be multiple solutions to go about it but one potential one is detailed below.
1 - Create Two calculated fields in SR BC.FromDate and ToDate of type "DTYPE_DATE". Set the calculated value to "Today()".
2 - Expose these two fields in the Query mode of the Applet. In the Edit Mode hide these controls.
3 - Below piece of code is required for setting up the Search expression and clearing the specs on To Date and From Date.
var sFromDate = this.GetSearchSpec("FromDate");
var sToDate = this.GetSearchSpec("ToDate");
if(sFromDate != "" && sToDate != "")
{
var sFrSubDate=sFromDate.substring(3,5)+"/"+sFromDate.substring(0,2)+"/"+sFromDate.substring(6,10);
var sToSubDate = sToDate.substring(3,5)+ "/" +sToDate.substring(0,2)+"/"+sToDate.substring(6,10);
var sSearchSpec = "[Created] >= '"+ sFrSubDate+ "' AND [Created] <= '"+ sToSubDate +"'";
this.ClearToQuery();
this.SetSearchExpr(sSearchSpec);
}
This code is generic enough without any error handling. Please add conditions as per your requirement. The basic catch here is we need to clear the FromDate and ToDate specs and build a new expression involving range and any other existing search criteria. Any dates used with SetSearchSpec must use the format
MM/DD/YYYY, regardless of the regional control panel settings of the server or client computer so the date variables should be modified to give proper result.
Happy Crunching!!
תגובות
הוסף רשומת תגובה