Support for BELOW function (similar to ABOVE)

I have an editable Table that contains a list of inventory item types. The first column is called "Type". I would like to store all user-defined types in a variable and use it afterwards. It would be great if there was a function in the Spreadsheet plugin that works as the "ABOVE" function but to refer to cells below. For example:

Type	Description
Apple	Red or green fruit	edit
Orange	Great color	edit
Kiwi	Brown outside and very sour	edit

The workaournd I tries is adding an additional row to get the item types, but it looks ugly, and it might be edited by a user by mistake.

Type	Description
Apple	Red or green fruit	edit
Orange	Great color	edit
Kiwi	Brown outside and very sour	edit
		edit

Apple, Orange, Kiwi

Is there any other way I can do this?

-- KostasAvgeropoulos - 09 Jan 2020

Hi Kostas, I would use FilterPlugin and the FORMATLIST macro. It has an easier syntax (IMO) than CALC and is simpler to nest.

There is a collection of examples for FORMATLIST in the blog.

If you want a worked example for your problem, let me know. FilterPlugin is installed at foswiki.org.

-- BramVanOosterhout - 10 Jan 2020

Introduction

Below is the complete solution of your problem.

I have wrapped the table in a STARTSECTION{ "table01" } ... ENDSECTION{ "table01" }. The EXTRACT gets the table rows following the Type header row. FORMATLIST takes the table, splits across the rows, takes the content of the first column and presents it as a comma separated list.

You can get the comma separated list anywhere in your wiki by %INCLUDE{ "%TOPIC%" section="2023" }%.

Note the expand="off" in the EXTRACT. It allows you to include the EDITTABLE, without the macro expansion. A regular INCLUDE would expand the table html before you get to filter the resulting text. That complicates the regex considerably.

You can also move the extract and 2023 sections to another wikitopic so they don't show on the page with the table. Change the topic="..." in the EXTRACT to the topic that contains the table. See ExampleSupport2023.

Good luck.

The table

Type	Description
Apple	Red or green fruit	edit
Orange	Great color	edit
Kiwi	Brown outside and very sour	edit

Extract

Apple	Red or green fruit
Orange	Great color
Kiwi	Brown outside and very sour

%STARTSECTION{ "extract"}%
%EXTRACT{
   topic="%TOPIC%"
   pattern="STARTSECTION{\s*\"table01.*?\|[^|]*Type[^\n]+\n(.*?)\n%ENDSECTION"
   format="$1"
   expand="off"
}%

%ENDSECTION{ "extract"}%

Result

Apple,Orange,Kiwi

%STARTSECTION{ "2023" }%
%FORMATLIST{
   "%INCLUDE{ "%TOPIC%" section="extract" }%"
   split="\n"
   pattern="^\|\s*([^|]+?)\s*\|"
   format="$1"
   separator=","
}%
%ENDSECTION{ "2023" }%

-- BramVanOosterhout - 11 Jan 2020