# JQDataTablesPlugin

JQuery based progressive enhancement of tables

## Overview

This plugin implements a widget to enhance normal HTML tables with extra navigation features such as sorting, filtering and paginating through datasets. The %DATATABLE macro is specifically tailored towards datasets created on the base of DataForms. It lets you easily compile an overview of data of that kind stored in a web as well as select specific records for further use in wiki apps.

JQDataTablesPlugin can be used in two distinct ways:

• by means of the %DATATABLE macro
• by means of HTML5 and JSON to enhance HTML tables directly

%DATATABLE takes a pointer to a DataForm definition and then creates a table based on the known formfields in that definition to create columns of a kind matching the formfield types. It then uses a connector fetch the data from the backend. There are several kinds of connectors available at the moment based on the additional functionality installed on your Foswiki:

• search: this one uses the normal native search feature of Foswiki also used by the %SEARCH macro
• dbcache: this one uses DBCachePlugin which is implementing a faster still native search, also available via its %DBQUERY macro
• solr: this connector uses the SolrPlugin which uses a Solr fulltext search engine integrated into Foswiki.

A default connector can be defined as required. Note however, that even though %DATATGABLE tries to hide the differences of available connectors behind the scene, connectors will behave differently, i.e. when it comes to filtering. Also, performance is vastly different with the search connector being the slowest one. It is strongly recommended that you at least install DBCachePlugin for acceptable performance in every-day usage.

## DATATABLE

Using a %DATATABLE you can query a set of topics and display their structured data in a dynamic table. Results are best when querying structured data attached to your topics as each formfield defined in the DataForm directly correlates to a column in the generated table. In this case you may specify the form definition in the form parameter and formfields in the columns parameter. The %DATATABLE will then use this information to properly display formfield values as well as sort columns along the technical representation. For example a date formfield will properly be displayed according to your locale's date format while sorting by date uses date values represented as epoch seconds.

Syntax: %DATATABLE{"<query>" parameter="..." parameter="..." ...}%

Parameter Description Default
<query>, query specify a search query to filter rows on the server-side; NOTE: the query language might vary depending on the connector being used, e.g. dbcache vs search vs solr
connector connector to be used to fetch data dynamically from the backend; possible values are search (using Foswiki's native %SEARCH), dbcache (needs Foswiki:Extensions/DBCachePlugin), solr (needs Foswiki:Extensions/SolrPlugin), additional connectors may be implemented by plugins; NOTE: for general-purpose search dbcache is the best choice defined in $Foswiki::cfg{JQDataTablesPlugin}{DefaultConnector} class additional css class to be added to the widget in addition to foswikiTable width width of table, e.g. 100% and the like web, webs web or list of webs to query for data current web form data form definition paging, pager switch on/off paging data; NOTE: this parameter is deactivated when scrolling is specifed as well (see docu) off pagelength, rows number of rows to be displayed when paging is enabled (see docu) 10 lengthmenu switches on a menu element to change the page length (see docu) [10, 25, 50, 100] scrolling, scroller switch on/off dynamic scrolling; data will be fetched from the backend as you are scrolling up and down; NOTE: this parameter disables paging as these two features are mutual exclusive off searching switch on/off the global search box (see docu) off searchmode defines how to search, either using one search box covering all columns (global), or using one search box per column (multi) global searchdelay delay before changes in a search box will cause new data to be fetched from the backend 400 sort specifies the column for initial ordering (see docu) first column reverse specifies the initial ordering direction off savestate switch on/off remembering the state the datatable was in when coming back to the page; this includes sorting, paging as well as filtering; state information is stored in the browser's session store, that is state information is specific to the browser tab; closing a browser tab will purge all state infos of datatables with it off info switch on/off the info about the number of rows in the set (see docu) off ordering switch on/off the ability to sort the table by clicking on a column header (see docu) on scrollx switch on/off horizontal scrolling (see docu) off scrolly specify vertical scrolling (see docu) off scrollcollapse switch on/off collapsing of the table height on small data sets (see docu) off columns comma-separated list of formfield names to be displayed; see the notes on special columns below all formfields of the DataForm specified by form rowgroup comma-separated list of columns that should be used to create groups of rows selecting switch on/off select extension selectproperty specifies the property of a row to be selected when selection is enabled topic selectmode possible values are os, single, multi, this specifies the way a selection is made (see docu) multi responsive switch on/off responsive extension fixedheader switch on/off fixed header extension hidecolumns comma-separated list of columns to hide from the output; while hidden column data is still fetched available for further processing such as grouping or styling a row rowclass javascript function that is supposed to return a css class when a row is rendered; the string returned is added as a class to the row elements of the table rowcss javascript function that is supposed to return a string or an object to apply inline css to all cells of a row autocolor comma-separted list of columns to auto-color based on the cell content; note this needs the JQAutoColorContrib to be installed and activated <field-name>_title a column title for a given field-name, e.g. ProjectState_title="State" name of the formfield as specified in columns <field-name>_width column width for a given field, e.g. ProjectID_width=5em ### Special column names In general column names specified in the columns parameter of the %DATATABLE parameter directly relate to a formfield of an attached DataForm. Values are displayed and sorted according the the type of the formfield. There are however a few column names that have a special meaning or trigger an additional behavior on data in this column: • index: this is an auto-generated column enumerating the rows in a table • Date, Changed, Modified, Created, info.date, createdate: these fields are all date fields and treated accordingly • Topic: alias for topic • TopicTitle: displays the TopicTitle of a topic linking to it • By: alias for author of recent topic revision • Creator, createauthor, info.author: author if the initial topic revision • publishauthor: author that published the topic, defaults to createauthor in case there is no explicit Author field in the form • publishdate: date when the publishauthor created the initial revision, defaults to createdate in case there is no explicut PublishDate field in the form • Worflow: name of the workflow assigned to a topic • A column name starting with a / (slash) will be excluded from any special treatment, i.e. /Author will not be interpreted as the author of the recent topic revision, but as the Author formfield of a DataForm (see below example). ## HTML5 A data table can also be applied to an already existing table enhancing it with additional features such as paging, client-side sorting, searching etc. This is done by wrapping your tables into a .jqDataTablesContainer div element and specify additional parameters using HTML5 data attributes %JQREQUIRE{"datatables"}% <div class="jqDataTablesContainer" data-paginate="true" data-searching="true" data-info="true"> | *Header* | *Header* | *Header* | *Header* | | Data | Data | Data | Data | | Data | Data | Data | Data | | Data | Data | Data | Data | | Data | Data | Data | Data | </div>  See http://datatables.net/reference/index for a full list of all options. ## Examples ### List of PackageForm topics Below example lists all topics in the System web that have the PackageForm attached to it: %STARTSECTION{"example3"}% %DATATABLE{ web="%SYSTEMWEB%" form="%SYSTEMWEB%.PackageForm" paging="on" searching="on" info="on" pagelength="10" lengthmenu="5, 10, 20, 50, 100" columns="index, Topic, Description, Version, Release, /Author" }% %ENDSECTION{"example3"}%  index Topic Description Version Release /Author ### Selecting topics %STARTSECTION{"example4"}% <form action="%SCRIPTURLPATH{"view"}%/%WEB%/%TOPIC%" method="post"> %DATATABLE{ web="System" class="foswikiFlatTable foswikiStripes" form="%SYSTEMWEB%.PackageForm" sort="TopicTitle" columns="index, TopicTitle, Description, /Author, Changed" TopicTitle_title="Title" info="on" paging="on" rows="10" selecting="on" selectmode="multi" selectproperty="Topic" selection="%URLPARAM{"Topic"}%" }% %BUTTON{"%TRANSLATE{"Submit"}%" type="submit" icon="tick"}% </form> %ENDSECTION{"example4"}%  Topic index TopicTitle Description /Author Changed Submit ### Coloring rows using inline CSS %STARTSECTION{"example5"}% %DATATABLE{ query="form~'FAQForm' AND topic!='FAQTemplate'" class="foswikiFlatTable foswikiTable foswikiStripes" web="%SYSTEMWEB%" paging="on" columns="Topic,TopicSummary,TopicClassification,Changed" TopicSummary_title="Summary" TopicClassification_title="Category" RelatedTopics_title="Related" rows="10" sort="TopicSummary" ordering="off" searching="on" rowcss="var css = { 'SupportQuestion': '#FFC107y', 'FrequentlyAskedQuestion': { 'background-color': '#4Caf40', 'color': '#fff' }, 'SupplementalDoc': '#f44336', 'AdminTopic': { 'background-color': '#b22222', 'color': '#fff' } }; return css[data.TopicClassification.raw];" }% %ENDSECTION{"example5"}%  Topic TopicSummary TopicClassification Changed ### Coloring rows using classes This example is not fully working. It depends on a DataForm definition for Invoice topics, that have at least formfields • Client: client that is invoiced • Total: amount of money • IssueDate: date when the invoice was sent to the client • DueDate: date until when the invoice has to be paid • State: the state the invoice is in: created, invoiced, overdue, reminded, disputed, paid, partial, withdrawn We will use two properties of invoices to color the table. Depeding in the property values css classes will be applied that we crawft styles for. The invoice column will be colored depending on the State of an invoice. A blue line will be drawn below which all invoices have to be paid, that is the table will be devided in two parts below and above the line where above the line invoices will be paid in the future and below the line invoices should have to be paid up to today. To make this work we create a small javascript fragment that returns the rowclass. In addition, any client cell will auto-colored using Foswiki:Extensions/JQAutoColorContrib. %STARTSECTION{"example6"}% %DATATABLE{ form="Invoice" class="foswikiFlatTable foswikiTable foswikiStripes" web="%SYSTEMWEB%" paging="on" columns="Topic,Client,Total,State,IssueDate,DueDate" rows="10" sort="DueDate" ordering="off" searching="on" autocolor="Client" rowclass=" var now = Date.now() / 1000; var cls = data.State.raw; if (data.DueDate.epoch <= now) { cls += ' past' }; return cls; " }% %JQREQUIRE{"autocolor"}% <style> .dataTable tr.past { border-top: 3px solid #3e89d3; } .dataTable tr.past:first-of-type, .dataTable tr.past + tr.past { border-top:0; } .dataTable tr.invoiced > td:nth-child(4) { background-color:#FFC107 !important; } .dataTable tr.reminded > td:nth-child(4) { background-color:#b22222 !important; color:#fff !important; } .dataTable tr.paid > td:nth-child(4) { background-color:#4Caf40 !important; color:#fff !important; } .dataTable tr.overdue > td:nth-child(4) { background-color:#f44336 !important; color:#fff !important; } </style> %ENDSECTION{"example6"}%  ### HTML5 simple example <div class="jqDataTablesContainer" data-paginate="true" data-searching="true" data-info="true"> | *Header* | *Header* | *Header* | *Header* | | Data | Data | Data | Data | | Data | Data | Data | Data | | Data | Data | Data | Data | | Data | Data | Data | Data | </div>  This example generates a table dynamically using a FormattedSearch: %STARTSECTION{"example1"}% <div class="jqDataTablesContainer" data-paginate="true" data-searching="true" data-info="true"> %SEARCH{ "1" type="query" topic="*Plugin" header="| *Name* | *Date* | *Author* |" format="|$topic | $date |$wikiusername |"
nonoise="on"
}%
</div>
%ENDSECTION{"example1"}%


Name Date Author
AttachContentPlugin 13 Jun 2016 - 10:03 UnknownUser
AutoTemplatePlugin 12 Nov 2019 - 08:10 UnknownUser
AutoViewTemplatePlugin 01 May 2016 - 03:35 UnknownUser
BibliographyPlugin 02 Jun 2016 - 09:28 MartinDiehl
BookmakerPlugin 02 Jun 2016 - 09:27 MartinDiehl
BreadCrumbsPlugin 15 Aug 2018 - 15:53 UnknownUser
CaptchaPlugin 12 Nov 2019 - 08:17 UnknownUser
ClassificationPlugin 02 May 2019 - 10:19 UnknownUser
CommentPlugin 22 Jan 2018 - 16:53 UnknownUser
CompareRevisionsAddonPlugin 01 Jun 2017 - 01:49 UnknownUser
ConfigurePlugin 22 Jan 2018 - 16:57 UnknownUser
DBCachePlugin 02 May 2019 - 09:16 UnknownUser
DiffPlugin 27 Nov 2019 - 16:29 UnknownUser
DigestPlugin 08 Mar 2016 - 16:04 UnknownUser
DocumentViewerPlugin 02 Sep 2016 - 11:19 UnknownUser
DpSyntaxHighlighterPlugin 02 Jun 2016 - 09:27 MartinDiehl
EditRowPlugin 02 Mar 2018 - 15:36 UnknownUser
EmptyPlugin 03 Feb 2016 - 17:22 UnknownUser
ExternalLinkPlugin 02 Jun 2016 - 09:27 MartinDiehl
FilterPlugin 25 Oct 2018 - 06:43 UnknownUser
FlexFormPlugin 12 Nov 2019 - 11:05 UnknownUser
FlexWebListPlugin 28 May 2018 - 13:33 UnknownUser
GraphvizPlugin 11 Jun 2018 - 11:58 UnknownUser
GridLayoutPlugin 12 Nov 2019 - 11:35 UnknownUser
HistoryPlugin 22 Jan 2018 - 17:10 UnknownUser
HomePagePlugin 02 Jun 2016 - 09:27 MartinDiehl
ImageGalleryPlugin 27 Jul 2017 - 10:14 UnknownUser
ImagePlugin 12 Nov 2019 - 11:51 UnknownUser
InterwikiPlugin 22 Jan 2018 - 17:14 UnknownUser
JQDataTablesPlugin 01 Jul 2019 - 10:49 UnknownUser
JQTablePlugin 26 Apr 2017 - 21:29 MartinDiehl
JQueryPlugin 18 Nov 2019 - 12:18 UnknownUser
LatexModePlugin 02 Jun 2016 - 09:27 MartinDiehl
LikePlugin 18 Nov 2019 - 13:42 UnknownUser
ListyPlugin 02 Jul 2019 - 08:08 UnknownUser
LocalTimePlugin 02 Jun 2016 - 09:27 MartinDiehl
MailerContribPlugin 22 Jan 2018 - 17:27 UnknownUser
MathJaxPlugin 27 Jan 2012 - 01:07 UnknownUser
MediaElementPlugin 18 Nov 2019 - 13:53 UnknownUser
MediaPlugin 02 Jun 2016 - 09:27 MartinDiehl
MetaCommentPlugin 26 Nov 2019 - 09:13 UnknownUser
MetaDataPlugin 30 Jan 2019 - 14:42 UnknownUser
MimeIconPlugin 18 Nov 2019 - 14:00 UnknownUser
MoreFormfieldsPlugin 10 Jan 2019 - 11:52 UnknownUser
MultiLingualPlugin 16 Jul 2018 - 14:18 UnknownUser
NatEditPlugin 06 Mar 2018 - 05:17 MartinDiehl
NatSkinPlugin 14 Feb 2019 - 11:56 UnknownUser
PageOptimizerPlugin 25 Sep 2017 - 11:53 UnknownUser
PreferencesPlugin 07 Jul 2015 - 01:09 UnknownUser
PubLinkFixupPlugin 01 Oct 2015 - 16:29 UnknownUser
RedDotPlugin 01 Nov 2016 - 14:41 MartinDiehl
RenderListPlugin 01 May 2016 - 13:15 UnknownUser
RenderPlugin 14 Feb 2019 - 09:27 UnknownUser
SecurityHeadersPlugin 17 Oct 2018 - 09:23 UnknownUser
SlideShowPlugin 13 Feb 2017 - 20:09 UnknownUser
SmiliesPlugin 02 Jun 2016 - 09:27 MartinDiehl
SolrPlugin 31 Jan 2019 - 11:10 UnknownUser
SpreadSheetPlugin 13 Feb 2017 - 20:11 UnknownUser
SubscribePlugin 29 Jan 2018 - 12:32 MartinDiehl
TWikiCompatibilityPlugin 07 Jul 2015 - 01:34 UnknownUser
TablePlugin 22 Jan 2018 - 17:57 UnknownUser
TagCloudPlugin 01 Apr 2016 - 10:32 UnknownUser
TinyMCEPlugin 13 Feb 2017 - 20:13 UnknownUser
TocPlugin 15 Apr 2011 - 22:05 UnknownUser
TopicInteractionPlugin 26 Nov 2018 - 17:49 UnknownUser
TopicTitlePlugin 14 Aug 2018 - 10:47 UnknownUser
TwistyPlugin 01 May 2016 - 13:37 UnknownUser
UpdatesPlugin 29 Jan 2018 - 12:32 MartinDiehl
WebLinkPlugin 02 Jun 2018 - 10:26 UnknownUser
WorkflowPlugin 01 Aug 2017 - 12:58 UnknownUser
WysiwygPlugin 22 Jan 2018 - 18:06 UnknownUser

### HTML5 client side sorting

JQDataTablesPlugin comes with additional sorting features for specific types of data:

• numeric
• string
• date (extended to be able to parse Foswiki's default date format)
• currency
• metrics (e.g. killo, mega, giga, tera, ...)

Click on the table headers to sort the columns according to their data type.

%STARTSECTION{"example2"}%
<div class="jqDataTablesContainer">
| *#* | *String* | *Date* | *Number* | *Currency* | *Size* |
| 3 | ActionTrackerPlugin | 27 Jan 2010 - 17:07 | 1 | 1,00 | 10KB |
| 1 | AntiWikiSpamPlugin | 03 Jan 2013 - 09:07 | 10 | 10,00 | 3GB |
| 2 | RenderListPlugin | 13 May 2012 - 02:59 | 0.01 | 1,01 | 100MB |
| 5 | CommentPlugin | 10 Apr 2011 - 23:39 | 100 | 0,10 | 2024kB |
| 4 | FindElsewherePlugin | 23 Dec 2012 - 17:06 | 20 | 100,- | 0.1kB |
| 6 | JsonRpcContrib |  | 0 | -100,- | 1024TB |
</div>
%ENDSECTION{"example2"}%


# String Date Number Currency Size
6 JsonRpcContrib   0 -100,- 1024TB
5 CommentPlugin 10 Apr 2011 - 23:39 100 0,10 2024kB
3 ActionTrackerPlugin 27 Jan 2010 - 17:07 1 1,00 10KB
2 RenderListPlugin 13 May 2012 - 02:59 0.01 1,01 100MB
1 AntiWikiSpamPlugin 03 Jan 2013 - 09:07 10 10,00 3GB
4 FindElsewherePlugin 23 Dec 2012 - 17:06 20 100,- 0.1kB

## Installation

You do not need to install anything in the browser to use this extension. The following instructions are for the administrator who installs the extension on the server.

Open configure, and open the "Extensions" section. "Extensions Operation and Maintenance" Tab -> "Install, Update or Remove extensions" Tab. Click the "Search for Extensions" button. Enter part of the extension name or description and press search. Select the desired extension(s) and click install. If an extension is already installed, it will not show up in the search results.

You can also install from the shell by running the extension installer as the web server user: (Be sure to run as the webserver user, not as root!)
cd /path/to/foswiki
perl tools/extension_installer <NameOfExtension> install


If you have any problems, or if the extension isn't available in configure, then you can still install manually from the command-line. See https://foswiki.org/Support/ManuallyInstallingExtensions for more help.

## Dependencies

NameVersionDescription
Foswiki::Plugins::JQueryPlugin >= 4.10Required
Foswiki::Contrib::JQMomentContrib >= 3.00Required
Foswiki::Plugins::MultiLingualPlugin >= 4.00Optional
Foswiki::Plugins::TopicTitlePlugin>1.00Required for Foswiki < 2.2

## Change History

 01 Jul 2019: updated to latest upstream version of Datatables; added parameters rowgroup, rowclass, rowcss, hidecolumns and autocolor 07 Jan 2019: added webs parameter to DATATABLE to query multiple webs at once 26 Nov 2018: add docu for newly added savestate parameter to DATATABLE; fixed rendering image columns in DBCacheConnector 01 Oct 2018: use JQMomentContrib for better parsing and sorting of date columns on the client side; make use of new TopicTitlePlugin; add support for MultiLingualPlugin 25 Sep 2017: fixed html5 data attributes 30 Aug 2017: disabled Author auto-column; added publishdate and publishauthor auto-columns 23 Jan 2017: don't report back an url parameter in the error message 02 Sep 2016: added default english translation files 13 Jun 2016: fixed parsing of dates that are already epoch seconds; improved default settings of table layout 25 May 2016: updated to latest version of DataTables 22 Apr 2016: implemented server-side grid widget 18 Mar 2014: remove console.log() leftover; improve sorting date columns 09 Nov 2013: implemented sorting for currency, and metrics 08 Nov 2013: make it work under {NoConflict}; enable jquery-ui theming by default now; created a non-goofy default look and feel to play nicely with a skin's table design; only add DataTables support to specific tables, not all; make it configurable with declarative metadata; compress and minify plugin assets; remove files not required by the plugin; clean up controls and css classes added by TablePlugin's; added type detector for foswiki date columns 18 Jan 2013: Initial version

### PackageFormedit

 Author Foswiki:Main.MichaelDaum, Foswiki:Main.SvenDowideit Version 5.00 Release 01 Jul 2019 Description JQuery based progressive enhancement of tables Repository https://github.com/foswiki/JQDataTablesPlugin Copyright 2012 SvenDowideit@fosiki.com, 2013-2019 Michael Daum http://michaeldaumconsulting.com License GPL (GNU General Public License) Home https://foswiki.org/Extensions/JQDataTablesPlugin Support https://foswiki.org/Support/JQDataTablesPlugin
Topic revision: r1 - 01 Jul 2019, UnknownUser

Copyright © by the contributing authors. All material on this site is the property of the contributing authors.
Ideas, requests, problems regarding DAMASK? Send feedback