---
title: "Data tables (subtables)"
slug: "xl-docs-data-tables"
updated: 2026-10-09T18:08:35Z
published: 2026-10-09T18:08:35Z
canonical: "help.quickbase.com/xl-docs-data-tables"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://help.quickbase.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Data tables (subtables)

A data table (also called a subtable or sub-report) lists a set of records in the generated document: the line items on an invoice, the expense lines on an expense statement, the time sheets in an export file. Each data table is a query against a Quickbase table, usually a child table of the master record, and the records it returns are written row by row onto a sheet of the workbook.

Data tables can sort, filter, group, subtotal, and average the records, limit how many rows fit in an area of the template and decide what happens when there are more, interleave or stack several queries on one sheet, place them side by side, or put each record on its own sheet.

> [!NOTE]
> Note
> 
> The XL Docs Subtable Configurator is the easiest way to set up data tables: it has a filter builder, sort lists, and subtotal and grouping settings. See [Configure a data table](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-configurator-configure-a-data-table). This article explains the concepts and the field values the Configurator writes. The exact syntax of every field is in [Document Subtable Excel table reference](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-document-subtable-excel-table).

## How data tables are stored

Each data table is a record in the **Document Subtable Excel** table, related to a **Document Templates** record through its **Related Document Template** field. When XL Docs generates a document from the template, it runs every related data table in order of **Sheet**, then **Tab sub report order**, and writes each one's records into the workbook.

A data table record holds:

- **dbid**: the table ID of the table to query.
- **Description**: a name so you can find it.
- **clist**: which field goes in which column, and the row the list starts on.
- **qry**, **slist**, **slist Options**: which records, in what order, how many.
- **Is Data Grid?**, **Max Line items**, **Max Lines type**, **Skip Rows**, **Tab sub report order**, **Each Record as New Tab**, **Sheet**: how the rows are laid out.
- **stlist**, **avlist**, **grouplist**: subtotals, averages, and grouping.

Setting up a data table by hand uses table IDs and field IDs. To look them up, see [Find your Client ID, table IDs, and field IDs](https://help.quickbase.com/quickbase-extensions/docs/en/find-client-id-table-ids-field-ids).

To add one by hand, open the template record and add a Document Subtable Excel record (the **Add New XL Document Subtable** button opens a new one already related to the template). In the Configurator, open the template's **XL Docs Configurator**, go to the **Data Tables** tab, and select **Add New Data Table**.

## Columns and starting row (clist)

The data table's **clist** uses the same syntax as the template clist (see [clist syntax](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-build-a-template#clist)): `Cell[-N|D|I|H|F]:FID[:Height|Width]` entries separated by periods. In a grid, the cells give the column for each field and the row where the first record goes. The next records go in the rows below.

For example, `A12-D:6.B12:7.C12:20.D12-N:9` puts the first record's field 6 (as a date) in A12, field 7 in B12, field 20 in C12, and field 9 (as a number) in D12; the second record goes in row 13, the third in row 14, and so on. All the cells normally use the same row.

Images (`-I`) and HTML (`-H`) work in data tables too; each record's image or rich text goes in its own row. In the Configurator, the **Table/Fields** tab has the same **Field Mapping** grid as the template, with the help text *Enter an Excel cell (e.g. A1, B3) for each field you want to include. Multiple cells can be comma-separated.*

## Grids and single records (Is Data Grid?)

- **Is Data Grid? checked** (the default in the Configurator): every record the query returns is written, one per row. Use this for lists.
- **Is Data Grid? unchecked**: the values go into the clist cells once, so only one record's values appear. Use it when the query returns one record and you want its fields in specific cells, for example a header row, a footer with totals, or data on another sheet (see [Which records do I need?](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-document-templates-table#which-records-do-i-need)).

Sorting applies to both. Grouping, subtotals, averages, Max Line items, Max Lines type, Skip Rows, and Tab sub report order apply only to grids; in the Configurator, the **Subtotal/Group** tab only appears while Is Data Grid? is checked.

## Filter the records (qry)

The **qry** field decides which records are listed. It accepts:

- a Quickbase query, such as `{'3'.EX.'123'}` or `{'3'.GT.'0'}` (all records). See [Components of a query](https://help.quickbase.com/docs/components-of-a-query);
- the name of a report on the queried table, such as `Active Opportunities`;
- a report ID, such as `23`.

### Records related to the master record (RK)

Usually a data table lists only the records that belong to the master record, such as the line items of this invoice. To do that, pass a value from the master record into the query:

1. In the **template** clist, add a variable for the master field, typically the Record ID#: `RK:3`. An entry without a row number isn't written to a cell; it only holds the value.
2. In the data table's **qry**, refer to the variable in square brackets: `{'13'.EX.'[RK]'}` returns the records whose field 13 (the reference field to the master table) equals the master record's Record ID#.

You can define several variables, such as `RK:3.AA:7.BB:10`, and use any of them in any data table's query, on any sheet. Names are matched without regard to case. XL Docs wraps the value in single quotes for you, so `'[RK]'` and `[RK]` give the same result.

### Master ("parent") fields in the Configurator

In the Configurator's **Query** tab, build the filter with **Build a Filter**: choose *all* or *any* *of these conditions are true*, then a field, an operator, and what to compare against. To compare with the master record, choose *the value in the field (parent)* (or *the date in the field (parent)* for dates) and pick the master field, for example *Related Project* *is equal to* *the value in the field (parent)* *Record ID#*.

When you save, the Configurator stores the master field as a column-letter variable. For example, the query becomes `{'18'.EX.[A]}` and the Configurator adds `A:3` to the end of the template's clist. Don't remove these letter entries from the template clist; the data-table queries depend on them.

## Sort and limit the records (slist and slist Options)

**slist** lists the field IDs to sort by, separated by periods: `8.7` sorts by field 8, then by field 7. **slist Options** sets the direction and limits, with options separated by periods:

| **Option** | **Effect** |
| --- | --- |
| `sortorder-A` | Ascending (the default). |
| `sortorder-D` | Descending. |
| `sortorder-AD` | One letter per slist field: the first ascending, the second descending. |
| `skp-n` | Skip the first n records. |
| `num-n` | Return at most n records. |

Example: with slist `8.7` and slist Options `sortorder-AD.skp-20.num-10`, field 8 is sorted ascending and field 7 descending, the first 20 records are skipped, and at most 10 of the rest are listed. You can also write the direction next to each field in slist, such as `28:ASC.6:DESC`.

If the data table isn't grouped, the queried table's default sort is applied after your sort fields. In the Configurator, use **Sort & Record Limits** on the Query tab: move entries such as *Date (Asc)* from **Available Fields** to **Sort Fields**, and enter **Skip Records** and **Max Records**.

## Fit a fixed area (Max Line items and Max Lines type)

Many templates have a fixed area for line items, with totals, signatures, or notes below it. **Max Line items** is how many rows that area holds; **Max Lines type** says what to do when there are more records:

- **Insert New Rows**: when the area is full, XL Docs inserts enough rows below it for the remaining records, formatted like the rows above, so everything below the area (totals, signature block) moves down. To copy special formatting such as merged cells into the inserted rows, add `insertsf=y` to the button; see [Preserve special formatting](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-tips-and-examples#preserve-special-format).
- **Insert New Tab**: when the area is full, XL Docs copies the whole sheet to a new tab and continues there, Max Line items records per tab. The new tabs get the original name with a suffix, such as *Expense statement_1_1*. Use it with ungrouped data tables. See [Max Lines type = Insert New Tab](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-tips-and-examples#insert-new-tab).
- **blank**: records simply continue down the sheet.

For example, an expense statement has 10 rows for expense lines (rows 12 to 21), so **Max Line items** is 10. With **Insert New Rows**, a project with 12 expense lines gets two extra rows and the subtotal moves down; with **Insert New Tab**, the first 10 lines go on the first tab and the other 2 on a copy of it. Leave both fields blank if there's nothing below the list. In the Configurator they're on the **Data-Grid Settings** tab and must be set together.

## Leave or share rows (Skip Rows)

**Skip Rows** is the number of rows XL Docs leaves after each record. Use it to:

- leave blank rows between records of a single query, for example when each record takes a pre-formatted block of rows in the template;
- **interleave several queries.** For example, a time sheet shows three rows per employee, labeled DT (double time), T 1/2 (time and a half), and ST (straight time), from three separate queries. Start the three data tables' clists on consecutive rows (12, 13, and 14) and set Skip Rows to 2 on each, so each query writes every third row and the rows mix together.

## One sheet per record (Each Record as New Tab)

With **Each Record as New Tab** checked, each record the query returns goes on its own copy of the sheet, written to the clist cells. Use it with **Is Data Grid?** unchecked (the Configurator disables it while Is Data Grid? is checked).

## Choose the sheet (Sheet)

**Sheet** is required: the tab the data table writes to, counted from the left starting at 1. A template can have data tables on different sheets, several on the same sheet, or both. If Sheet is higher than the number of tabs in the template, generation stops with an error naming the data table. In the Configurator, this is **Excel Sheet #** on the Table/Fields tab.

## Stack data tables on one sheet (Tab sub report order)

When several grids go on the same sheet, one below the other, the earlier ones can return any number of rows, so you can't know where the later ones should start. **Tab sub report order** solves this: it's like a SQL union running down the sheet, except each data table can have its own columns and data types.

1. Give the first data table `01` and the next ones `02`, `03`, `04`, `05`, in the order they should appear. Blank or `01` starts a new stack.
2. Set each data table's clist row to keep the gap you want from the one before it. XL Docs starts each later data table at: **the last row used by the previous data table + (its clist row − the previous data table's clist row)**. The last row used is the previous data table's last record, or the end of its Max Line items area if it returned fewer records.

**No gap.** In the CSV export example in [Build a document template](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-build-a-template#csv), four data tables start in rows 1, 2, 3, and 4 with orders 01 to 04. The third returns 26 records (rows 3 to 28). Because the fourth's clist row is one more than the third's, the footer goes directly under the last record, in row 29.

**With a gap.** An expense statement lists expense lines in rows 12 to 23 (clist row 12, order `01`), followed by approval and office-use rows. A second grid with hours per employee (Name, # Reg Hours, # Hrs Time and a half, # Hrs Dbl Time) should start in column M, row 41, leaving rows 24 to 40 for the approval area. The first grid's last row is 23, and the second should start 18 rows later (41 − 23 = 18), so its clist row is 12 + 18 = 30: `M30:…`, `N30:…`, `O30:…`, `P30:…`, with order `02`. If the first grid grows, the second grid moves down with it and keeps the same gap.

In the Configurator, this is **Tab Sub Report Order** on the Data-Grid Settings tab.

## Side-by-side data tables

You can also place two grids next to each other on the same sheet, for example one in columns A to D and one in columns G to J. XL Docs recognizes data tables on the same sheet whose clists use none of the same column letters as side by side, so rows inserted for one don't push the other down. No setting is needed.

## Subtotals (stlist)

**stlist** adds a subtotal row under a grid. It has four parts separated by `|`:

```
C:Total|G.H.J|LightGray|SGT
```

1. `C:Total`: put the label *Total* in column C. XL Docs adds a count of the rows, so it reads *Total (13)*. Add `<<grouping>>` to include the group's name when the grid is grouped: `C:Total <<grouping>>` reads, for example, *Total Active (13)*, and `B:Sub Total for <<grouping>>` reads *Sub Total for O1 - New Lead (1)*.
2. `G.H.J`: total columns G, H, and J. Each total is a real formula, for example `=SUM(H2:H14)` in H15.
3. `LightGray` (optional): shade the subtotal row with a standard color name. Leave blank (or `None`) for no shading.
4. `SGT` (optional): suppress the grand total.

Leave unused optional parts empty, for example `C:Total|G.H.J||`.

The count in the label is the number of rows that have a value in the label column (column C in this example). A row with an empty cell in that column isn't counted, so map a field that always has a value, such as a name, to the label column.

For an ungrouped grid there's one subtotal row. For a grouped grid there's a subtotal row after each group and a grand total row at the end, labeled *Grand* plus your label and the total count, such as *Grand Sub Total (4)*, unless you add `SGT`.

Example output for `C:Total|G.H.J|LightGray|SGT` on an orders grid with 13 rows (rows 2 to 14):

| **Row** | **C (Customer Name)** | **G (# of Items)** | **H (Line Items Subtotal)** | **J (Order Total)** |
| --- | --- | --- | --- | --- |
| 2–14 | ACME Inc., Main Street Inc., Sales R Us, ABC Tech Services… | … | … | … |
| 15 (gray) | Total (13) | 916 | $90,611.00 (`=SUM(H2:H14)`) | $96,349.05 |

In the Configurator, use the **Subtotals** section of the Subtotal/Group tab: **Row Label** (for example *TOT* or *Sub-Total*), **Column for Label** (for example *A*), **Columns to Total**, **Row Shading**, and **Suppress Grand Totals**. The label, label column, and at least one column are needed together.

## Averages (avlist)

**avlist** adds an average row, in the same way:

```
C:Average|G.H.J|LightGreen
```

1. `C:Average`: the label *Average* in column C.
2. `G.H.J`: average columns G, H, and J. Each is an average formula rounded to two decimals, for example `=ROUND(AVERAGE(H2:H14),2)` in H15.
3. `LightGreen` (optional): shade the average row light green. Leave blank for none.

For a grouped grid there's an average row per group and an overall row labeled *Total* plus your label. If you use both stlist and avlist, the average row comes right after the subtotal row. In the Configurator, use the **Averages** section: **Row Label** (for example *AVG* or *Average*), **Column for Label**, **Columns to Average**, and **Row Shading**.

## Grouping (grouplist)

**grouplist** groups the rows of a grid by a field, with a heading row above each group. One level of grouping is supported.

```
A:28|MC
```

- `A`: the column where the group's name goes in the bold heading row.
- `28`: the field ID to group by. The records are sorted by this field first.
- `MC` (optional, after `|`): `M` merges the heading row's cells, `C` colors it light gray. Use either or both.

Combine grouping with stlist to get a subtotal per group. For example, customers grouped by status (grouplist `A:28|MC`), with a *Sub Total* label and the *# of Orders* column totaled:

| **Customer Name** | **# of Orders** |
| --- | --- |
| **O1 - New Lead** (heading, gray) |  |
| ACME Inc. | 4 |
| Sub Total (1) | 4 |
| **O4 - Proposal** (heading, gray) |  |
| ABC Tech Services | 3 |
| Main Street Inc. | 3 |
| Sub Total (2) | 6 |
| **A - Active Customer** (heading, gray) |  |
| Sales R Us | 3 |
| Sub Total (1) | 3 |
| Grand Sub Total (4) | 13 |

With the label `Sub Total for <<grouping>>` instead, each subtotal row names its group, for example *Sub Total for O1 - New Lead (1)* and *Sub Total for O4 - Proposal (2)*.

In the Configurator, use the **Data Grouping** section of the Subtotal/Group tab: **Grouping Field**, **Column for Label**, **Merge Set?** (*If checked, Merge Set will merge all of the columns where the grouping occurs into a single column.*), and **Colorize?** (*If checked, the grouping row(s) will be colored. Currently, this defaults to Light Gray.*).

## Worked example: expense lines on an expense statement

The *Employee Expense Template* (master table Projects, template clist ending in `RK:3`; see [Build a document template](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-build-a-template#worked-example-expense)) lists the project's expense sheets in rows 12 to 21 of sheet 1. Expense Sheets is a child table of Projects. The data table record:

| **Field** | **Value** | **Why** |
| --- | --- | --- |
| Description | Expense Sheet details | Name in the Data Tables list. |
| dbid | Expense Sheets table | The table to list. |
| clist | `A12-D:6.B12:7.C12:20.D12-N:9.E12-N:10.F12-N:11.G12-N:12.H12-N:13.I12-N:14.J12-N:15` | One row per expense sheet, starting at row 12. |
| qry | `{'18'.EX.'[RK]'}` | Field 18 (Related Project) equals the project's Record ID#. |
| slist | `6` | Sort by date. |
| Is Data Grid? | Checked | List every record. |
| Max Line items / Max Lines type | `10` / `Insert New Rows` | The template has 10 rows; more records push the subtotal down. |
| Sheet | `1` | The *Expense statement* tab. |

The clist maps the fields to the template's columns:

| **Cell** | **Field** | **Configurator Type** | **Template column** |
| --- | --- | --- | --- |
| A12 | 6 Date | Date | Date |
| B12 | 7 Account | Text | Account |
| C12 | 20 Employee Name (lookup) | Text | Employee |
| D12 | 9 Hotel Exp | Numeric | Hotel |
| E12 | 10 Transport Exp | Numeric | Transport |
| F12 | 11 Fuel Exp | Numeric | Fuel |
| G12 | 12 Meals Exp | Numeric | Meals |
| H12 | 13 Phone Exp | Numeric | Phone |
| I12 | 14 Entertainment Exp | Numeric | Entertain. |
| J12 | 15 | Numeric | Misc. |

The template's own formulas in the TOTAL column and the Subtotal, Advances, and TOTAL cells calculate from these values.

## Related articles

- [Document Subtable Excel table reference](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-document-subtable-excel-table): every field and its syntax.
- [Configure a data table](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-configurator-configure-a-data-table) and [Cross-app subtables](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-configurator-cross-app-subtables).
- [Tips and examples](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-tips-and-examples).
- [XL Docs messages and troubleshooting](https://help.quickbase.com/quickbase-extensions/docs/en/xl-docs-messages).