Item9734: CALC's expansion behaviour should be more consistent with other macros when no table cells are addressed

I ran into this problem today on our corporate wiki. I'm doing a formatted search, displaying the results in a table, and using the SpreadSheetPlugin CALC macro to filter out results if a formfield doesn't match a particular value. In one case, I had a topic where one of the formfields being displayed contained a comma. This resulted in some messed up output, as you can see below (providing nobody changes the Codebase fields for the Tasks I've referenced). See raw/wiki text for the searches I'm doing.

With no CALC the search output is fine - even when formfields have commas (displaying items 9660-9669)

Item	Codebase	Reporter	Priority
Tasks.Item9660	1.0.0 beta1, trunk	KennethLavrsen	Urgent
Tasks.Item9661	1.0.0 beta1, trunk	ArthurClemens	Normal
Tasks.Item9662	1.1.5, 1.0.9, trunk	LeilaPearson	Normal
Tasks.Item9663	1.1.0 beta1, trunk	GeorgeClark	Normal
Tasks.Item9664	1.1.0 beta1, trunk	CrawfordCurrie	Normal
Tasks.Item9665	1.1.0 beta1, trunk	ArthurClemens	Urgent
Tasks.Item9666		WillNorris	Normal
Tasks.Item9667	1.1.0 beta1, trunk	LarsEik	Urgent
Tasks.Item9668	1.1.0 beta1	LarsEik	Normal
Tasks.Item9669	1.1.0 beta1, trunk	SvenDowideit	Normal

With CALC, the search output is fine as long as formfields don't have commas (displaying Items 9666 and 9668)

Item	Codebase	Reporter	Priority
Tasks.Item9666		WillNorris	Normal
Tasks.Item9668	1.1.0 beta1	LarsEik	Normal

With CALC, the search output goes awry if a formfield has a comma (displaying Items 9666-9668)

Item	Codebase	Reporter	Priority
Tasks.Item9666		WillNorris	Normal

trunk | LarsEik | Urgent |, <nop>

Tasks.Item9668

1.1.0 beta1

LarsEik

Normal

-- LeilaPearson - 21 Sep 2010

This is a fault of SpreadSheetPlugin. It uses commonTagsHandler to expand %CALC% expressions, so does strange and inconsistent things, behaving differently wrt expansion order/artefacts compared with other macros.

To compare, let's just use IfStatements instead:

With IF, formfields with comma (displaying Items 9666-9668)

Normal priority is displayed with , others - if we really wanted to filter instead of decorate each result, we should use a query expression to exclude non-Normal priority tasks instead

Item	Codebase	Reporter	Priority
Tasks.Item9666		WillNorris	Normal
Tasks.Item9667	1.1.0 beta1, trunk	LarsEik	Urgent
Tasks.Item9668	1.1.0 beta1	LarsEik	Normal

This is the code:

%SEARCH{
  "Item966[6-8]"
  web="Tasks"
  scope="topic"
  type="regex"
  order="topic"
  nonoise="on"
  header="| *Item* | *Codebase* | *Reporter* | *Priority* | "
  format="$percntIF{
      \"'$topic'/Priority='Normal'\"
      then=\"| $dollarpercntY$dollarpercnt [[Tasks.$topic]] | $formfield(Codebase) | $formfield(ReportedBy) | $formfield(Priority) |\"
      else=\"| $dollarpercntX$dollarpercnt [[Tasks.$topic]] | $formfield(Codebase) | $formfield(ReportedBy) | $formfield(Priority) |\"
    }$percnt"  
}%

Refer to the new new FormattedSearch documentation on trunk/1.1

Let me know if the solution (not using CALC) is appropriate for you.

I'm afraid getting CALC to behave properly might require a lot of work. Unless we make a new version that is a registerTagHandler macro, like %CALC_{}%? Yuck..

Foswiki lacks table processing in the parser so CALC will have to continue its existence as a commonTagsHandler type macro, but maybe we can use a registerTagHandler version of CALC that tries to determine if the CALC contains any references to table cells - and if so, does nothing (let commonTagsHandler pick it up). If no reference to a table cell - like in this case - then the registerTagHandler can handle the CALC expression without ever letting the commonTagsHandler touch it.

BTW, don't filter in the output; use something like this (see QuerySearch):

Item	Codebase	Reporter	Priority
Tasks.Item9661	1.0.0 beta1, trunk	ArthurClemens	Normal
Tasks.Item9662	1.1.5, 1.0.9, trunk	LeilaPearson	Normal
Tasks.Item9663	1.1.0 beta1, trunk	GeorgeClark	Normal
Tasks.Item9664	1.1.0 beta1, trunk	CrawfordCurrie	Normal
Tasks.Item9666		WillNorris	Normal
Tasks.Item9668	1.1.0 beta1	LarsEik	Normal
Tasks.Item9669	1.1.0 beta1, trunk	SvenDowideit	Normal

Looks like:

%SEARCH{
  "Priority='Normal'"
  type="query"
  topic="Item966*"
  order="topic"
  nonoise="on"
  header="| *Item* | *Codebase* | *Reporter* | *Priority* | "
  format="|  [[Tasks.$topic]] | $formfield(Codebase) | $formfield(ReportedBy) | $formfield(Priority) |"
}%

-- PaulHarvey - 22 Sep 2010

Thanks. That's a big help. I fixed the two pages where I was using "conditional output" searches to do a query search instead - and now fields with commas don't cause problems.

When I was trying to figure out how to display what I wanted, I was following the advice here: FormattedSearch#Search_with_conditional_output

It looks like that documentation has been overhauled in the trunk, as you mentioned, and won't lead people like me so astray anymore

The old documentation seemed to work (until the unexpected issue with commas came up) and I wasn't familiar enough with query searches to realize that was a better option in this case. The search is much clearer and cleaner this way.

As for the problem with SpreadSheetPlugin, I'm not sure what the best solution is. It sounds complicated...

-- LeilaPearson - 22 Sep 2010

this will hopefully be addressed by Item8417 in the 1.2.0 release

-- SvenDowideit - 05 Nov 2012

Foswiki 1.2 has added a %CALCULATE% macro, which expands as a conventional registered Tag instead of in the common tags handler. This is expected to resolve the issue.

-- GeorgeClark - 17 Jun 2014

Commenting is disabled while not logged in