Hey Jeff, Sorry, had to go and actually work today. It will be nice when this stuff pays the bills. It is a time consuming hobby.
As to the <cfdump> on the list page, it would seem to be too late in the stack to see what it is that leaves the search page. I think the values go through the database blocks and tons of other functions/templates before they get to the list page. I don't know how to do it but it would seem like the way to test the value as it leave the search page would be to assign those values to session variables using the onChange or onSubmit attributes of the display search button. That way the values would be set before they go through the ringer, so to speak. If it turns out that the values are blank when the form is submitted then it probably is the Qforms API. However, if the values are there when the form is submitted, then it must get "Nulled" somewhere in the stack. Do you know how to do the onChange thing? Mark -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jeff Fleitz Sent: Friday, August 19, 2005 8:35 AM To: [email protected] Subject: Re: [plum] date ranges and whereClauseExtension with AND Yeah, I looked into that. I am just getting ramped back up this morning, so I'll give it another couple hours and then I have to put it away for a while. Here is what I have found so far: 1. The DisplaySearchDateTimeControl custom tag has two hidden fields in it, one each for the upper and lower criteria (#Attributes.column#_CriteriaUpper and #Attributes.column#_CriteriaLower). Those are there to build the criteria that should be passed to the DatabaseBlocks.cfc for filtering. 2. It looks to me like those values are supposed to be set by the javascript generated at the botton of the SearchFormFieldValidation include, when the form is submitted (part of the qForms API). However, I have placed dummy values in there just trying to the the #Attributes.column#_CriteriaUpper/Lower variables to display them in the debug stream, and they don't show up. 3. I placed a cfdump and cfabort at the very top of the DisplayList tag to see what those _CriteriaUpper/Lower variables were at that point, and they are blank. Not only that, but my Type attribute value is set as 'Date' in the DisplaySearchDateTimeControl tag in my search form, but by the time it reaches the cfdump in the DisplayList, it has been reset to 'DateTime'. The only place I can figure where this might be happening in in the qForms API somewhere. Anyway, I will give it another look and let you know if I find anything. Jeff Mark Fuqua wrote: > Hey Jeff, > > Just for kicks I looked at the searchformfieldvalidation.cfm and thought > maybe if I eliminated suppressTime="yes" from the search date by range, > maybe it would work. For a second, I thought it was working 'cause it > returned multiple records. However, it turns out that it doesn't filter at > all. The where clause is messed up but in a different way: > > SELECT TimeCard.TimeCardId AS TimeCard_TimeCardId,TimeCard.ReportDate AS > TimeCard_ReportDate,PlumUser.UserName AS PlumUser_UserName,Jobs.JobName AS > Jobs_JobName,TimeCard.totalHours AS TimeCard_totalHours,TimeCard.Associate > AS TimeCard_Associate,TimeCard.Job AS TimeCard_Job > FROM (TimeCard LEFT OUTER JOIN PlumUser ON TimeCard.Associate = > PlumUser.UserID) LEFT OUTER JOIN Jobs ON TimeCard.Job = Jobs.JobId > WHERE reportdate > > > > ORDER BY TimeCard.Associate ASC, TimeCard.ReportDate ASC > > WHERE 0>1 returns what you would expect, nada and WHERE reportDate returns > everything. But, the fact that they return different WHERE clause's must > mean something? right? > > Mark > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Mark > Fuqua > Sent: Thursday, August 18, 2005 9:14 PM > To: [email protected] > Subject: RE: [plum] date ranges and whereClauseExtension with AND > > > Hey Jeff, > > I was looking at the databaseblocks.cfc, most of which is over my head. But > it looks like the upper and lower values are needed. The following is around > line 400: It checks to see if lowerValue * UpperValue is GT 0 I guess you > have already looked at this, but I thought I would pass it along to someone > who can do a better job "proofing/reading" the code. > > <cffunction name="AppendRangeCriteria" returntype="string" access="public" > output="No"> > <cfargument name="currentCriteria" type="string" required="yes"> > <cfargument name="fieldName" type="string" required="yes"> > <cfargument name="fieldType" type="string" required="yes"> > <cfargument name="valueLower" type="string" required="yes"> > <cfargument name="valueUpper" type="string" required="yes"> > > <cfscript> > var accumulatedCriteria = ""; > > if(Len(Trim(valueLower)) GT 0) { > accumulatedCriteria = fieldName; > > if(Application.Formatting.GetGenericType(fieldType) NEQ "date") { > valueLower = Replace(valueLower, "'", > "''", "ALL"); > } > > > if(Application.Formatting.GetGenericType(fieldType) EQ "string") { > accumulatedCriteria = > accumulatedCriteria & " >= '#valueLower#' "; > } > else > if(Application.Formatting.GetGenericType(fieldType) EQ "date") { > accumulatedCriteria = > accumulatedCriteria & " >= > #CreateODBCDateTime(valueLower)# "; > } > else > if(Application.Formatting.GetGenericType(fieldType) EQ "numeric") { > accumulatedCriteria = > accumulatedCriteria & " >= #Val(valueLower)# "; > } > } > > if(Len(Trim(valueLower)) * Len(Trim(valueUpper)) GT 0) { > accumulatedCriteria = accumulatedCriteria & " > AND "; > } > > if(Len(Trim(valueUpper)) GT 0) { > accumulatedCriteria = accumulatedCriteria & > fieldName; > > if(Application.Formatting.GetGenericType(fieldType) NEQ "date") { > valueLower = Replace(valueUpper, "'", > "''", "ALL"); > } > > > if(Application.Formatting.GetGenericType(fieldType) EQ "string") { > accumulatedCriteria = > accumulatedCriteria & " < '#valueUpper#' "; > } > else > if(Application.Formatting.GetGenericType(fieldType) EQ "date") { > accumulatedCriteria = > accumulatedCriteria & " < > #CreateODBCDateTime(valueUpper)# "; > } > else > if(Application.Formatting.GetGenericType(fieldType) EQ "numeric") { > accumulatedCriteria = > accumulatedCriteria & " < #Val(valueUpper)# "; > } > } > > if(Len(Trim(accumulatedCriteria)) GT 0) { > if(Len(Trim(currentCriteria)) EQ 0 OR > currentCriteria EQ "1 > 0") { > return accumulatedCriteria; > } > else { > return currentCriteria & " AND " & > accumulatedCriteria; > } > } > else if(Len(Trim(currentCriteria)) EQ 0) { > return "1 > 0"; > } > else { > return currentCriteria; > } > </cfscript> > </cffunction> > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Jeff > Fleitz > Sent: Thursday, August 18, 2005 4:45 PM > To: [email protected] > Subject: Re: [plum] date ranges and whereClauseExtension with AND > > > > > Mark Fuqua wrote: > >>Jeff, >> >>A couple of notes: >> >>First, I appreciate your efforts. As I think all the users of PLUM >>will...being able to search by a range of dates from within PLUM would be > > a > >>great asset. It is quick and easy. Plus it integrates with all the other >>functionality of this most excellent product. >> > > > I am going to need this as well, so we need to get it solved. > > >>Second, I think with David and Adam being as busy as they are, if you want >>their attention, you should put their names near the top of the message. >> > > > I am just checking to see if they are checking ;) I know how to get ahold of > them if I really want to make a nuisance of > myself. > > >>Third, I discovered why my whereClauseExtension was not working. If you >>look at the queries below, you will notice that they are cached (I am a >>little slow, the first thing I noticed was the 0 ms execution time, on my >>laptop nothing takes 0 ms). This explains why all my changes seemed to > > work > >>the first time but not after that. Is there a way to keep certain queries >>or pages from caching? >> > > > Add 'refresh=1' as a query string, a la > > ThisList.cfm?refresh=1 > > Jeff > > >>Thanks, >> >>Mark Fuqua >> >>ps notice that all the where clauses have 1>0 as a condition. This must > > be > >>some sort of default (i.e. all queries have a where clause and if nothing >>else is provided, 1>0 is provided to keep from throwing an error.) If it > > is > >>a default, maybe there is just a typo somewhere in the code?! Or I guess >>the where 1>0 is an error check...if 1>0 than the query was constructed as >>expected, if it is 0>1, then the query was constructed in such a way as to >>throw a unhandled exception so instead, it just kills the query??? >> >>queryResult (Datasource=mdfinish, Time=0ms, Records=4, Cached Query) in >>C:\CFusionMX7\wwwroot\mdprofinish\components\DatabaseBlocks.cfc @ >>16:12:46.046 >> >> SELECT NewAppointment.NewAppointmentID AS >>NewAppointment_NewAppointmentID,NewAppointment.AppointmentDate AS >>NewAppointment_AppointmentDate,NewAppointment.AppointmentTime AS >>NewAppointment_AppointmentTime,PlumUser.UserName AS >>PlumUser_UserName,NewAppointment.AppointmentWith AS >>NewAppointment_AppointmentWith >> FROM NewAppointment LEFT OUTER JOIN PlumUser ON >>NewAppointment.AppointmentWith = PlumUser.UserID >> WHERE 1 > 0 AND appointmentFor = 2 >> >> >> >> ORDER BY NewAppointment.appointmentDate DESC >> >> >>queryResult (Datasource=mdfinish, Time=0ms, Records=5, Cached Query) in >>C:\CFusionMX7\wwwroot\mdprofinish\components\DatabaseBlocks.cfc @ >>16:12:46.046 >> >> SELECT tasks.TaskId AS tasks_TaskId,tasks.TaskDueDate AS >>tasks_TaskDueDate,TaskStatus.TaskStatus AS >>TaskStatus_TaskStatus,tasks.TaskStatus AS tasks_TaskStatus >> FROM tasks LEFT OUTER JOIN TaskStatus ON tasks.TaskStatus = >>TaskStatus.TaskStatus >> WHERE 1 > 0 AND TaskAssignedTo = 2 >> >> >> >> ORDER BY tasks.TaskDueDate DESC >> >> >>queryResult (Datasource=mdfinish, Time=0ms, Records=2, Cached Query) in >>C:\CFusionMX7\wwwroot\mdprofinish\components\DatabaseBlocks.cfc @ >>16:12:47.047 >> >> SELECT Message.NewMessageId AS > > Message_NewMessageId,Message.NewMessageDate > >>AS Message_NewMessageDate,PlumUser.UserName AS >>PlumUser_UserName,Message.messageSubject AS >>Message_messageSubject,Message.NewMessageFrom AS Message_NewMessageFrom >> FROM Message LEFT OUTER JOIN PlumUser ON Message.NewMessageFrom >> = >>PlumUser.UserID >> WHERE 1 > 0 AND Message.NewmessageTo = 2 AND >> Message.newMessageStatus = >>'new' >> >> >> >> ORDER BY Message.NewMessageDate desc >> >> >> >> >>-----Original Message----- >>From: [EMAIL PROTECTED] >>[mailto:[EMAIL PROTECTED] Behalf Of Jeff >>Fleitz >>Sent: Thursday, August 18, 2005 3:11 PM >>To: [email protected] >>Subject: Re: [plum] date ranges and whereClauseExtension with AND >> >> >>Well, I have spent most of the day trying to track this one down, and I > > have > >>not been able to resolve it yet, although I >>believe the bug resides in the SearchFormFieldValidate include template; >>specifically the block of code at about line >>216. The code is trying to set the value of the _CriteriaUpper and >>_CriteriaLower values, which refer to hidden form >>fields passed in the DisplaySearchDateTimeControl custom tag, but it is > > not > >>working. >> >>Another interesting tidbit is that I am setting the type attribute in the >>DisplaySearchDateTimeControl control in the >>search from to 'date' instead of 'datetime', the list page shows the type > > as > >>datetime, so it is getting converted in >>there somewhere. >> >>Adam, David, you guys got any suggestions? >> >>Jeff >> >> >>Mark Fuqua wrote: >> >> >>>Hey Jeff, >>> >>>I did not try <cfdump> but I do have debuging enabled which shows the >>>following form and session variables from the appontmentList page which is >>>what the search page posts to: (ignor the area, job and time card session >>>stuff, it is left over from other recently visited pages within the same >>>application...It would seem, strictly from a common sence point of view, >>>that form.appointmentdate_criterialower and >>>form.appointmentdate_criteriaUpper should have a value). Will <cfdump> >> >>show >> >> >>>me more than this. >>> >>>Interestingly enough, it is obvious why the query (copied from debug stuff >>>and pasted at the end of this email) returns 0 records, it uses: WHERE 0>1 >>>as the Where clause. Guess the chances of that returning records is >> >>fairly >> >> >>>slim. >>> >>>Form Fields: >>>APPOINTMENTDATE_CRITERIALOWER= >>>APPOINTMENTDATE_CRITERIALOWERDAY=01 >>>APPOINTMENTDATE_CRITERIALOWERMONTH=8 >>>APPOINTMENTDATE_CRITERIALOWERYEAR=2005 >>>APPOINTMENTDATE_CRITERIAUPPER= >>>APPOINTMENTDATE_CRITERIAUPPERDAY=17 >>>APPOINTMENTDATE_CRITERIAUPPERMONTH=8 >>>APPOINTMENTDATE_CRITERIAUPPERYEAR=2005 >>>APPOINTMENTDATE_OPERATOR=BETWEEN >>>APPOINTMENTDATE_TYPE=datetime >>>DISPLAYALL=0 >>> >> >> > FIELDNAMES=FROMSEARCH,RAND,DISPLAYALL,APPOINTMENTDATE_TYPE,APPOINTMENTDATE_O > >> > PERATOR,APPOINTMENTDATE_CRITERIALOWER,APPOINTMENTDATE_CRITERIALOWERMONTH,APP > >> > OINTMENTDATE_CRITERIALOWERDAY,APPOINTMENTDATE_CRITERIALOWERYEAR,APPOINTMENTD > >> > ATE_CRITERIAUPPER,APPOINTMENTDATE_CRITERIAUPPERMONTH,APPOINTMENTDATE_CRITERI > >>>AUPPERDAY,APPOINTMENTDATE_CRITERIAUPPERYEAR >>>FROMSEARCH=1 >>>RAND=C75B34FC-B379-0F42-61240690D704C4C0 >>> >>> >>>Session Variables: >>>areaid=19 >>>areascriteria=areas.jobid = 20 >>>areaslist=26~25~19~18~17 >>>areaslist.cfm|search|areas.jobid=20;EQUAL;integer >>>areassortorder=Areas.AreaGroup ASC, Areas.AreaId DESC >>>assignedestimateitemscriteria=AssignedEstimateItems.AreaId = 19 >>>assignedestimateitemslist=58~59~61 >>>assignedestimateitemssortorder= >>>cachetimespan=0.0833333333333 >>>cfid=2000 >>>cftoken=84235458 >>>fixedestimateitemscriteria=1 > 0 >>>fixedestimateitemslist=13~14~15~21~19~18~17~16~9~20 >>>fixedestimateitemssortorder=FinishTypes.JobType ASC, >> >>FinishTypes.FinishType >> >> >>>ASC, FixedEstimateItems.ItemType ASC >>>jobid=20 >>>jobscriteria=1 > 0 >>>jobslist=20~17~19~18~21 >>>jobssortorder=Jobs.JobStatus ASC, Jobs.Customer ASC >>>loginstatus=Ready >>>messagecriteria=1 > 0 AND NewmessageTo = 2 >>>messagelist=16~15~14~12 >>>messagesortorder=Message.NewMessageDate desc >>>nameofloggedinuser=Mark Fuqua >>>newappointmentcriteria=0 > 1 AND appointmentfor = 2 >>>newappointmentlist= >>>newappointmentlist.cfm|search|appointmentdate|range=%20 ;%20;datetime >>>newappointmentsortorder=NewAppointment.AppointmentDate ASC >>>plumuserroles=CONTRIBUTOR,ADMIN >>>previousmodule= >>>sessionid=MDPROFINISH_2000_84235458 >>>targetformvars= >>>targetlocation= >>>targeturlvars= >>>taskscriteria=1 > 0 AND TaskAssignedTo = 2 >>>taskslist=7~6~5~4 >>>taskssortorder=tasks.TaskDueDate DESC >>>timecardcriteria=timecard.job = 19 >>>timecardlist=11~14 >>>timecardlist.cfm|search|reportdate|range=%20 ;%20;datetime >>>timecardlist.cfm|search|timecard.associate=%20;EQUAL;integer >>>timecardlist.cfm|search|timecard.job=19;EQUAL;integer >>>timecardlist.cfm|search|timecard.timecardid=%20;EQUAL;integer >>>timecardsortorder=TimeCard.Associate ASC, TimeCard.ReportDate ASC >>>urltoken=CFID=2000&CFTOKEN=84235458 >>>userid=2 >>> >>>queryResult (Datasource=mdfinish, Time=90ms, Records=0) in >>>C:\CFusionMX7\wwwroot\mdprofinish\components\DatabaseBlocks.cfc @ >>>22:28:03.003 >>> >>> SELECT NewAppointment.NewAppointmentID AS >>>NewAppointment_NewAppointmentID,NewAppointment.AppointmentDate AS >>>NewAppointment_AppointmentDate,Associates.AssociateName AS >>>Associates_AssociateName,PlumUser.UserName AS >>>PlumUser_UserName,TimeDuration.NumberOfHours AS >>>TimeDuration_NumberOfHours,NewAppointment.AppointmentFor AS >>>NewAppointment_AppointmentFor,NewAppointment.AppointmentWith AS >>>NewAppointment_AppointmentWith,NewAppointment.AppointmentDuration AS >>>NewAppointment_AppointmentDuration >>> FROM ((NewAppointment LEFT OUTER JOIN Associates ON >>>NewAppointment.AppointmentFor = Associates.AssociateContactRefNo) LEFT >> >>OUTER >> >> >>>JOIN PlumUser ON NewAppointment.AppointmentWith = PlumUser.UserID) LEFT >>>OUTER JOIN TimeDuration ON NewAppointment.AppointmentDuration = >>>TimeDuration.NumberOfHours >>> WHERE 0 > 1 >>> >>> >>> >>> ORDER BY NewAppointment.AppointmentDate ASC >>> >>> >>>-----Original Message----- >>>From: [EMAIL PROTECTED] >>>[mailto:[EMAIL PROTECTED] Behalf Of Jeff >>>Fleitz >>>Sent: Wednesday, August 17, 2005 9:43 PM >>>To: [email protected] >>>Subject: Re: [plum] date ranges and whereClauseExtension with AND >>> >>> >>>Hi Mark, >>> >>>Did you try using the cfdump suggestion I made in your post of 8/7 on this >>>topic? >>> >>>I haven't had a chance to try a search form with a date range yet, but I >>>will build one tomorrow and see if I get the >>>same behavior. >>> >>>Jeff >>> >>>Mark Fuqua wrote: >>> >>> >>> >>>>Hey there, >>>> >>>>I am having some troubles with date ranges on PLUM search forms and also >>>>with trying to get multiple filters on a whereClauseExtension of the >>>><cf_displayList>. >>>> >>>>When I use search by date range on a search form, the list posts with no >>>>records and the message that "you didn't enter and search criteria". >>>> >>>>When I use the whereClauseExtension of a <cf_displayList>, and I try and >>> >>>use >>> >>> >>> >>>>AND, it doesn't seem to work. For Example: >>>> >>>>whereClauseExtension="messageSentTo = #session.UserId# AND messageStatus > > = > >>>>'new'" >>>> >>>>Chances are good that these problems are of my own making, however, I am >>>>just wondering whether anyone else has had such problems. Searching for >>>>solutions is almost becoming fun. I am just trying to make sure > > solutions > >>>>exist. >>>> >>>>Thanks all, >>>> >>>>Mark Fuqua >>>> >>>> >>>> >>>>********************************************************************** >>>>You can subscribe to and unsubscribe from lists, and you can change >>>>your subscriptions between normal and digest modes here: >>>> >>>>http://www.productivityenhancement.com/support/DiscussionListsForm.cfm >>>>********************************************************************** >>>> >>>> >>> >>> >>>********************************************************************** >>>You can subscribe to and unsubscribe from lists, and you can change >>>your subscriptions between normal and digest modes here: >>> >>>http://www.productivityenhancement.com/support/DiscussionListsForm.cfm >>>********************************************************************** >>> >>> >>> >>>********************************************************************** >>>You can subscribe to and unsubscribe from lists, and you can change >>>your subscriptions between normal and digest modes here: >>> >>>http://www.productivityenhancement.com/support/DiscussionListsForm.cfm >>>********************************************************************** >>> >>> >> >> >> >>********************************************************************** >>You can subscribe to and unsubscribe from lists, and you can change >>your subscriptions between normal and digest modes here: >> >>http://www.productivityenhancement.com/support/DiscussionListsForm.cfm >>********************************************************************** >> >> >> >>********************************************************************** >>You can subscribe to and unsubscribe from lists, and you can change >>your subscriptions between normal and digest modes here: >> >>http://www.productivityenhancement.com/support/DiscussionListsForm.cfm >>********************************************************************** >> >> > > > ********************************************************************** > You can subscribe to and unsubscribe from lists, and you can change > your subscriptions between normal and digest modes here: > > http://www.productivityenhancement.com/support/DiscussionListsForm.cfm > ********************************************************************** > > > > ********************************************************************** > You can subscribe to and unsubscribe from lists, and you can change > your subscriptions between normal and digest modes here: > > http://www.productivityenhancement.com/support/DiscussionListsForm.cfm > ********************************************************************** > > > > ********************************************************************** > You can subscribe to and unsubscribe from lists, and you can change > your subscriptions between normal and digest modes here: > > http://www.productivityenhancement.com/support/DiscussionListsForm.cfm > ********************************************************************** > > ********************************************************************** You can subscribe to and unsubscribe from lists, and you can change your subscriptions between normal and digest modes here: http://www.productivityenhancement.com/support/DiscussionListsForm.cfm ********************************************************************** ********************************************************************** You can subscribe to and unsubscribe from lists, and you can change your subscriptions between normal and digest modes here: http://www.productivityenhancement.com/support/DiscussionListsForm.cfm **********************************************************************
