This question about Missing functionality: Asked

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
 

QuestionForm edit

Subject Missing functionality
Extension SpreadSheetPlugin
Version Foswiki 2.1.4
Status Asked
Related Topics
Topic revision: r3 - 11 Jan 2020, BramVanOosterhout - This page was cached on 03 Jun 2020 - 21:29.

The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy