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
**********************************************************************
