Question

In working on Sandbox.CollaborativeGroupwareSoftware I have a formatted search that I would like to limit to a one month window. Suggestion on the Sandbox page would be great too. The date is coming from a formfield. Here's the twiki source for the top search on that page:

%SEARCH{"groupname.*?value=\"" scope="text" regex="on" nosearch="on" nototal="on"
order="formfield(groupupdated)" reverse="on" header="| *Updates during the last month* | *Updated* | "
format="| $formfield(groupsection): $formfield(groupname), <em>lookup magic change comments here</em>
[[$topic]] | $formfield(groupupdated) | "}%

So my question is, how can I limit the data to the last month based on date?

I'm guessing the solution is a bunch of regex-perl for the initial search that I will struggle with. If I need to change the date data itself (from 2003.01.03) to be a real number such as 20030103 it might make this much easier by allowing a simple number comparison. If I was doing this with a shell script I would use the Unix way of seconds since 1970. I don't know if there's another way to do this. It's complex since the data in the column is a formfield itself. I thought I would put it out there in case someone else has a similar problem or might know the answer quickly. When I solve the problem I will of course share my results here.

• TWiki version: twiki.org
• Web browser & version: Mozilla
• Client OS: Linux

-- GrantBow - 03 Jan 2003

Answer

One approach is to have a groupupdatecomment form field instead of a comment table. This which limits you to show just the last comment, but you get it in the RCS history anyway. Search then picks just form fields, e.g. format="| $formfield(groupsection): $formfield(groupname), $formfield(groupupdatecomment) [[$topic]] | $formfield(groupupdated) | "}%.

Like this you can sort the search by topic update date. The tricky part is to limit the search. Currently it is only possible to limit by number of entries, e.g. limit="10". You would need a hacked search where you can specify a limit, e.g. limit="last 30 days".

A workaround might be to use SpreadSheetPlugin formulas to calculate the last month string, then search for the current month and last month. This would show 30 to 60 days, depending on the current date.

Here is the table to calculate the last month (HTML-hide this table in your real example) :

Edit the topic to see the formula. This is kind of a complicated formula because the current Plugin does not do mod and div calculations. The Plugin could be enhanced with that.

Once you have the strings for the current month and previous month you can search for "groupupdated.*?value=\"(%CALC{"$T(R1:C2)"}%|%CALC{"$T(R3:C2)"}%)"

-- PeterThoeny - 05 Jan 2003

Interesting, those are some great techniques. I'll have to think more about how best to implement this. Is it possible to do a Formatted Search on all of the RCS versions of one topic? This would make life very easy and accomplish the goals I have for this function.

-- GrantBow - 09 Jan 2003

Not really. Well, theoretically if you do an INCLUDE of the rendered HTML pages, one per version (instead of the topic), e.g. include https://www.twiki.org/cgi-bin/view/Support/LimitFormattedSearch?rev=1.4. You can add a regular expression to screen-scrape only the text you need. This will be slow because it will be the rendering speed of one page times the number of INCLUDES done.

With the latest TWikiBetaRelease you can also IncludePreviousTopicRevision, which is faster.

-- PeterThoeny - 14 Jan 2003