Back to all How-tos

Creating multi-level dynamic tables

Up to Connect 1.7, nested dynamic tables had to be built entirely through scripts. This changed with the release of Connect 1.7. The software can now expand nested dynamic tables by itself, if the correct HTML attributes are used; scripts are only needed to add the data. This how-to shows how to use this feature.

NOTE: As of OL Connect version 2020.1, nested dynamic tables can be made with the Dynamic Table wizard, without any scripting.

The HTML

Let’s assume that you have a Data Model which consists of three levels: InstrumentClass  > Sector > Holding.
In order to get a dynamic table with nested dynamic tables, there should be a base row in the HTML table for each level in the detail data. All base rows must be direct children of the <tbody> element.
The data-repeat attribute on these base rows tells the software to repeat the row for each record in the specified detail table. The value of this attribute is the path of the (sub-)detail table in the data, from the root detail table down to the respective sub-detail level; level names are separated with a period.
Here’s an example of such a table in HTML:

<table id="table" data-detail="InstrumentClass">
<thead>
  <tr>
    <td>ID</td>
    <td>Description</td>
    <td>Quantity</td>
    <td>Value</td>
  </tr>
</thead>
<tbody>
  <tr data-repeat="InstrumentClass" class="firstLevel">
    <td></td>
    <td>@instrumentclass@</td>
    <td></td>
    <td></td>
  </tr>
  <tr data-repeat="InstrumentClass.Sector" class="secondLevel">
    <td></td>
    <td>@sector@</td>
    <td></td>
    <td></td>
  </tr>
  <tr data-repeat="InstrumentClass.Sector.Holding" class="thirdLevel">
    <td>@code@</td>
    <td>@r1r2@</td>
    <td>@quantity@</td>
    <td>@value@</td>
  </tr>
</tbody>
</table>

A few side notes:

  • In this example, empty cells are used to align the content of columns.
  • Classes (e.g. firstLevel, secondLevel, thirdLevel) allow to style the rows via CSS.

The scripts

The software will automatically expand the HTML table with as many rows as there are records in a detail table, but it will not add content to the fields; this requires scripting. The scripts should use the data-repeat attribute and value as selector.

First level

The script that fills the first level in the table uses the standard dynamic table technique. It combines a selector with a placeholder text and iterates over the result set using the each() command. In this loop the placeholders are replaced with the value of the respective data field, which is retrieved from the corresponding row in the detail table by index.

Selector: [data-repeat=’InstrumentClass’]
Find text: @instrumentclass@

Script:
results.each(function(index) {
  var field = record.tables["InstrumentClass"][index].fields["description"];
  this.html(field);
});

Sub-levels

Remaining levels require more advanced scripting, with a sub-loop that retrieves data from the corresponding sub-levels. Here is an example of a script that fills the rows of a second-level dynamic table. Subsequent levels could be filled using a similar script.

Selector: [data-repeat=’InstrumentClass.Sector’]
Find text: @sector@

Script:

results.each(function(index) {
  var tables = this.parent().attr('data-repeat');
  var tablesArr = tables.split('.');
  var LEV1 = tablesArr[0];
  var LEV2 = tablesArr[1];
  var field, result = "";
  var index0 = 0;
  var index1 = index;
  var threshold = record.tables[tablesArr[0]].length;

  while (index1 >= record.tables[LEV1][index0].tables[LEV2].length) {
    index1 -= record.tables[LEV1][index0].tables[LEV2].length;
    index0++;
    if (index0 >= threshold)
    return;
  }
  field = record.tables[LEV1][index0].tables[LEV2][index1].fields["description"];
  this.html(field);
});

Populating multiple cells with one script

The scripts above use a selector combined with a text to find. Text scripts, however, are notoriously slow. A faster alternative is a script that populates all cells in a single row, like in the following example. The base row is first converted into a string. Then, the replace() command is used to replace the placeholders. The result is added to the table.

Selector: [data-repeat=’InstrumentClass.Sector.Holding’]

Script:

results.each(function(index) {
  var tables = this.attr('data-repeat');
  var tablesArr = tables.split('.');
  var LEV1 = tablesArr[0];
  var LEV2 = tablesArr[1];
  var LEV3 = tablesArr[2];
  var field, result = "";
  var index0 = 0;
  var index1 = 0;
  var index2 = index;
  var threshold0 = record.tables[LEV1].length;
  var threshold1 = record.tables[LEV1][index0].tables[LEV2].length;

  if(record.tables[LEV1][index0].tables[LEV2][index1].tables[LEV3]){
    while (index2 >= record.tables[LEV1][index0].tables[LEV2][index1].tables[LEV3].length) {
      index2 -= record.tables[LEV1][index0].tables[LEV2][index1].tables[LEV3].length;
      index1++;
      if (index1 >= threshold1){
        index0++;
        if (index0 >= threshold0)
        return;
        index1 = 0;
        threshold1 = record.tables[LEV1][index0].tables[LEV2].length;
      }
    }
    var baseRow = this.html().toString();
    var data = record.tables[LEV1][index0].tables[LEV2][index1].tables[LEV3][index2];
    baseRow = baseRow.replace("@code@",  data.fields["code"]);
    baseRow = baseRow.replace("@r1r2@",  data.fields["r1r2"]);
    baseRow = baseRow.replace("@quantity@",  data.fields["holding"]);
    baseRow = baseRow.replace("@value@",  data.fields["value"]);
    this.html(baseRow);
  }
});

Repeating headers

In a nested dynamic table that runs across multiple pages, you may want higher level rows to be repeated on the next page, when a page break occurs at a certain level. This can be achieved by adding the data-show-row attribute to the row. If the value of this attribute is set to all, the respective row is repeated on all pages its sub-levels run on.
Example:

<tr class="firstLevel" data-show-row="all" data-repeat="InstrumentClass">
  …
</tr>

Leave a Reply

Your email address will not be published. Required fields are marked *