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