I'm going to try and figure out what I did wrong later tonight. Have to go
to dinner. It is not working for me. One difference might be that the
existing code for displayList seems to be different for my copy of PLUM.
The only line I could find similar to what you had was around 495 (I am
guessing line numbering is not exact) and it had different text:
else {
// determine whether
this is a range criteria
if(ListLen(currentKey, "|") EQ 4
AND ListGetAt(currentKey, 2, "|") EQ
"range") {
The query I am gettihg on the debugging info is:
queryResult (Datasource=mdfinish, Time=10ms, Records=14) in
C:\CFusionMX7\wwwroot\mdprofinish\components\DatabaseBlocks.cfc @
17:33:19.019
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
When I get back I will look to see if I can spot any irregularities with the
datatype (this is what was being returned when I set suppressTime="yes" The
difference in the code above is the "2". What you sent me as the "before"
text said 3.
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Jeff
Fleitz
Sent: Friday, August 19, 2005 4:39 PM
To: [email protected]
Subject: Re: [plum] date ranges and whereClauseExtension with AND
Here it is as a text file. Just rename it.
Jeff
Mark Fuqua wrote:
Jeff,
My anti virus program grabbed your last email. When I go to open it I get
an error that says there is a script error on the page. Is there anything
in the body of the email? I have opened the attachment...I'll need to
read
it a few times to understand it well enough to test it.
Scratch that, I think the "attachment" I got was the body of the email.
It
references another attachment which is not there. Here is what the
ATT00007.text said: (if this is the body of the email, could you re-post
with the attachment?)
Thanks,
Mark
I think I found the bugs. Please test this out and let me know if it
works
for you.
This was a combination of items in a couple files:
1. includes/SearchFormValidation.cfm - This one was generating a
Javascript
error if you used the suppressTime error,
which was causing the #Attributes.column#_CriteriaUpper and
#Attributes.column#_CriteriaLower variables to not be set.
The reason was this line (218 or thereabouts):
if(objForm#Attributes.table#["#ThisTag.dateTimeControls[i].column#_CriteriaL
owerTime"].getValue().length > 0) {
If you have time suppressed there are no properties for this value and a
js
error occurs.
I am attaching my new version of this file with corrections I made. I
removed the time value checks and changed the
code a little. You can use BeyondCompare or some other file comparison
tool
to see the change. This is the only change
I have made to this file.
2. The DisplayList tag has an function error in it that was preventing the
DatabaseBlocks.AppendRangeCriteria from being
called.
Around line 530 there is this block of code:
else {
// determine whether this is a range criteria
if(ListLen(currentKey, "|") EQ 4 AND ListGetAt(currentKey, 3, "|") EQ
"range") {
Change to:
else {
// determine whether this is a range criteria
if(ListLen(currentKey, "|") EQ 4 AND ListGetAt(currentKey, 4, "|") EQ
"range") {
Let me know, it is working for me here after repeated testing.
Jeff
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Mark
Fuqua
Sent: Friday, August 19, 2005 4:16 PM
To: [email protected]
Subject: RE: [plum] date ranges and whereClauseExtension with AND
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
**********************************************************************
**********************************************************************
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
**********************************************************************
