Search Option for Date Fields > Forums > Bi4ce Open Source DotNetNuke Skins: DNN Modules: DNN Consulting

Forums

Forums > Bi4ce.Modules.ListX > Blue Sky

Subject: Search Option for Date Fields

You are not authorized to post a reply.

Author

Messages

mlarsen

Posts:49

02/27/2006 11:51 PM	Alert
I am using listx for displaying a log file. This file is growing at a fairly quick rate of about 5000 records per day. This log must be maintained for 1 year before records older than a year are purged. I realy would love an option to search date fields with a start and stop date. If there is currently a way to do this please let me know.

kevinmschreiner
Please Wait...

Posts:749

02/28/2006 1:29 AM	Alert
Actually, you can already do this pretty easily within ListX. There are a few simple steps to take, and, I will attach the configuration I used for this scenario. First - Add a new header/footer to your template. This level will provide 5 objects. Two text boxes - one for start date and the other for end date. Two Links - both which pop open the calendar control contained within DNN. And the last, a Search button which will assign our criteria. The configuration is something like this: <script src="/js/PopupCalendar.js"></script> <table border=0 cellpadding=0 cellspacing=0> <tr> <td><a id="calStartDate" class="CommandButton" href="javascript:popupCal('Cal','frmStartDate','M/d/yyyy', 'January,February,March,April,May,June,July,August,September,October,November,December', 'Sun,Mon,Tue,Wed,Thu,Fri,Sat','Today','Close','Calendar',0);">Start Date:</a></td> <td><input id="frmStartDate" name="frmStartDate" value="[FORMAT,[StartDate,Session],{ISEMPTY:[DATE]}]"></td> <td><a id="calEndDate" class="CommandButton" href="javascript:popupCal('Cal','frmEndDate','M/d/yyyy', 'January,February,March,April,May,June,July,August,September,October,November,December', 'Sun,Mon,Tue,Wed,Thu,Fri,Sat','Today','Close','Calendar',0);">End Date:</a></td> <td><input id="frmEndDate" name="frmEndDate" value="[FORMAT,[EndDate,Session],{ISEMPTY:[DATE]}]"></td> <td><input type=submit value="Search" id=frmSearch name=frmSearch></td> </tr> </table> As you can see, I have included the PopupCalender.js library, which is launched by pressing the Start Date and End Date links. These are wired to the frmStartDate and frmEndDate form variables. It is key to point out that I provide both the ID and NAME attributes within listX form fields. This is because - ID works best for working with Javascript, and Name works best for working with the post-back data. Additionally, I have utilized the {ISEMPTY} formatter to set a default value, in this case, to the current date. Now that I have this form, I can add some actions which will retain the selected date information. Additionally, you will want to copy this section and place it in the "No Results" detail template, otherwise it will never be visible unless some data comes back from your query. Now - We need to add a few lines of Action script - which check to see if the incoming form request contains the frmSearch button value of Search. This simply means the user has pressed the Search button. If this condition passes, simply assign two session variables to the incoming search variables. If '[frmSearch,Form]' = 'Search' * Assignment: Assign session variable 'StartDate' to '[frmStartDate,Form]'. * Assignment: Assign session variable 'EndDate' to '[frmEndDate,Form]'. Now, it comes down to simply setting up the best query for the job. I prefer to create Query Variables which handle the standard replacement of the database owner and object qualifier, as well as SQL Injection secured varaibles for consuming all Form and Querystring variables. So - the query ends up looking like this - Select * from {databaseowner}{objectqualifier}EventLog where LogCreateDate between '[StartDate]' and '[EndDate]' Thats it in a nutshell, I think you should be able to work pretty effectively from this example. Let me know if there is anything I can do to clarify the concepts. Happy Nuking!
Attachment: Sample - Log Viewer Date Range.xml
Kevin M Schreiner Business Intelligence Force, Inc. (bi4ce)

tltyson

Posts:4

04/20/2006 12:11 AM	Alert
I'm using this for a time tracking system. By default I want it to show all entries that are within the last 15 days. Is there a way to get the frmStartDate to default to 15 days before the current date?

fresco

Posts:6

09/06/2006 10:41 AM	Alert
I tried to set this up as described. I was able to get the date fields to display at the top and the date can be chosen from the Calendar. I then added the Actions and could see that the Sessions were getting filled with the dates, but when I get to the Query, I have not been able to get it to work. As soon as I try to add the following line to my query, it has problems WHERE... AND (dbo.SiteLog.DateTime between '[StartDate]' and '[EndDate]' ) "Conversion failed when converting datetime from character string" I have tried several date conversions without success. I am not even sure if the code is inserting the dates correctly. If I hard code dates like below it works. WHERE.... AND (dbo.SiteLog.DateTime between '08/25/2006' and '09/01/2006') My Actions are as follows... If [frmSearch,Form] = Search Assignment: Assign <Session> variable 'StartDate' to '[frmStartDate,Form]'. Assignment: Assign <Session> variable 'EndDate' to '[frmEndDate,Form]'. Else Assignment: Assign <Session> variable 'StartDate' to '08012006'. I have all the html code in a header above "Table Structure and Column Headings" and "List Item Detail" Any ideas?

bgates

Posts:188

09/13/2006 4:49 PM	Alert
You need to be explicit about where the variable is coming from. To say [StartDate], ListX will look for a column named StartDate. That doesn't exist, so it will just skip that. Since you added the action to assign to the StartDate Session variable, your query needs to reflect that: WHERE... AND (dbo.SiteLog.DateTime between '[StartDate,Session]' and '[EndDate,Session]' )

Bob Gates Business Intelligence Force, Inc. (bi4ce)

You are not authorized to post a reply.

Forums > Bi4ce.Modules.ListX > Blue Sky > Search Option for Date Fields

ActiveForums 3.6