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