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