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