Good Afternoon Jeff,
I shut everything down and started over, still getting the same result. The
values are being passed they are just not being appended to the where
clause.
So, I am going to do the smart thing and put this aside for a while, as
there are plenty of other items I can be working on which will help further
the progress and I am hoping a break will help me see the issue clearer when
I return to it. But just incase it is something in the two snippets of code
I have cut and pasted below, maybe you will see it easily. The first
section is the snippet I changed on DisplayList along with the code directly
below what I changed.
The second section is the appendRangeCriteriaFunction of the
DatabaseBlock.cfc. Let me know if anything jumps out at you. Thanks for
all your time on this issue.
HERE ARE THE TWO LINES I CHANGED IN DISPLYLIST AND THE LINES THAT DIRECTLY
FOLLOW WHAT I CHANGED:
else {
// determine whether this is a range criteria
if(ListLen(currentKey, "|") EQ 4 AND ListGetAt(currentKey, 4, "|") EQ
"range") {
localCriteria =
Application.DatabaseBlocks.AppendRangeCriteria(
localCriteria,
ListGetAt(currentKey, 3, "|"), // name
ListGetAt(currentKeyValue, 3, ";"), // type
URLDecode(ListGetAt(currentKeyValue, 1, ";")), // lower value
URLDecode(ListGetAt(currentKeyValue, 2, ";")) // upper value
);
// populate
form fields
Form["#ListGetAt(currentKey, 3, '|')#_Type"] =
ListGetAt(currentKeyValue, 3, ";");
Form["#ListGetAt(currentKey, 3, '|')#_CriteriaLower"] =
URLDecode(ListGetAt(currentKeyValue, 1, ";"));
Form["#ListGetAt(currentKey, 3, '|')#_CriteriaUpper"] =
URLDecode(ListGetAt(currentKeyValue, 2, ";"));
}
AND HERE IS THE APPENDRANGECRITERIA FUNCTION OF THE DATABASEBLOCKS.CFC:
<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: Saturday, August 20, 2005 5:33 PM
To: [email protected]
Subject: Re: [plum] date ranges and whereClauseExtension with AND
Hey Mark,
That session variable is write, so you have the write line in
DisplayList.cfm
Changing
// determine whether this is a range criteria
if(ListLen(currentKey, "|") EQ 4 AND ListGetAt(currentKey, 2, "|") EQ
"range") {
To
if(ListLen(currentKey, "|") EQ 4 AND ListGetAt(currentKey, 4, "|") EQ
"range") {
Setup will not overwrite your files. It just resets the Scoped variables for
the application (unless you are setting up
verity collections)
I think this is a browser caching issue. Clear your browser cache, and
restart IIS.
I modified 6 existing projects with that code, and they all work fine.
Jeff
Mark Fuqua wrote:
Hey Jeff, had to work again today.
Here is what I get for that session variable:
timecardlist.cfm|search|reportdate|range=8%2F10%2F2205%2012%3A00AM%20
;8%2F17%2F2005%2012%3A00AM%20;datetime
That is what you would expect, right? This is with the time included of
course.
I reran setup too. Before I did I made a backup copy of my PLUM project,
is
that necessary? Can running setup screw up anything?
I am still getting all results with no filtering. Can you tell me what
line
you changed on DisplayList.cfm and what the new copy says. I want to make
sure it is what is expected. Did you fix the line that calls the
AppendRangeCriteria method of the DatabaseBlocks component? Maybe that is
what is not working? (by the way I haven't been struck by a bolt of
lightening which has suddenly made me smart, I just copied and pasted the
phrase, "AppendRangeCriteria method of the DatabaseBlocks component" from
your last email).
Thanks,
Mark
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Jeff
Fleitz
Sent: Saturday, August 20, 2005 10:42 AM
To: [email protected]
Subject: Re: [plum] date ranges and whereClauseExtension with AND
Are you using session scope?
If you do, you should see a session variable in debug that is a list
delimited with the pipe symbol, as mine below. The
first item is the list template which is probably reportdatelist.cfm or
something like that for you. You can see (watch
the wrapping) that the fourth value in my list is range, and corresponds
to
that change you made in the DisplayList tag
from postition 2 to 4. If you don't have 3 pipe delimiters in this
variable and 'range' is not in the fourth position,
after the 3rd pipe symbol, then the DatabaseBlocks.AppendRangeCriteria
method will not be called. You can see where the
range dates have been appended. You can see in my case why the
ListGetAt(currentKey, 2, "|") EQ "range" in the
DisplayList tag wasn't working, as it was evaluating to 'search' and not
'range'.
voyageeventlist.cfm|search|eventstartlocal|range=8%2F10%2F2005%2010%3A05%3A0
0AM%20
;8%2F20%2F2005%2010%3A05%3A00AM%20;datetime
Rerun setup and watch this sesssion variable.
Jeff
Mark Fuqua wrote:
Well Jeff,
I changed the suppressTime to ="no" and it looks the same, except the time
value is there. I do not have a program that compares text files, is it
possible the SearchFormFieldValidation you sent was a copy of the old one?
Was there something to do other than copy new SearchFormFieldValidation
and
change that line in DisplayList.cfm? Do I need to run SetUp.cfm again?
Thanks guy. Have a great night. I am sure the fix works, I just missed
something. Talk to you tomorrow.
Mark
Form fields:
REPORTDATE_CRITERIALOWER=8/10/2005 12:00AM
REPORTDATE_CRITERIALOWERAMPM=AM
REPORTDATE_CRITERIALOWERDAY=10
REPORTDATE_CRITERIALOWERMONTH=8
REPORTDATE_CRITERIALOWERTIME=12:00
REPORTDATE_CRITERIALOWERYEAR=2005
REPORTDATE_CRITERIAUPPER=8/17/2005 12:00AM
REPORTDATE_CRITERIAUPPERAMPM=AM
REPORTDATE_CRITERIAUPPERDAY=17
REPORTDATE_CRITERIAUPPERMONTH=8
REPORTDATE_CRITERIAUPPERTIME=12:00
REPORTDATE_CRITERIAUPPERYEAR=2005
REPORTDATE_OPERATOR=BETWEEN
REPORTDATE_TYPE=datetime
Query Info:
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
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Mark
Fuqua
Sent: Friday, August 19, 2005 9:09 PM
To: [email protected]
Subject: RE: [plum] date ranges and whereClauseExtension with AND
Good evening Jeff,
Well, I still don't have it working, but it's close (the list gets
returned
with all records, unfiltered). Now the criteria_Lower and criteria_Upper
do
have values, but the reportDate type is incorrectly set as dateTime.
Check
it out:
REPORTDATE_CRITERIALOWER=8/10/2005
REPORTDATE_CRITERIALOWERDAY=10
REPORTDATE_CRITERIALOWERMONTH=8
REPORTDATE_CRITERIALOWERYEAR=2005
REPORTDATE_CRITERIAUPPER=8/17/2005
REPORTDATE_CRITERIAUPPERDAY=17
REPORTDATE_CRITERIAUPPERMONTH=8
REPORTDATE_CRITERIAUPPERYEAR=2005
REPORTDATE_OPERATOR=BETWEEN
REPORTDATE_TYPE=datetime
I have the type set as date on the search form and suppressTime="yes". I
think I'll try setting suppressTime="yes" and see what I get. at least
the
upper and lower have a value. But the query is still getting WHERE
reportDate:
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
I hope you have a good night sleep.
Mark
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Jeff
Fleitz
Sent: Friday, August 19, 2005 5:56 PM
To: [email protected]
Subject: Re: [plum] date ranges and whereClauseExtension with AND
Hi Mark,
That is the line. I'm sorry, I must have been playing with the undo/redo
in
my editor. I checked another copy, and that
value at column 88 is a 2 and not a 3. Change it to 4.
Jeff
Mark Fuqua wrote:
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.c
f
m
********************************************************************
*
*
*********************************************************************
*
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.cf
m
*********************************************************************
*
*********************************************************************
*
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.cf
m
*********************************************************************
*
**********************************************************************
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
**********************************************************************
**********************************************************************
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
**********************************************************************
