SID-01342: How to... sum formfield values

Status:	Answered	TWiki version:	4.0.1	Perl version:	Not sure
Category:	SpreadSheetPlugin	Server OS:	Windows	Last update:	14 years ago

Hi,

Looking for help to see how I can sum the values for a particular formfield across a number of topics containing that formfield. If possible I would like to first filter the topics to those that have a particular value for a different formfield. In terms of display I only want to output the summed value. Is this possible?

Thanks Colin

-- ColinStapleton - 2011-11-30

Discussion and Answer

Use a CALC in a SEARCH format, something like this (untested) :

%CALC{$SET(total, 0)}%
%SEARCH{
 "whatever..."
 format="$percntCALC{$IF($formfield(FieldA) > 10, $SETM(total, +$formfield(FieldB)))}$percnt"
}%
Total: %CALC{$GET(total)}%

Details in SpreadSheetPlugin (see example), FormattedSearch, VarSEARCH.

-- PeterThoeny - 2011-11-30

This is a great help thanks. However, try as I might I can't get the formfield evaluation to work. Here's my script:

%SEARCH{ "[F]y" scope="text" type="regex" nosearch="on" nototal="on" format="$percntCALC{$IF($formfield(Fy) = \"2011\", $IF(defined $formfield(Amount), $SETM(total, +$formfield(Amount))))}$percnt"}%

I think I am not escaping properly but I can't pinpoint the error. Any help with this would be greatly appreciated.

-- ColinStapleton - 2011-12-01

The IF formula is wrong. Read SpreadSheetPlugin. Compare an integer using ==, or compare a string using $EXACT().

-- PeterThoeny - 2011-12-01

Ok makes sense. Is there a way to include formfields in an $EXACT() comparison? I tried this:

%CALC{"$EXACT($formfield(Fy), 2012)"}%

But always returns 0.

Using %FORMFIELD% I am able to verify that Fy value is 2012.

Thanks Colin

-- ColinStapleton - 2011-12-01

Should work. Is there maybe some whitespace around 2012?

-- PeterThoeny - 2011-12-01

Your suggestion about whitespace gave me an idea. I tried

%CALC{"$LENGTH($formfield(Fy))"}%

And returns 14 which is the number of characters in the string "$formfield(Fy)". Hence I think what is happening is that the $EXACT() and also the $LENGTH() functions are not resolving the formfield values as you would expect. Could this be a fault in version of twiki I am using (4.05 personal for windows). Perhaps it is time I stepped up to the proper webserver version?!

-- ColinStapleton - 2011-12-02

$formfield(Fy) only gets resolved in a SEARCH format="".

-- PeterThoeny - 2011-12-02

Eureka. Thanks a million

-- ColinStapleton - 2011-12-03

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.