Data Table Plugin

• Variables provided
  • DATATBL{"topic" format="..."} -- look up a table
• Basic use
• Format parameter
• Select rows
  • Basic
  • Multiple values in a column
  • Select rows having no value in a column
  • Select rows having neither of specified values
• Processing sequence
• Output processing
  • When there is no match
  • Iterate through values in a column
  • Different separators at different places
  • Transliteration
  • Referring to non-existent columns
• More on data source table
  • Values are always comma separated
  • Column names don't have to be unique
  • Multiple topics
  • Getting table data dynamically
  • How to specify a column whose name contains non-word characters
  • Transposed tables
  • Expand variables in cells
  • Treat null value as no match
• Other information
  • Efficiency
  • Debugging
• Plugin Info

Look up a table in the TWiki table markup and get specified rows of specified columns. %DATATBL{...}% is provided by this plugin. It is to table rows as %SEARCH{...}% is to topics.

Variables provided

DATATBL{"topic" format="..."} -- look up a table

• Look up a table in the TWiki table markup on the specified topic. And then compose an ouput in the similar manner as SEARCH{...}.
• Syntax: %DATATBL{ "topic" format="..." separator="..." }%
• Parameters:

  Parameter	Description	Default
  "topics"	comma separated topics whose first table is looked up. If multiple topics are specified, tables on the topics are combined and hanled as if there is one big table
  "escaped_variable"	Instead of comma separated topics, you can specify an escaped TWiki variable such as $percntINCLUDE{\"http://foo/bar\"}$percnt. Then the variable is expanded and the result becomes the data soruce. Read Getting table data dynamically for more information
  assumedcols="COLUMNS"	causes COLUMNS to be assumed. Read Referring to non-existent columns for more information
  evalcell="on"	causes variable expansion in table data. Read Expand variables in cells for more information	off
  COLUMN_NAME="VALUES"	select rows whose COLUMN_NAME column has either of comma separated VALUES. Read Select rows for more information
  by="COLUMN_NAME" value="VALUES"	Same as above. But if COLUMN_NAME starts with a lower case letter, this format needs to be used
  ignorenull="on"	causes null result to resort to ifnomatch value. Read Treat null value as no match for more information	off
  ignorecase="on"	causes string comparison to case insensitive	off
  sort="COLUMN" sorta="COLUMN" sortn="COLUMN"	sorts the output chunks based on a source table column. sort is for case-sensitive string sort, sorta is for case-insensitive (alphabetical) string sort, and sortn is for numeric sort. The sorting result is in the ascending order. To get the decending order, specify reverse="on" in addition	no sorting
  reverse="on"	specifies the order of output chunks to be reversed. If combined with sort="COLUMN", output chunks are sorted in the descending order
  limit="NUMBER"	specifies the maximum number of rows selected	no limit
  transposed="on"	species that the topics have transposed tables - the first column has field names and the second column has field values. Read Transposed tables for more information	off
  split="SPLIT_PATTERN"	may be used when transposed="on" (above) is specified. It specifies a pattern to split tables even within a table
  trfrom="SEARCH_LIST" trto="REPLACE_LIST" tropt="OPTIONS"	causes transliteration in the result. Read Transliteration for more information
  format="..."	specifies the format of each output chunk corresponding a selected source table row. Read Format parameter for more information	$Name
  separator="..."	specifies a separator between output chunks. This is the same as the separator parameter of SEARCH{...}	(null string)
  separatorN="..."	specifies a separator inserted every N output chunks. Read Different separators at different places for more information
  ifnomatch="STRING"	causes the STRING to be yielded when there is no matching row. Read When there is no match for more information

• Category: DevelopmentVariables, DatabaseAndFormsVariables, SearchingAndListingVariables
• Related: DataTablePlugin, SEARCH, FormattedSearch

Basic use

Let's assume the topic TableA has the following table as the first or sole table on the topic.

Name	Number
apple	5
orange	7
banana	10

Then, the following line is rendered into the lines after next.

%DATATBL{"TableA" format="   * $Name:  $Number" separator="$n"}%

• apple: 5
• orange: 7
• banana: 10

DATATBL{...} is to a table as SEARCH{...} is to topics.

• The unnamed parameter of DATATBL{...} specifies a topic having a table in TWiki markup
• The first row of the table specifies column names
• The second row onward constitute table data
• The data rows are iterated through and converted into output based on the format parameter

Format parameter

In the format parameter

• '$' followed by a column name such as $Name and $Number is expanded to the value of the column
• $rownum is expanded to the 0 origin row number on the data source table. $rownum1 is similar but it's 1 origin
• $resultseq is expanded to the 1 origin result sequence. $resultseq is identical to $rownum1 if no row selection and no sorting are performed. $rownum and $rownum1 are about the original data source table without row selection or sorting. $resutseq is about the each piece of output after selection and sorting are done.

In the format and separator parameters, the following special character escapes are available:

Notation	Meaning
$n or $n()	New line. Use $n() if followed by alphanumeric character, e.g. write Foo$n()Bar instead of Foo$nBar
$nop or $nop()	Is a "no operation". This variable gets removed; useful for nested search
$quot or \"	Double quote (")
$aquot	Apostrophe quote (')
$percnt	Percent sign (%)
$dollar	Dollar sign ($)
$lt	Less than sign (<)
$gt	Greater than sign (>)

Select rows

Basic

Assuming the same TableA topic as above, the following line yields the lines after next.

%DATATBL{"TableA" Name="apple, banana" format="   * $Name:  $Number" separator="$n"}%

• apple: 5
• banana: 10

This is how to select rows.

• A parameter of the COLUMN_NAME="VALUES" format selects only rows whose COLUMN_NAME column has VALUE. For this way of row selection to work, COLUMN_NAME must start with a capital letter. Otherwise, you need to have by="COLUMN_NAME" and value="VALUES" parameters. As such, column names starting with a capital letter are recommended.
• Instead of selecting rows by column values, you can specify 0 origin row numbers separated by comma using the rows parameter e.g. rows="1, 3"

Multiple values in a column

Let's assume the topic TableB having the following table.

Name	Favorite
John	apple, orange
Jane	apple, banana
Joe	banana, strawberry
Mary
Chris	kiwi, papaya

The next line is rendered into the line after next.

%DATATBL{"TableB" Favorite="banana" format="$Name" separator=" and "}% like banana.

Jane and Joe like banana.

Here's what's happening.

• COLUMN_NAME="VALUES" checks values separated by comma in a column rather than an entire column

Select rows having no value in a column

You may want to select rows having no value in a specified column. Assuming TableB as agove, the next line yields the line after next.

%DATATBL{"TableB" Favorite="''" format="$Name" separator=" and "}% have/has no favorite fruits.

Mary have/has no favorite fruits.

As you see above, the value '' (two consecutive single quotes) means no value.

Select rows having neither of specified values

Instead of selecting rows having either of specified values, you can select rows having neither of specified values by preceding a value list with !.

%DATATBL{"TableB" Favorite="!banana, orange" format="$Name" separator=" and "}% like(s) neither banana or orange.

Mary and Chris like(s) neither banana or orange.

Processing sequence

1. Data source tables are read and fully indexed.
2. If row selection criteria is spcified, rows are selected accordingly from the data source table. Otherwise all rows are selected.
3. If sorting is specified, the selected rows are sorted in the ascending order by the specified column on the data source table.
4. If reverse="on" is specified, the order of the rows is reversed. This means that reverse="on" has meaning even without sorting.
5. If limit="LIMIT" is specified, rows exceeding the number are eliminated.
6. Output is generated using the rows yielded so far, the format parameter value, and separators

Output processing

When there is no match

If there are no matching rows, %DATATBL{...}% yields a zero length string by default. If ifnomatch="STRING" parameter is provided, STRING is yielded when there are no matching rows.

%DATATBL{"TableB" Favorite="mango" format="$Name" separator=" and " ifnomatch="Nobody"}% like(s) mango.

Nobody like(s) mango.

Iterate through values in a column

Assuming the same TableB as above, the next line is rendered into the line after next.

John likes %DATATBL{"TableB" Name="John" for="Favorite" format="$i" separator=" and "}%.

John likes apple and orange.

• By specifying a column with the "for" parameter, you can iterate through all values of the column.
• In the format parameter, an individual value is referred to by $i.

Different separators at different places

You may want to put different separators at different places rather than using the same separator at all places. For example, you want to show a list of things separated by comma but every 3 items, you want to put a new line. This is achieved as follows.

%DATATBL{"TableB" format="$Name" separator=", " separator3=",<br/>"}%

John, Jane, Joe,
Mary

After the third, sixth, ninth, ... item, ",
" is put instead of ", " as a separator.

What would happen if separator="SEP", separator2="SEP2" and separator3="SEP3" are specified? After sixth, 12th, 18th, ... item, SEP3 is put instead of SEP2 or SEP. In general, both separatorM and separatorN are applicable where M < N, separatorN is used.

Transliteration

You may want to transliterate characters in the output. The following parameters are for that.

• trfrom="SEARCH_LIST" (required)
• trto="REPLACE_LIST" (required)
• tropt="OPTIONS" (optional)
  • You can specify any combination of c, d, and s, which corresponds to the same options of the tr operation in Perl

For example, trfrom="A-Z" trto="a-z" translate all uppercase letters in a result to lowercase letters.

Referring to non-existent columns

In the format parameter, columns are referred to by $ followed by a column name. What happens if a non-existent column name is put after $? %DATADBL{...}% assumes that's for somebody else and leaves as it is. For example, assuming TableA mentioned above, the next line is rendered into the line after next.

%DATATBL{"TableA" rows="1" format="$BogusColumnName"}%

$BogusColumnName

You may mix %DATATBL{...}% with %SEARCH{...}% and/or %LDAP{...}%. So this behavior is handy.

But you may want to %DATATBL{...}% behave as if a column exists. For example, let's assume:

• You plan to introduce another column "Alias" to TableC mentioned above.
• Before doing so, you want to refer to that column and get a null string rather than $Alias.

You can get that behavior by using assumedcols="COLUMNS" parameter. The following line yields a null string.

%DATATBL{"TableC" ID="joe" assumedcols="Alias" format="$Alias"}%

More on data source table

Values are always comma separated

A comma in a table is always handled as a value separator. For example with TableB above, John's row's Favorite column value is a list consisted of "apple" and "orange". When the value is referred to by $Favorite in the format parameter, the values in the list are joined with ", ".

There is no way to use other symbols than comma to list values. This design decision is somewhat influenced by the fact that %EDITTABLE{...}% cannot use other symbols than comma to separate values.

Column names don't have to be unique

The following table means the same as TableB above.

Name	Favorite	Favorite
John	apple	orange
Jane	apple	banana
Joe	banana	strawberry
Mary
Chris	kiwi	papaya

There are cases where having multiple columns of the same name is preferable.

Multiple topics

So far, only one topic, TableA or TableB are specified. You can specify multiple topics by listing topics separated by comma. Each topic must have a table starting with a column defining row followed by the content rows.

Order of columns may be different among the tables. And a table may miss some columns used in another table.

Getting table data dynamically

So far table data is obtained from TWiki topics and static. You can get table data dynamically as follows. Let's assume http://foo/bar yields the following text.

| *ID* | *Name* |
| TK | Tokyo |
| HK | Hong Kong |
| LN | London |
| NY | New York |

Then, the next line is rendered into the line after next.

Hong Kong's ID is %DATATBL{"$percntINCLUDE{\"http://foo/bar\"}$percnt" Name="Hong Kong" format="$ID"}%.

Hong Kong's ID is HK.

This is like FormattedSearch. What you need to do is

1. Come up with a TWiki variable expression yielding table data
2. Escape the expression in the following mannter
   1. replace % starting or ending a variable with $percnt
   2. replace " with \".

How to specify a column whose name contains non-word characters

A column name in the format parameter and for row selection must start with a letter and consists only of word characters -- a word character is either an upper or lower case letter, digit, or underscore. What if a column name on a table contains non-word characters? You should be able to cope with such situations beccause you may not have strict control over a table you are referring to.

Each column name on a table is massaged as follows:

1. Non-word characters and the end of a column name are removed.
2. Each consecutive string of non-word characters is replaced with an underscore

The first step may look unnatural. This step is there to cope with cases where a non-word character is added to a column name on a table. E.g. tables on InterWikis have colons on their headings. With InterWiki tables, without the first step, the URL column would be referred to as URL_ rather than URL, which is not desirable.

As such, with the following table, the first column is referred to as Foo_Bar while the second column is referred to as Fizz_Buzz_.

Foo/Bar:	Fizz Buzz_
12345	abcde
67890	fghij

Transposed tables

You may want to use transpose tables on a topic where each table constitute a record:

| FIELD_NAME1 | VALUE1_OF_TABLE1 |
| FIELD_NAME2 | VALUE2_OF_TABLE1 |
| FIELD_NAME3 | VALUE3_OF_TABLE1 |

| FIELD_NAME1 | VALUE1_OF_TABLE2 |
| FIELD_NAME2 | VALUE2_OF_TABLE2 |
| FIELD_NAME3 | VALUE3_OF_TABLE2 |

This is desirable especially if you have text area fields; ordinary single table construct doesn't work well.

transposed="on" parameter is for this transposed table construct. Here are some points to worth mentioning.

• The third and following columns are ignored.
• %EDITCELL{...}% at the end of the second column is ignored. This is for EditTablePlugin.

Rather than having multiple transposed tables, you may have one contiguous table having multiple records. In that case, you can specify the split="SPLIT_PATTERN" parameter. Then, a line matching the SPLIT_PATTERN is regarded as a table separator.

Let's say you have the following table.

| FIELD_NAME1 | VALUE1_OF_TABLE1 |
| FIELD_NAME2 | VALUE2_OF_TABLE1 |
| FIELD_NAME3 | VALUE3_OF_TABLE1 |
| <hr/> ||
| FIELD_NAME1 | VALUE1_OF_TABLE2 |
| FIELD_NAME2 | VALUE2_OF_TABLE2 |
| FIELD_NAME3 | VALUE3_OF_TABLE2 |

By specifying split="<hr/>", the above table is regarded as two tables.

Expand variables in cells

You may have TWiki variables on tables and want to have %DATATBL{...}% expand those variables. You can achieve it by the evalcell="on" parameter.

Care should be taken if you refer to the same table topic list multiple times on a topic. Because a table topic list is read only once, at which point variabes in the cells are either expanded or not. A subsequent references the same table topic list uses the table data retrieved at the first reference.

Let's assume a topic has the following in that order.

• %DATATBL{"TableA,TableB" evalcell="on" ...}%
• %DATATBL{"TableA,TableB" evalcell="off" ...}%

Though the second reference to TableA,TableB has evalcell="off", it doesn't take effect because the table topic set is already read with evalcell="on".

Treat null value as no match

You may want to have %DATATBL{...}% resort to ifnomatch="STRING" when the selected row's specified column is null. The ignorenull="on" parameter is for that.

Let's say there is a topic TableC having the following table.

ID	Name
joe	Joe Schmoe
jane	Jane Doe
foobar

Then, the following lines are renedered into the one further below.

   * Set ID2NAME = %DATATBL{"TableC" ID="%DEFAULT%" format="$Name"
     ifnomatch="%DEFAULT%" ignorenull="on"}%
   * %ID2NAME{"joe"}%
   * %ID2NAME{"foobar"}%
   * %ID2NAME{"fizzbuzz"}%

• Set ID2NAME = %DEFAULT%
• Joe Schmoe
• foobar
• fizzbuzz

Other information

Efficiency

It's efficient because tables are read only once and fully indexed when read.

Debugging

By setting the DATATBL_DEBUG variable on on the WebPreferences page, you can get debug output on the debug log of TWiki. This is not so useful for ordinary users because they don't have access to server debug logs. So this is mainly for TWiki admins.

Plugin Info

Plugin Author:	TWiki:Main.HideyoImazu
Copyright:	© 2008-2016 TWiki:Main.HideyoImazu © 2016 TWiki:TWiki.TWikiContributor
License:	GPL (GNU General Public Licen:se)
Plugin Version:	2016-02-15

TWiki Dependency:	$TWiki::Plugins::VERSION 1.1
CPAN Dependencies:	none
Other Dependencies:	none
Perl Version:	5.008
Plugin Home:	http://TWiki.org/cgi-bin/view/Plugins/DataTablePlugin
Feedback:	http://TWiki.org/cgi-bin/view/Plugins/DataTablePluginDev
Appraisal:	http://TWiki.org/cgi-bin/view/Plugins/DataTablePluginAppraisal

Related Topics: VarSEARCH, TWikiPlugins, DeveloperDocumentationCategory, AdminDocumentationCategory, FormattedSearch