Question
I have a form based application for logging events. I would like to have a search that will display topics that were created during a certain date range. I loved the date parameter in search, but that is unfortunately the modified date. I have the created date stored in a formfield. I tried to do a search with conditional results. However, the $IF function does not appear to have an elseif. I tried a nested $IF, that didn't work either. Looking for suggestions. Plenty more specific information is available if/when needed.
Environment
--
MatthewCardozo - 28 Jun 2006
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.
With the
SpreadSheetPlugin you can nest
$IF()
if needed. Here is how I would approach your task:
- Convert the create date to the serialized date number and assign it to a variable, with
$SET(createdate, $TIME($createdate))
- Set a
show
variable to 1
- Do two sequential
$IF()
to compare the createdate
to the serialized start and end date, setting show
to 0 if out of range
- Output the search result if
show
is 1
This can all be done in the
format=""
of a
FormattedSearch. Make sure to delay CALC so that it evaluates once for each search hit:
format="$percntCALC{$SET(createdate, $TIME($createdate))$SET(show, 1)$IF(.....}$percnt"
.
For better performance you can set the start and end date variables before the SEARCH.
Let us know your progress; if possible share your
format=""
.
--
PeterThoeny - 28 Jun 2006
So, i'm in the process of trying to implement your suggestion. I'm having a little difficulty. Here's what I Have:
<form action="%SCRIPTURLPATH{"view"}%/%WEB%/%TOPIC%">
Find closed topics by date range (!YYYY-MM-DD, ex 2006-06-21): <br>
*From:* <input type="text" name="fromDate" size="32" value="%URLPARAM{"fromDate"}%" />
*To:* <input type="text" name="toDate" size="32" value="%URLPARAM{"toDate"}%" /> <input type="submit" class="twikiSubmit" value="Search" />
<input type="hidden" name="CategoryNonNet" value="CategoryNonNet">
</form>
<br> _For a single date, put the date in both "To" and "From" fields._
---++ Past Issues
%CALC{$SET(fromDate, $TIME(fromDate)) $SET(toDate, $TIME(toDate))}%
%SEARCH{"![T]ype.*value=\"IDSScan"" scope="text" regex="on" excludetopic="TestDateRangeSearch" nosummary="on" nototal="on" nosearch="on" reverse="on" noheader="on" expandvariables="on" zeroresults="off" format="%CALC{$SET(created, $TIME($Created)) $GET(fromDate) $IF($GET(created) > $GET(fromDate), <, >) $GET(created) $IF($GET(created) < $GET(toDate), <, >) $GET(toDate)}%<br>"}%
I left the
%'s% in there becuase when i do
$percent it doesn't evaluate the expression at all. I get my format string unevaluated as an output for every result. I'm still working on it so I'll let you know how it goes. Thanks again for the help so far.
--
MatthewCardozo - 28 Jun 2006
Already found one mistake. I forgot to use the %URLPARAM{}% in my $SET statements. Will report back.
--
MatthewCardozo - 28 Jun 2006
OK, so I'm now just having my
format=""
section show the serialized fromDate - $Created - serialized toDate.
That seems to be working. When I try to set the $Created to a serialized value with
$TIME()
it returns a 0. This seems to be throwing off my results.
--
MatthewCardozo - 28 Jun 2006
I'm continuing to strip stuff off to get to the root of the problem.
$SET(createdate, $TIME($createdate))
is returning 0. That needs to work for any of the rest of this to work. Any ideas?
--
MatthewCardozo - 28 Jun 2006
format="%CALC{...}%
gets executed before the SEARCH, you need to write
format="$percntCALC{...}$percnt
to have it evaluated once per hit (notice
$percnt
, not
$percent
.)
--
PeterThoeny - 28 Jun 2006
Unless it's just not listed in the accepted formats for
$TIME()
, the format I'm getting for
$createdate
: DD Mon YYYY - HH:MM is not a valid input for
$TIME()
this looks to be why that's not working. Is there another way around this? Maybe a way to get
$createdate
into an accepted format.
--
MatthewCardozo - 28 Jun 2006
Geez, I've done that
$percent
thing before so I'm a little embarrassed that I missed it again. I'm still having an issue getting the createdate into a serialized format however.
--
MatthewCardozo - 28 Jun 2006
Lets try:
- Search with plain
$createdate
and assigning it to a variable: 2006-06-28 - 18:32
- Get variable: 2006-06-28 - 18:32
- Serialized date: 1151544720
--
PeterThoeny - 29 Jun 2006
That seemed to do it. Thanks a ton for your help. Wonder why serializing
$createdate
directly doesn't want to work right. Well here's where I'm at with my format. It may not be the best way to do this, but the logic is working properly:
$percntCALC{$SET(created, $createdate) $SET(createSer, $TIME($GET(created))) $IF($GET(fromDate) < $GET(createSer), $SET(from, 1), $SET(from, 5)) $IF($GET(toDate) > $GET(createSer), $SET(to, 1), $SET(to, 6)) $IF($GET(from) == $GET(to), $SET(show, 1), $SET(show, 0)) $IF($GET(show) != 1, <nop>, *lots of tabular formatting here*)}$percnt"}%
The problem I'm hitting now is that one of the formfields I'm trying to display is breaking everthing. It's a 60x5 textarea I've named Details. So if I have
$formfield(Details)
anywhere in there, it does some really odd stuff. I can put in any other formfield and it works perfectly. I'm going to do some further testing with another form and textarea. Is this a known issue?
--
MatthewCardozo - 29 Jun 2006
My testing netted success. It turns out there are some characters that don't work within the
$IF()
statement. Carriage returns ('s )'s stuff like that don't work inside the formfield. So, I popped a
$TRIM()
around my
$formfield()
and it worked great for the carriage returns. I'll have to add a note for now about not using paren's. Thanks again for all the help. I was stuck for sure. This exercise has given me a much better understanding of SEARCH{}. I hope I can help answer questions soon.
--
MatthewCardozo - 29 Jun 2006
Glad it worked out.
--
PeterThoeny - 01 Jul 2006