ForEachPlugin and SpreadSheetPlugin interaction

Environment:

• ForEachPlugin and SpreadSheetPlugin: ForEachPlugin is listed first in {PluginsOrder}
• You can see this live on http://extranet.blendedperspectives.com/Sandbox/PivotTest

Client - count	Client
Worker	Client 1	Client 2	Client 3	Client 1	Totals
Worker 1	3	1	2	2	8
Worker 2	2	2	0	0	4
Worker 3	0	2	1	0	3
Totals	5	5	3	2	15

Questions:

1. Why does 4, which is static per row, evaluate to an empty string, whereas the same expression (CALC{"$T(R3:C2)"}%) later on the page outside the loop works fine
2. Should I be using 2 or 3 and why does neither result in an answer given that the parameters are changing?

%FOR{"outer" start="3" stop="5" step="1"}%
%FOR{"inner" start="2" stop="4" step="1"}%
  $outer:$inner = $T(R$outer:C$inner) = %<nop>CALC{"$T(R$outer:C$inner)"}%
= =1= $percntCALC{"$T(R$outer:C$inner)"}$percnt = =2= %CALC{"$T(R$outer:C$inner)"}% 
= =3= %CALC{"$PROPER($outer:$inner)"}% =4= = (%CALC{"$T(R3:C2)"}%)
%NEXT{"inner"}%
%NEXT{"outer"}%

%FOR{"outer" start="3" stop="5" step="1"}% %FOR{"inner" start="2" stop="4" step="1"}% $outer:$inner = $T(R$outer:C$inner) = %CALC{"$T(R$outer:C$inner)"}% = 1 $percntCALC{"$T(R$outer:C$inner)"}$percnt = 2 = 3 $Outer:$Inner 4 = (3)
%NEXT{"inner"}% %NEXT{"outer"}%

• Note that the same expression outside of the FOR result in correct answers

%CALC{"$T(R3:C2)"}% = 3
%CALC{"$T(R1:C1)"}% = Client - count

• Question: is it possible to reference the cells by name?

%FOREACH{"col" in="Client 1, Client 2, Client 3, Client 4"}% %FOREACH{"row" in="Worker 1, Worker 2, Worker 3"}% $row $col, = WHATEXPRESSION{"$row","$col"}
%NEXT{"row"}% %NEXT{"col"}%

---

ForEachPlugin is clearly messing with the static parameters passed to SpreadSheetPlugin.

I asked Colas Nahaboo, Michael Daum and Eugene Mayer about this. None would even try to do this in TML.

We could fix ForEachPlugin, but that's missing the point.

My conclusion: it's become to convoluted to do even this simple thing.

From the time I wasted, I agree. I think Foswiki should Development.RetireTMLForScripting.

-- MartinCleaver - 09 Feb 2010

Actually storing sensitive data into a foswikiTable has always been too fragile. Processing it later is a pita.

There are a couple of other plugins that might work instead of CALC+FOREACH. Try FilterPlugin or RenderTableDataPlugin (still in T* namespace) to access the cells in a slightly more robust way.

Mocking around with the evaluation order of TML like both, SpreadSheetPlugin and ForEachPlugin do is bound to be troublesome. They do so because there's no means to parse a foswikiTable in core Foswiki other than to generate HTML from it, as strange as it sounds.

-- MichaelDaum - 09 Feb 2010

Note that Extensions.RenderTableDataPlugin has been released in Foswiki namespace.

-- GeorgeClark - 25 Feb 2010

Foswiki needs to provide a table parser/iterator that plugins can easily hook into and utilise in a rationalised way.

So rather than "retire TML for scripting", actually a better approach would be to implement the missing table handling functionality into Foswiki core so that these plugins could hook into that and co-exist happily as registerTagHandler() macros instead of fighting each other with regexes in commonTagsHandler()

-- PaulHarvey - 13 Jul 2010

Don't use this plugin. Please see Faq39 instead.

-- PaulHarvey - 22 Dec 2010