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

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. 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.

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.

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): 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?).

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;

• 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:

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.

• 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.

• 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, 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):

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:

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) 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:

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

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: every field and its syntax.

• Configure a data table and Cross-app subtables.

• Tips and examples.

• XL Docs messages and troubleshooting.