Document Subtable Excel table reference

Related Document Template

Numeric (reference to Document Templates)

The Record ID# of the template this data table belongs to. XL Docs loads every Document Subtable Excel record related to the template being generated.

Configurator label: not shown; the Configurator fills it in from the template you opened it from.

17

Description

Text

A name for this data table so you can recognize it, for example in the Configurator's Data Tables list.

Configurator label: Description (Table/Fields tab, required). Saving without it shows Description is required.

Expense Sheet details

dbid

Text (required)

The table ID (DBID) of the table to query, usually a child table of the master table. It can be a table in another app, as long as that app is authorized for XL Docs with the same user token; see Cross-app subtables.

Configurator label: Table (Table/Fields tab, required). Tooltip: Selects the table to grab fields and records from for the data-table.

The DBID from the table's URL. See Find your Client ID, table IDs, and field IDs.

clist

Text (required)

Where each field of the queried records goes. Same syntax as the template clist:

Cell[-N|D|I|H|F]:FID[:Height|Width]

entries separated by periods. In a grid, the row number in each cell is the row where the first record goes; the following records go in the rows below. Suffixes: -N number, -D date, -I image (with optional Height|Width in pixels), -H HTML, -F formula. See clist syntax.

It must contain at least one entry. An empty clist stops generation with Clist was empty on related subtable #{RecordID} - please enter in at least one field into the clist for this subtable, or unrelate it from the Document Template.

Configurator label: the Field Mapping grid (Table/Fields tab, Cell Position and Type columns) and the Images tab.

A12-D:6.B12:7.C12:20.D12-N:9.E12-N:10

slist

Text

The sort list: the field IDs to sort the records by, separated by periods. The records are sorted by the first field, then by the second, and so on. Ascending is the default; set the direction in slist Options, or add :ASC or :DESC after a field ID.

If the data table isn't grouped, the queried table's own default sort is applied after your sort fields. If it's grouped, the grouping field is sorted first.

Configurator label: Sort Fields (Query tab, Sort & Record Limits). Pick entries such as Date (Asc) or Date (Desc) from Available Fields. A field can only be used once (You cannot sort by the same field twice.).

8.7 sorts by field 8, then field 7. 28:ASC.6:DESC

slist Options

Text

Options for the sort and for how many records are returned. Separate options with periods. Leave blank for ascending order and all records.

• sortorder-A: ascending.

• sortorder-D: descending (applies to every slist field that has no direction of its own).

• sortorder-AD: one letter per slist field, in order. AD sorts the first field ascending and the second descending.

• skp-n: skip the first n records of the sorted results.

• num-n: return at most n records.

Sometimes called options on older forms.

Configurator label: built from the (Asc)/(Desc) choices in Sort Fields, plus Skip Records and Max Records (Query tab). A 0 in Skip Records or Max Records counts as blank.

With slist 8.7: sortorder-AD.skp-20.num-10 sorts field 8 ascending and field 7 descending, skips the first 20 records, and returns at most 10.

qry

Text (required)

Which records to include. Three formats are accepted:

• A Quickbase query, such as {'3'.EX.'123'}. For the syntax, see Components of a query.

• A report name from the queried table, such as Active Opportunities.

• A report ID, such as 23.

To find records related to the master record, compare against a variable from the template clist. With RK:3 in the template clist, {'18'.EX.'[RK]'} returns the records whose field 18 (for example Related Project) equals the master record's Record ID#. Variable names are matched without regard to case. XL Docs puts single quotes around the value, so '[RK]' and [RK] both work. A value that is itself a query (contains { and }) is inserted without quotes.

Configurator label: Build a Filter (Query tab). Choosing the value in the field (parent) compares against a master-table field. The Configurator stores it as a column-letter variable such as [A] and adds the matching entry (such as A:12) to the template clist.

{'18'.EX.'[RK]'}

stlist

Text

Subtotal row (grids only). Four parts separated by |:

LabelColumn:Label|Columns|Color|SGT

1. Label: the column and text of the label, for example C:Total. XL Docs adds the row count, so the label reads Total (13). The count is the number of cells in the label column (column C in this example) that aren't empty, so make sure every record fills that column. Include <<grouping>> to add the group's name when grouplist is used: C:Total <<grouping>> reads Total Active (13).

2. Columns to total, separated by periods, for example G.H.J. Each gets a =SUM() formula under the column, for example =SUM(H2:H14) in H15.

3. Color (optional) to shade the subtotal row, for example LightGray (a standard color name). Blank or None means no shading.

4. SGT (optional) suppresses the grand total.

When the data table is grouped, there's a subtotal row per group plus a grand total row, labeled Grand plus your label and the total count, for example Grand Sub Total (4). Leave unused optional parts blank: C:Total|G.H.J||. A value with no | is ignored.

The field help reads: FORMAT: Column and text to Display | "." delimited list of columns to total | Color shading for the total row.

Configurator label: Subtotals section (Subtotal/Group tab): Row Label, Column for Label, Columns to Total, Row Shading, Suppress Grand Totals. The Configurator saves the shading as a hex color code.

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

avlist

Text

Average row (grids only). Three parts separated by |:

LabelColumn:Label|Columns|Color

1. Label: column and text, for example C:Average puts Average in column C.

2. Columns to average, separated by periods, for example G.H.J. Each gets an average formula under the column, rounded to two decimals, for example =ROUND(AVERAGE(H2:H14),2) in H15.

3. Color (optional) to shade the average row, for example LightGreen shades it light green. Leave blank for no shading.

When grouped, there's an average row per group and an overall row labeled Total plus your label.

Configurator label: Averages section (Subtotal/Group tab): Row Label, Column for Label, Columns to Average, Row Shading.

C:Average|G.H.J|LightGreen

grouplist

Text

Groups the rows by a field (grids only; one level of grouping).

Column:FID|Flags

• Column: where the group's name goes in the bold group heading row.

• FID: the field ID to group by. The records are sorted by this field first.

• Flags (optional, after |): M merges the heading row's cells, C colors the heading row (light gray), MC does both.

Combine with stlist to get a subtotal per group.

Configurator label: Data Grouping section (Subtotal/Group tab): Grouping Field, Column for Label, Merge Set?, Colorize?.

A:28|MC groups by field 28, heading in column A, merged and colored.

Is Data Grid?

Checkbox

Checked: every record the query returns is written, one row per record, down the sheet from the clist's row. Grouping, subtotals, averages, Max Line items, Max Lines type, Skip Rows, and Tab sub report order apply only to grids.

Unchecked: the values are written to the clist cells once, so only one record's values appear. Use this for a query that returns a single record (for example a header or footer row), or with Each Record as New Tab.

Configurator label: Is Data Grid? (Data-Grid Settings tab; checked by default for a new data table; older versions labeled it Is this data-table a grid?). Tooltip: Enables XL Docs to place multiple line-items of data based on the Query. The Subtotal/Group tab appears only while it's checked.

Checked

Each Record as New Tab

Checkbox

When checked, each record the query returns goes on its own copy of the sheet. Use it only when Is Data Grid? is unchecked.

The installation wizard may label this field EachRecordasNewTab; it's the same field.

Configurator label: Each Record as New Tab (Data-Grid Settings tab; older versions: Each Record as New Sheet). It's disabled while Is Data Grid? is checked. Tooltip: Every record returned by the query will be put onto its own new sheet. Only usable when 'Is this data-table a grid' is not checked.

Unchecked

Max Line items

Numeric

How many rows the data table may fill on the sheet, for example when the template has a fixed area of 10 line-item rows. Used with Max Lines type, which says what happens when there are more records. Blank means no limit.

Configurator label: Max Line Items (Data-Grid Settings tab). Tooltip: How many line items should be placed onto the sheet? Leave empty for no upper bound. The Configurator requires Max Line Items and Max Lines Type to be set together: Max Line Items and Max Lines Type must both be set, or both be empty.

10

Max Lines type

Text (multiple choice)

What to do when the records exceed Max Line items:

• Insert New Rows: insert enough new rows below the area for the remaining records, formatted like the rows above, and keep going down the sheet. Everything below the area moves down. To copy special formatting such as merged cells into the new rows, add insertsf=y to the button (see Formula URL parameters).

• Insert New Tab: copy the whole sheet to a new tab and continue there, Max Line items records per tab. The new tabs are named after the original, such as Expense statement_1_1. Use it with ungrouped data tables.

• blank: rows continue down the sheet without inserting rows.

On older forms this field is labeled If Max Lines > exceeded then do this?.

Configurator label: Max Lines Type (Data-Grid Settings tab). Tooltip: What should happen when the sheet reaches the above Max Line Items?

Insert New Rows

Skip Rows

Numeric

The number of rows to leave after each record in a grid. Default 0 (records on consecutive rows).

Use it to leave blank rows between records, or to interleave several data tables: for three queries whose records should alternate row by row, set Skip Rows to 2 on each and start their clists on three consecutive rows (for example rows 12, 13, and 14).

Configurator label: Skip Rows (Data-Grid Settings tab). Tooltip: For each line item placed onto the sheet, how many rows should be skipped before we place the next line item?

2

Sheet

Numeric (required)

The sheet (tab) this data table is written on, counted from the left starting at 1. If the number is higher than the number of sheets in the template, generation stops with Subtable #{RecordID} references Sheet {Sheet}, but the template only has {n} sheet(s) - please fix the Sheet value on this subtable, or add the missing sheet to the template.

Data tables are processed in order of Sheet, then Tab sub report order. When the button uses sameworkbook=y, every data table goes on its record's own tab.

Configurator label: Excel Sheet # (Table/Fields tab, required). Tooltip: Sheet that the data from this data-table will be placed on.

1

Tab sub report order

Text (multiple choice: blank, 01–05)

Stacks several grid data tables on the same sheet, one below the other, when the earlier ones return a varying number of rows. Think of it like a SQL union running down the sheet, except each data table has its own columns.

Give the first data table 01 and the next ones 02, 03, and so on. Blank or 01 starts a new stack. Each later data table starts below the one before it, keeping the gap you set between their clist rows: its first row = 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. Details and examples: Data tables (subtables).

Configurator label: Tab Sub Report Order (Data-Grid Settings tab). Tooltip: Order position in which this data-table will be loaded onto the sheet with respect to other data-tables on the same sheet. For Example, 01 will load before 02.

02

Link to table

Formula - URL

Created by the installation wizard: a link that opens the queried table. Not used for generation.

Link to table

XL Docs Subtable Configurator

Formula - Text with "Allow some HTML tags" (button)

The button that opens this data table in the XL Docs Subtable Configurator. See Configurator overview and installation.

Button labeled XL Docs Subtable Configurator

Related Document Template

17

Description

Expense Sheet details

dbid

The Expense Sheets table's DBID

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

slist

6

qry

{'18'.EX.'[RK]'} (template clist includes RK:3)

Max Line items / Max Lines type

10 / Insert New Rows

Is Data Grid?

Checked

Each Record as New Tab

Unchecked

Sheet

1

slist Options, stlist, avlist, grouplist, Skip Rows, Tab sub report order

(blank)