Tags:
plugin1Add my vote for this tag structured_wiki1Add my vote for this tag tables1Add my vote for this tag create new tag
, view all tags

Data Table Plugin

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#Nested_Search. 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
2016-02-15: Having non-word characters at the end of a colum name ignored
2016-01-26: Documentation typo fix
2016-01-21: TWikibug:Item7718: DataTablePlugin to handle column names containing non-word characters
2015-10-09: TWikibug:Item7694: DataTablePlugin regards "0" as ""
2015-06-23: sort, sortn, revers parameters introduced
2014-03-14: Initial release
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

Topic attachments
I Attachment History Action Size Date Who Comment
Unknown file formatmd5 DataTablePlugin.md5 r8 r7 r6 r5 r4 manage 0.2 K 2016-02-16 - 01:35 HideyoImazu  
Compressed Zip archivetgz DataTablePlugin.tgz r8 r7 r6 r5 r4 manage 12.7 K 2016-02-16 - 01:35 HideyoImazu  
Compressed Zip archivezip DataTablePlugin.zip r8 r7 r6 r5 r4 manage 16.0 K 2016-02-16 - 01:34 HideyoImazu  
Unknown file formatEXT DataTablePlugin_installer r2 r1 manage 3.5 K 2016-02-16 - 01:35 HideyoImazu  
Edit | Attach | Watch | Print version | History: r5 < r4 < r3 < r2 < r1 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r5 - 2016-02-16 - HideyoImazu
 
  • Learn about TWiki  
  • Download TWiki
This site is powered by the TWiki collaboration platform Powered by Perl Hosted by OICcam.com Ideas, requests, problems regarding TWiki? Send feedback. Ask community in the support forum.
Copyright © 1999-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.