These examples show common ways to handle long lists, special formatting, images, and opening the generated files. For the settings themselves, see Data tables (subtables) and Document Subtable Excel table reference.
Max Lines type = Insert New Tab
Use this when a data table can return more records than fit in its area of the sheet, and you don't want the extra records in that area. XL Docs sends the additional rows to a new tab: a copy of the original sheet with the remaining records in the same area.
Example: an expense statement has room for 10 expense lines (rows 12 to 21), with totals, a subtotal, and an approval box below. Set these fields on the Document Subtable Excel record for the expense lines:
Field | Value |
|---|---|
Description | Expense Sheet details |
clist |
|
slist |
|
qry |
|
Max Line items | 10 |
Max Lines type | Insert New Tab |
Is Data Grid? | Checked |
Sheet |
|
For a project with 12 expense lines, the generated workbook has two tabs:
Expense statement (the original tab): the first 10 expense lines in rows 12 to 21, from 5/22/2021 to 10/10/2021. The template's totals row and formulas total these 10 lines (Subtotal and TOTAL $12,904.00).
Expense statement_1_1 (the new tab): a copy of the sheet with the remaining two lines, 11/1/2021 ($4,800.00) and 11/4/2021 ($3,045.00), in rows 12 and 13. Rows 14 to 21 are empty, and this tab's Subtotal and TOTAL are $7,845.00.
If there were more than 20 lines, XL Docs would add further tabs (Expense statement_1_2, and so on), 10 lines per tab. Insert New Tab is for ungrouped data tables. In the Configurator, set Max Line Items and Max Lines Type on the Data-Grid Settings tab.
Preserve special formatting with Insert New Rows
Sometimes the rows of a list use special formatting, such as merged cells. With Max Lines type = Insert New Rows, XL Docs inserts new rows when the list is longer than the area. To have the inserted rows copy that special formatting, add the URL parameter insertsf=y to the XL Docs button. XL Docs then uses the last formatted row of the area as the model for every row it inserts.
Example: an invoice template has a header block (Invoice #, PO #, Sent Date:, Delivery Date:, Terms: Net 30), From/Bill To/Ship To blocks, and a line-item table with the columns Item Description, Qty, Price, Tax %, Tax, and Total. In each item row (rows 11 to 14), the Item Description cell is columns B through F merged into one cell. There are only four item rows; row 15 starts the signature block and the Taxable / Non-Taxable / Subtotal / Tax / Total block, with standard, unmerged columns.
To fill this with any number of line items:
On the line items' Document Subtable Excel record, set Max Line items to
4and Max Lines type to Insert New Rows.Add
&insertsf=yto the button's URL (see Formula URL parameters):& "&insertsf=y"
XL Docs copies the formatting of the fourth item row, including the merged Item Description cell, to every additional row it inserts, then fills them. The signature and totals blocks move down below the last item. In the PDF output, every item row has the same merged description cell, and the signature block is at the bottom.
Images that might be missing
An image field mapped with -I (for example a signature or a photo) may be empty on some records, or its link may not load. When XL Docs can't load an image in the template clist, the cell shows Image Error instead of the image. To avoid surprises:
Point image mappings at a file attachment field or a formula URL field that returns a working image URL, for example a QR code service.
For a formula field that returns an
<img>tag, use a Formula - Text field with Allow some HTML tags turned on, and don't includeheightorwidthattributes; set the size in the clist (:Height|Width) instead.Test the template on a record without an image before you roll it out.
See Images.
Message when a data table finds no records
The MSG_DetailsNotFound field on a Document Templates record holds a message that appears in the generated document when no records are found for the subtable data, for example No line items found. See Document Templates table reference.
Enable editing when opening an Excel document
When you open a workbook downloaded from the internet, Microsoft Excel may open it in Protected View, a read-only mode with a yellow message bar and an Enable Editing button. This is an Excel security feature, not part of XL Docs. For a file you trust, select Enable Editing on the message bar to edit it.
Important
Turning off Protected View affects every file you open in Excel, not just XL Docs documents. Excel's own warning reads: Protected View opens potentially dangerous files, without any security prompts, in a restricted mode to help minimize harm to your computer. By disabling Protected View you could be exposing your computer to possible security threats. Check with your IT or security team before changing these settings.
If you decide to change the setting in the Excel desktop app:
Go to File > Options > Trust Center.
Select Trust Center Settings….
Select Protected View in the left list.
Clear Enable Protected View for files located in potentially unsafe locations.
Select OK.
If files still open in Protected View, clear the first checkbox, Enable Protected View for files originating from the Internet. Leave Enable Protected View for Outlook attachments as it is.
For the messages XL Docs shows and what to do about them, see XL Docs messages and troubleshooting.