how can I combine SEARCH formfield and SpreadSheetPlugin

I want to create a dynamic list of topics. Formatted output shall contain conent of a formfield, but about 1000 old topics do not contain this field, instead I want to use $createdate (but only for this old topics)

what I tried:

%SEARCH{ "ToolName_.*_ToolUpdate" regex="on" scope="topic"  nosearch="on" 
  header="|*Topic*|*Release Date*| " 
  format="| $topic | %CALC{$IF( $EMPTY($formfield(ReleaseDate)), $createdate, $formfield(ReleaseDate) )}% |"
}%

I am not getting the $createdate, i.e. formfield is never empty. Is this because of order of SEARCH and SpreadSheetPlugin is executed - I do not think so? What am I doing wrong here- how can I overcome this issue?

I tried a live example in this web, the string "no version defined" shall be displayed if field is empty:

%SEARCH{ "BestPracticeTip1\d" regex="on" scope="topic"  nosearch="on"
  header="|*Tip*|*Version*| " 
  format="| $topic | %CALC{$IF( $EMPTY($formfield(Version)), no version defined, $formfield(Version) )}% |"
}%

results in

Tip	Version
BestPracticeTip10	Foswiki 1.0.0
BestPracticeTip11	TWikiRelease04x03x00
BestPracticeTip12
BestPracticeTip12Form
BestPracticeTip13	Foswiki 1.0.9
BestPracticeTip14
BestPracticeTip15
BestPracticeTip16	Foswiki 1.1.3
BestPracticeTip17	Foswiki 1.0.9
BestPracticeTip18
BestPracticeTip19	Foswiki 1.0.9
BestPracticeTip19ViewTemplate
BestPracticeTip19aViewTemplate

-- MatthiasThullner - 06 Jun 2011

This can be done by the following query search:

%SEARCH{"name~'BestPracticeTip1*'" web="%WEB%" type="query" nosearch="on"
  header="|*Tip*|*Version*|"
  format="| $topic | $percentIF{\"'$topic'/Version\" then=\"$formfield(Version)\" else=\"no version defined\"}$percent |"
}%

which results in:

Tip	Version
BestPracticeTip1	Foswiki 1.0.6
BestPracticeTip10	Foswiki 1.0.0
BestPracticeTip11	TWikiRelease04x03x00
BestPracticeTip12	no version defined
BestPracticeTip12Form	no version defined
BestPracticeTip13	Foswiki 1.0.9
BestPracticeTip14	no version defined
BestPracticeTip15	no version defined
BestPracticeTip16	Foswiki 1.1.3
BestPracticeTip17	Foswiki 1.0.9
BestPracticeTip18	no version defined
BestPracticeTip19	Foswiki 1.0.9
BestPracticeTip19ViewTemplate	no version defined
BestPracticeTip19aViewTemplate	no version defined

So you should redefine your regex search as query search and/or use the delayed IF in the format string as I did below:

%SEARCH{ "ToolName_.*_ToolUpdate" regex="on" scope="topic"  nosearch="on" 
  header="|*Topic*|*Release Date*| " 
  format="| $topic | $percentIF{\"'$topic'/ReleaseDate\" then=\"$formfield(ReleaseDate)\" else=\"$createdate\"}$percent |"
}%

-- FranzJosefGigler - 09 Jun 2011

Danke Franz Josef,

on my system (Foswiki 1.0.9) $percent does not work, but $percnt does

%SEARCH{ "ToolName_.*_ToolUpdate" regex="on" scope="topic"  nosearch="on" 
  header="|*Topic*|*Release Date*| " 
  format="| $topic | $percntIF{\"'$topic'/ReleaseDate\" then=\"$formfield(ReleaseDate)\" else=\"$createdate\"}$percnt |"
}%

-- MatthiasThullner - 13 Jun 2011