SID-02053: How to filter search results by the date in a formfield

Status:	Answered	TWiki version:	6.0.1	Perl version:	5.8
Category:		Server OS:	Ubuntu	Last update:	11 years ago

Hi TWiki community,

I am setting up a simple task tracking system in TWiki. Each task is a page with a form to store structured data. One of the form fields is a date, for example the due date of the task.

I need to be able to search or filter the list of tasks based on date field operations, for example to find the list of tasks which are due in the next month.

Can anyone share a recipe for this, or suggest a direction to explore?

Due dates can be evaluated with the ActionTrackerPlugin, but I need a bit more flexibility than this plugin allows.

Any suggestions welcome.

Steve.

-- Steve Jones - 2015-04-13

Discussion and Answer

Disclaimer: I don't have much experience QuerySearch. So this is neither robust nor elegant:

• The basic selection of "tasks with a certain due time" would be done like this:
  • %SEARCH{"YourForm.DueDate.value < within the next month " type="query"};%
• The within the next month part could be done using the SpreadSheetPlugin: Convert the current time to a serial using $TIME, add a month using $TIMEADD, and then format it back to the ISO format I use in form fields.
  • %CALCULATE{$FORMATTIME($TIMEADD($TIME(),1,month),$year-$month-$day)}%

It would be more robust if the comparison could be done as an arithmetic comparison between two serials, but so far I failed to apply the d2n function of query search to the content of a form field.

-- Harald Jörg - 2015-04-13

Many thanks, Harald. Your solution worked for me as expected. This has been really helpful for me, and also made me understand the QuerySearch functionality which I had not explored until your response.

For anyone else who needs something similar, this was my search where IsComplete, DrivingProject and DueOn are all fields in a form which is attached to relevant task pages:

%SEARCH{"IsComplete = 'No' AND DrivingProject = 'Flow' AND DueOn <= '%CALCULATE{$FORMATTIME($TIMEADD($TIME(),1,month),$year-$month-$day)}%'" web= "all, -Trash" type="query

Thanks a lot.

Steve.

-- Steve Jones - 2015-04-14

      Change status to: Asked question Answered question Closed unanswered