Question

Hi *,

I want to make a list of all topics created after a certain date and to make a statistics of users to see a winner who has created most topics.

With help of FormattedSearch I have come to following which lists the topics as I want. The spreadsheet formula just after the should calculate the statistics.

%SEARCH{ "." scope="text" regex="on" nosearch="on" nototal="on" order="modified" reverse="on" format="$percntCALC{$IF($TIME($createdate) < $TIME(2004/10/01 GMT), <nop>, | [[$topic]] | Main.$createusername | $createdate | $rev |)}$percnt" }% 
| Total: %CALC{"$ROW(-2)"}% | %CALC{"$COUNTITEMS( R2:C2..R$ROW(-1):C2 )"}% | | |

Table look nice, but the formula is not working. What am I doing wrong? Thanks,

Radek.

Environment

-- RadekSedlacik - 28 Dec 2004

Answer

Reason why the summary row does not render correctly: The SEARCH produces table rows conditionally, with <nop> for invalid lines (which gets removed at the end of the rendering process). Example result of SEARCH:

<nop>
<nop>
| ... | ... | ... |
<nop>
<nop>
| ... | ... | ... |
| ... | ... | ... |
<nop>
| ... | ... | ... |
<nop>
<nop>

That is, the CALC in the summary row just sees a <nop> above, e.g. does not see consecutive table rows with all <nop> removed.

To address this you can try one of:

1. Use space instead of <nop>:

• Specify separator=" " (space separator)
• No output for invalid lines
• Output the table row with a newline at the end for valid rows

2. Use Spreadsheet variables:

• In the formatted SEARCH, use $SET() variables to build the statistics, e.g. SETM(total, +1)
• In the summary row, use $GET() to show the stats

Let us know what worked out (with a working example?)

-- PeterThoeny - 29 Dec 2004

I'm trying to do something very similar to what Radek is doing: I want to only print out the first 25 results of a = across multiple webs; since the =limit="25" option would limit the results from each web, rather than the total number of results, I thought using SpreadSheetPlugin would help.

Anyway, I don't see how I can implement your first suggestion. Specifically, how do I conditionally output a newline? $n is replaced by FormattedSearch rather than inside $IF so I can't just put it at the end of the valid row branch, and if I put it outside of the %CALC%, the table isn't contiguous (just like using <nop>).

To use Radek's example, modified according to your first suggestion:

%SEARCH{ "." scope="text" regex="on" nosearch="on" nototal="on" order="modified" reverse="on" separator=" " format="$percntCALC{$IF($TIME($createdate) < $TIME(2004/10/01 GMT), , | [[$topic]] | Main.$createusername | $createdate | $rev |$n)}$percnt" }% 

Which might produce something like this:

%CALC{$IF($TIME(2005/03/04 GMT) < $TIME(2004/10/01 GMT), , | [[FooTopic]] | Main.FooCreator | 2005/03/04 GMT | 1.1 |
)}%

Which doesn't render properly because of the newline.

Is there something missing in my understanding? The only way around this I can think of is to use LISTJOIN which seems really awkward...

-- BryanThrall - 04 Mar 2005