Document Templates table reference

Name

Text (required)

The name of the template, for example the kind of document it produces. The field help reads Enter a Name for this Document Template.

Configurator label: Template Name (Fields tab, Template Settings section). The Configurator won't save without it and shows the error Name is required.

Employee Expense Template

Excel Template File

File attachment

The Excel workbook that XL Docs fills in. It must be saved as .xls, .xlsx, or .xlsm. Any other file type, including Word files, is rejected with the error This version of Excel Generator only supports .xls, .xlsx, or .xlsm template files.

The workbook can contain static text, logos, images, formatting, formulas, charts, and pivot tables. XL Docs writes values into the cells you map and leaves the rest of the workbook as it is. By default, the generated document has the same file type as this template. A record with no file attached fails with The template record exists but there is no file associated to it. Please make sure that a template file is attached to the template record.

On some installs the installation wizard labels this field Template File. It's the same field.

Not shown in the Configurator. Upload the file on the Quickbase record.

Employee Expenses v4.xlsx

clist

Text

The column list: which master-record fields go into which cells. The master record is the record the XL Docs button runs on.

Format: one entry per cell, entries separated by a period (.). Each entry is

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

• Cell is an Excel cell reference such as A1, B6, or M12.

• FID is the field ID of the Quickbase field whose value goes in that cell (see Find your Client ID, table IDs, and field IDs).

• An optional type suffix after the cell tells XL Docs how to write the value:

  • no suffix: plain text value, for example B6:6.

  • -N numeric: written as a number so it right-justifies and follows the cell's number format. A leading $ is removed. If the cell's format is a percentage, the value is divided by 100. Example C22-N:6.

  • -D date: written as a date. Use it when the template cell isn't already formatted as a date. Example A12-D:6.

  • -I image: places an image, optionally sized as Height|Width in pixels. Example E22-I:21:20|20.

  • -H HTML: converts rich text or an HTML table from the field. Example A5-H:12.

  • -F formula: if the field's value contains ( and ), it's written as an Excel formula; otherwise as text.

• Variables: an entry whose cell part has no row number, such as RK:3, AA:7, or A:12, isn't written to a cell. Instead it makes the field's value available to data-table queries as [RK], [AA], or [A]. Use a two-character name of your own (such as RK for "related key", usually the Record ID#). The Configurator adds column-letter variables such as A:12 automatically when a data-table query compares against a master ("parent") field.

To send one field to two cells, add two entries, for example B6:6.E10:6. Full syntax and examples are in Build a document template.

Leave the clist empty if all of the template's data comes from data tables.

Configurator label: the Field Mapping grid on the Fields tab (Cell Position and Type columns) and the Images tab (Width (px or Auto), Height (px or Auto)).

B6:6.B7:7.B8:8.K6:104.K7:105.M12-I:275:200|200.A38-H:276.RK:3

Excel Sheet #

Numeric

The sheet (tab) of the workbook where the clist values are written. The number is positional: 1 is the first tab from the left, 2 the second, and so on, whatever the tabs are named. If it's blank, XL Docs uses the first sheet.

Not required: a workbook that only needs rows from data tables doesn't use it. Each data table has its own Sheet setting.

Configurator label: Excel Sheet # (Fields tab, Template Settings; whole numbers from 1).

1

PDF Pages

Text

Which sheets of the generated workbook go into the PDF. Used only when the button creates a PDF (docfmt=pdf; see Formula URL parameters). It's ignored for Excel and CSV output.

Valid values:

• blank: every sheet goes into the PDF.

• sheet numbers separated by commas, such as 1,3,5.

• ranges with a hyphen, such as 1-3 (the first, second, and third tabs).

• a mix, such as 1-5,8,10-12: tabs 1 through 5, then 8, then 10 through 12, as consecutive PDF pages.

Sheets are numbered left to right, starting at 1. XL Docs sorts the numbers and drops duplicates. If you list more sheets than the workbook has, generation stops with Something's wrong with the pages you selected. You entered more than the sheets you have in your template.

The field help reads: You need to specify the pages you want to be included in the PDF Document. You can use few of these formats, with the examples 1-3,5,7-10, 3-5, and 2,3,7,8,9.

Configurator label: PDF Pages (Fields tab, Template Settings; placeholder e.g. 1,3-5).

1-5,8,10-12

Master table name

Text

The name of the master table: the table the clist fields come from, and where the XL Docs button lives. Free-form text, for your reference.

The Configurator fills it in from the table you choose. The table XL Docs actually reads at generation time is the one passed in the button's dbid parameter.

Configurator label: the text of the Table dropdown (Fields tab, Master Table Selection).

Projects

Master table dbid

Text

The table ID (DBID) of the master table. The Configurator uses it to list the master table's fields in the Field Mapping grid and to resolve "(parent)" field comparisons in data-table queries.

Configurator label: the value of the Table dropdown (Fields tab, Master Table Selection). The tooltip reads: Select the master/parent table to grab data from. You must select a table, but you do not have to fill in fields below if you intend to only use subtables.

The DBID shown in the table's URL, for example bxxxxxxxx. See Find your Client ID, table IDs, and field IDs.

Document Subtable Excel DBID

Formula - Text (required)

Holds the table ID of the Document Subtable Excel table, so XL Docs can find this template's data tables. The installation wizard creates it as a formula that returns the table's alias. Don't change or remove it.

If the field is missing or blank, generation stops with The template record was found, however the 'Document Subtable Excel DBID' field was missing or blank. Please make sure that the field exists and is set to the DBID of the 'Document Subtable Excel' table.

Not shown in the Configurator.

[_DBID_DOCUMENT_SUBTABLE_EXCEL]

MSG_DetailsNotFound

Text

Optional message for this template. The field help reads: This message appears in the generated document when no records are found for the subtable data.

Not shown in the Configurator.

No line items found.

Exact Forms Plus Version

Text

Created by the installation wizard because the Document Templates table is shared with Exact Forms Plus. XL Docs doesn't use it; leave it blank for XL Docs templates.

(blank)

XL Docs Configurator

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

The button that opens the XL Docs Configurator for this template. It appears after the record is saved. See Configurator overview and installation.

Button labeled XL Docs Configurator

Add New XL Document Subtable

Formula - URL (button)

A standard Quickbase add-record button that opens a new Document Subtable Excel record already related to this template. Use it if you add data tables by hand instead of with the Configurator.

Button labeled Add New XL Document Subtable

Customers dump

A2:6.B2:43.C2:12.D2:34.E2:55.F2-N:52

6

{'3'.GT.'0'}

stlist B:Sub Total|F|, grouplist A:28|MC

Checked

1

Orders

A2:26.B2-D:6.C2:17.D2:18.E2:57.F2:7.G2-N:46.H2-N:25.I2:43.J2-N:37

26

{'3'.GT.'0'}

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

Checked

2

Line Items

A2:15.B2-N:6.C2:19.D2-N:7.E2:12.F2-N:8

15

{'3'.GT.'0'}

(none)

Checked

3