Question

I like the new QuerySearch very much, but I can't figure out how to construct a search that returns topics in a particular date range.

For example, I want to return a list of topics that have a formfield "DateIssued" that is within one week of today.

I've tried using CALC inside a SEARCH, but I suspect the SEARCH gets evaluated before the CALC:

%SEARCH{"d2n('DateIssued') > $percntCALC{$TIMEADD($TIME(), -604800, second)}$percnt" web="NCR" type="query" nonoise="on" format="| [[$topic]] | $formfield('DateIssued') | $formfield('ProjectNumber') | $formfield('Issuedby') |  $formfield('Description') |"}%

Is there an easy way (or even a painful way) to do this? I'd love it if there were a few more date related operators in QuerySearch.

Environment

-- DeanSpicer - 29 Feb 2008

Answer

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.

I've also tried using a local variable to store the calculated date (one week ago):

   * Local LASTWEEK = %CALC{$TIMEADD($TIME(), -604800, second)}%

%SEARCH{"d2n('DateIssued') > $percntLASTWEEK$percnt" web="NCR" type="query" nonoise="on" format="| [[$topic]] | $formfield('DateIssued') | $formfield('ProjectNumber') | $formfield('Issuedby') |  $formfield('Description') |"}%

I've also tried using TimeSpecifications strings like:

%SEARCH{"d2n('DateIssued') > d2n('P3w')" web="NCR" type="query" nonoise="on" format="| [[$topic]] | $formfield('DateIssued') | $formfield('ProjectNumber') | $formfield('Issuedby') |  $formfield('Description') |"}%

That didn't work either. I still haven't found a way to calculate a date for use inside a QuerySearch.

-- DeanSpicer - 03 Mar 2008

Still haven't given up on this, my latest attempt looks like:

%CALC{"$percntSEARCH{\"d2n(DateIssued) > $TIMEADD($TIME(), -604800, second)\" web=\"NCR\" type=\"query\"}$percnt"}%

The above attempt was derived from an example in HowToCombineCalcAndSearch

That doesn't work either. Any ideas would be welcome.

-- DeanSpicer - 27 Mar 2008

Sorry, closing this question after more than 30 days of inactivity. Feel free to re-open if needed.

-- PeterThoeny - 01 May 2008

Hi Dean,

I was excited to find your support question as I have been trying to solve the same puzzle. I'm trying to build an HTML search form where the user enters a date, and the search then retrieves all of the topics that have been modified on or after that date.

After trying various approaches, some of which you described above, I was finally able to get it to work simply by putting the normal CALC variable inside the search string, except WITHOUT any quotes inside the {} section of the variable, e.g. CALC{"$TIME()"}, which is how it instructs you to write it on the TWikiVariables page. From what I've been reading, it seems like you only need to substitute $percnt for % if the CALC is going in the "format=" section of a search variable, not if the CALC is part of the actual search string itself.

The only other hint I can offer for this type of calculated search is that a date format with /s wouldn't work, e.g. 05/01/2008. The / format would work in a local variable as you described, but not in the actual SEARCH string. Switching the date format to "01 May 2008" made the CALC work within the search string.

Here is my SEARCH string for reference:

%SEARCH{"form.name ~ "*DocumentationForm" AND ReviewedInAnnualAudit = "Yes" AND web ~ "**" AND info.date >= "%CALC{$TIME(%URLPARAM{"qD"}% %URLPARAM{"qM"}% %URLPARAM{"qY"}%  )}%"" excludetopic="Web*"...%

Hope this helps.

-- GarySprague - 12 May 2008

Dean,

Sorry, can't seem to get the CALC part to show up without evaluating. So the part in the search string that says "info.date >= "0"" really looks like this (without the spaces in between the % and the rest):

info.date >= "% CALC{$TIME(% URLPARAM{"qD"} % % URLPARAM{"qM"} % % URLPARAM{"qY"} % )} %""

-- GarySprague - 12 May 2008

To answer the original question, you should be able to do this using SpreadSheetPlugin by delaying evaluation of the SEARCH until SpreadSheetPlugin has evaluated the %CALC. Something like this:

%CALC{"$CHAR(37)SEARCH{$CHAR(34)dtn(when)>12345678$CHAR(34) type=$CHAR(34)query$CHAR(34)}$CHAR(37)"}%

(no query search on twiki.org, but you get the idea)

-- CrawfordCurrie - 12 May 2008

Wow, I just realized that d2n is a query search operator for doing exactly what I was trying to do with my embedded %CALC{$TIME()} function. I just tried swapping in d2n but it wasn't a cinch to get it to evaluate my %URLPARAMs as a string, so I'm sticking with my %CALC variable for the time being.

-- GarySprague - 12 May 2008