Configure a data table

A data table (also called a subtable) puts a set of records from another table into the workbook—for example, an invoice's line items, or every expense for a project employee. You set one up in the XL Docs Subtable Configurator, and it is saved as a record in the Document Subtable Excel table, related to the template. For the fields themselves, see Document Subtable Excel table reference. For what data tables can do in a document, see Data tables (subtables).

Open the Subtable Configurator

Open it in any of these ways:

• In the XL Docs Configurator, on the Data Tables tab, select Add New Data Table to create one, or Edit on a row to change one. See Configure a template.

• On a Document Subtable Excel record in Quickbase, select the XL Docs Subtable Configurator button.

The screen has five tabs: Table/Fields, Images, Data-Grid Settings, Query, and Subtotal/Group.

How the tabs work

• Start on Table/Fields. For a new data table, Data-Grid Settings, Query, and Subtotal/Group are locked until you save the Table/Fields tab once—that save creates the Document Subtable Excel record. Clicking a locked tab shows Please save the Fields tab first to create a record.

• Subtotal/Group is shown only while Is Data Grid? is checked on the Data-Grid Settings tab.

• Save one tab at a time. Save saves only the tab you're on (Table/Fields and Images save together). Switching tabs with unsaved changes shows Please save or cancel changes before proceeding.

• Description is required for every save, on any tab. If it's blank, you see Description is required.

• Each successful save shows Settings saved successfully. Cancel undoes your changes on the tab since you opened it or last saved.

Note

The field lists on the Query and Subtotal/Group tabs are built when the screen opens. After you save a new data table for the first time—or after you change its Table—close the pop-up and open the data table again with Edit before you set up the Query and Subtotal/Group tabs.

Table/Fields tab

Subtable Settings

The data table is linked to the template automatically (Related Document Template).

Field Mapping

Enter an Excel cell (e.g. A1, B3) for each field you want to include. Multiple cells can be comma-separated.

The grid works like the one on the master screen—Field ID, Field Name, Reference Type, Type (Text, Numeric, HTML Text, Image, Date), and Cell Position. See Field Mapping.

For a grid of rows, enter the cells of the first row. XL Docs writes the first record there and each following record on the next row down. For example, to fill an expense grid starting at row 12: Date in A12, Account in B12, Employee Name in C12, Hotel Exp in D12, Transport Exp in E12, and so on across the row.

The mapping is saved to clist—for example A12-D:6.B12:7.C12:20.D12-N:9.E12-N:10.

Images tab

Works the same as the master screen's Images tab: it lists the fields whose Type is Image and that have a Cell Position, with Width (px or Auto) and Height (px or Auto). See Images tab. It's saved with the Table/Fields tab.

Data-Grid Settings tab

The General Settings section controls how the records are laid out in the workbook.

When Is Data Grid? is checked, Max Line Items, Max Lines Type, Skip Rows, and Tab Sub Report Order are available and Each Record as New Tab is cleared and unavailable. When it's unchecked, those four are cleared and unavailable and Each Record as New Tab is available.

Max Line Items and Max Lines Type go together: set both or neither. Otherwise saving shows Max Line Items and Max Lines Type must both be set, or both be empty.

Two data tables on the same sheet whose cells use different columns—side by side—are handled automatically: rows inserted for one don't push the other down.

Query tab

The Query tab decides which records the data table includes and in what order.

Build a Filter

The filter builder works like a Quickbase report filter.

1. At the top, choose all or any and read it as "all of these conditions are true" or "any of these conditions are true".

2. For each condition, choose a field (Select a field), an operator, and what to compare against.

3. Use the icon buttons beside a condition to add a condition, remove it, add a nested set of conditions, or move it out of a set. Sets can be nested up to three levels deep, each with its own all/any.

4. Removing a whole set asks Are you sure you want to delete this entire set of filters?—select Delete or Cancel.

The operators depend on the field's type:

What you can compare against:

• Most fields: the value (type it), the value in the field (another field on the same record), or the value in the field (parent) (a field on the master record—see below).

• Date fields: the date, day(s) in the past, yesterday, today, tomorrow, day(s) in the future, the date in the field, or the date in the field (parent). With is during or is not during: the previous, the current, or the next, a number, and a unit—day, week, month, quarter, fiscal quarter, year, or fiscal year.

• Checkbox fields: checked, or the value in the field.

Parent fields in queries

Most data tables show the records that belong to the master record—the expenses for this employee, the line items for this invoice. To do that, compare the child table's relationship field to a field on the master record with the value in the field (parent). For example:

all of these conditions are true: Project Employee is equal to the value in the field (parent) Record ID#.

When you save, the Configurator gives each parent field a column-letter variable and writes the query with it, for example {'18'.EX.[A]}. It also adds the alias to the template's clist (for example A:3), so saving the Query tab can update the template record too. When you open the data table again, the builder shows the field names, not the letters. The parent fields come from the template's master table (Master table dbid).

The filter is saved to qry in Quickbase query syntax: each condition is {'field ID'.OPERATOR.'value'}, joined with AND or OR, and nested sets are wrapped in parentheses. Relative dates are saved as, for example, 7 days ago.

Sort & Record Limits

• Available Fields lists every field twice, as Name (Asc) and Name (Desc). Move the ones you want into Sort Fields with the transfer buttons on the left, or drag them. The first field in Sort Fields is the main sort; use the up and down buttons on the right to reorder. You can't add the same field twice—that shows You cannot sort by the same field twice.

• Skip Records—skip this many records from the start of the results.

• Max Records—return at most this many records. Leave it empty for no upper limit.

Without sort fields, the records are in the table's default sort order. The sort fields are saved to slist as field IDs separated by periods (for example 6.7). The directions and limits are saved to slist Options as sortorder- with one letter per sort field (A or D), then skp-n and num-n—for example sortorder-AD.skp-5.num-20.

Subtotal/Group tab

Available when Is Data Grid? is checked. It adds total rows, groups records under headings, and adds average rows. Each section is optional: to turn one off, leave all its boxes blank.

Subtotals

Adds a total row below the grid (and below each group, if you group), with an Excel =SUM() under each column you choose.

Saved to stlist as label column:label|columns|color, with |SGT added when grand totals are suppressed—for example A:TOT|E.F.G|#C0C0C0|SGT. A label, a label column, and at least one column are all needed; otherwise saving shows Subtotal settings need a label, a label column (e.g. "A"), and at least one column selected -- or leave all three blank to disable subtotals.

Data Grouping

Groups the records by a field, with a heading row for each group—for example, all tasks under Cleaning and touch ups. Records are sorted by the grouping field first. One grouping per data table.

Saved to grouplist as column:field ID, followed by |M, |C, or |MC for Merge Set and Colorize—for example A:28|MC.

Averages

Adds an average row, like the total row, with the average of each column you choose (rounded to two decimals).

Saved to avlist in the same format as subtotals, without |SGT. Like subtotals, it needs a label, a label column, and at least one column, or all three blank.

For other messages, see XL Docs Configurator messages and troubleshooting.