Generate documents for multiple records

XL Docs can create documents for many records with a single button click. Instead of pointing the button at one record with msid, you give it a query (msqry) or a report (msqid), and XL Docs creates one document for every record that matches. You can also combine the documents into one workbook, or merge them into a single PDF that is saved on the record where the button was clicked.

This is called web-based processing: the user clicks a formula button on a Quickbase form and XL Docs runs while the user waits in a progress window. It creates up to 50 documents per click by default; your account's limit may differ, so a single request can't run for a long time. If you need more than 50 documents, or want documents to be created automatically without a click, use Batch processing instead.

How it works

1. The user clicks the button on a record—for example, a Project.

2. XL Docs runs the query in msqry (or the report in msqid) against the table in dbid—for example, Project Employees—and finds the matching records.

3. For each record, XL Docs fills the template from the Document Templates record in tpid, exactly as it would for a single record, including any data tables.

4. Each document is saved to that record—in the file attachment field in faid, or in cloud storage or a repository table if you use those options (see Output and storage).

5. Optionally, XL Docs puts all the records into one workbook (sameworkbook), or merges all the PDFs into one file and saves it on the record you choose (mrgdbid, mrgrid, mrgfa).

6. The progress window shows how many documents were created—for example, 3 Document(s) generated and uploaded successfully.—with a Close button.

Records are processed in the default sort order of the table in dbid. To choose which records run, use a report (msqid, or msqry with a report ID) or a query (msqry).

Example: expense sheets for every employee on a project

This example uses three related tables:

The goal: on a Project, one click creates an expense statement for each employee on the project, listing all of that employee's expenses, and attaches each statement to its Project Employee record.

Step 1: Set up the template for one record

First, set up the template exactly as you would for one Project Employee. A multiple-record run uses the same template configuration for every record. In this example the template is an expense statement:

• Cells B6, B7, and B8 hold the Project Name, Project Number, and Project Location, and cells K6 and K7 hold the project's start and end dates. These are lookup fields on the Project Employees table.

• Row 11 is a header row: Date, Account, Employee, Hotel, Transport, Fuel, Meals, Phone, Entertain., Misc., TOTAL.

• Row 12 is the first row of expense lines, followed by Subtotal, Advances, and TOTAL rows.

In the XL Docs Configurator, this looks like:

1. Fields tab: Table is Project Employees, Template Name is Employee Expense Template, Excel Sheet # is 1, and the lookup fields are mapped to cells B6, B7, B8, K6, and K7. See Configure a template.

2. Data Tables tab: one data table, Expense Sheet details, on sheet 1, reading the Expense Sheets table.

3. In that data table's Table/Fields tab, the expense fields are mapped across row 12: Date to A12, Account to B12, Employee Name to C12, Hotel Exp to D12, Transport Exp to E12, Fuel Exp to F12, Meals Exp to G12, Phone Exp to H12, Entertainment Exp to I12.

4. In its Query tab, the filter finds all expenses for the employee: all of these conditions are true—Project Employee is equal to the value in the field (parent) Record ID#. The sort is Date (Asc). See Configure a data table.

Test the template on a single Project Employee record first. When one document looks right, every document in the multiple-record run will too.

Step 2: Create the button on the parent table

On the Projects table, create a Formula - Text field with Allow some HTML tags turned on (for example, Create ALL Project Employee Expense Sheets) with this formula:

var text url = "https://.../ExcelGen_LP.aspx?"  // your XL Docs server URL, created by the installation wizard
& "clientid=Q#"  // your Client ID
& "&appid=" & AppID()
& "&dbid=" & [_DBID_PROJECT_EMPLOYEES]  // the table to create documents for
& "&tpdbid=" & [_DBID_DOCUMENT_TEMPLATES]
& "&tpid=36"  // Record ID# of the Document Templates record
& "&fn=My_Project_Expenses_" & ToText(Today())
& "&msqry={'6'.EX.'" & URLEncode([Record ID#]) & "'}"  // Related Project = this project
& "&faid=62"  // file attachment field on Project Employees
& "&xlddte=63"  // date/time field on Project Employees
& "&docfmt=xlsx"
& "&stream=n"
& "&apptoken=[Your App Token]";

"<a class='Vibrant Success OpenAsPopup POSTTempToken' data-height=400 data-width=600 data-refresh=true data-temptokendbid='" & [_DBID_PROJECT_EMPLOYEES] & "' href=\"" & $url & "\">Create ALL Project Employee Expense Sheets</a>"

Replace https://... with your designated XL Docs server URL from the installation wizard, Q# with your Client ID, and the field and record IDs with your own (see Find your Client ID, table IDs, and field IDs). Replace [Your App Token] with an application token from your app's settings, or leave it empty if your app doesn't require application tokens. Never publish a real token in shared formulas or documentation. The button follows the Quickbase enhanced-security pattern. Because dbid is the Project Employees table, data-temptokendbid names the same table ([_DBID_PROJECT_EMPLOYEES]): XL Docs checks the temporary token against the table in dbid. See Change formula fields and Configure pop-ups.

What the important parameters do in this example:

• dbid is the table the documents are created for—Project Employees—not the table the button is on. Each document is attached to its own Project Employee record.

• msqry selects the Project Employee records whose field 6 (Related Project) equals this Project's Record ID#.

• faid and xlddte are the file attachment field and the date/time field on the Project Employees table that receive the document and the date/time it was created.

• stream=n: when several documents are created, they are saved to the records, not sent back to the browser. The progress window confirms how many were created.

The result: for this Project, one expense sheet is created for each Project Employee and attached to that employee's record. For example, a Project with five employees ends up with five files—My_Project_Expenses_03-02-2024_6.xlsx, My_Project_Expenses_03-02-2024_7.xlsx, and so on, each ending in the Project Employee's Record ID#—and an embedded Project Employees report on the Project form shows each file and its created date.

Merge all documents into one PDF

You can also combine all the documents from one click into a single PDF and save it on the current record—here, the Project. Add the merge parameters to the formula and set docfmt=pdf; merging only works with PDF output.

var text url = "https://.../ExcelGen_LP.aspx?"  // your XL Docs server URL, created by the installation wizard
& "clientid=Q#"
& "&appid=" & AppID()
& "&dbid=" & [_DBID_PROJECT_EMPLOYEES]
& "&tpdbid=" & [_DBID_DOCUMENT_TEMPLATES]
& "&tpid=36"
& "&fn=My_Project_Expenses_" & ToText(Today())
& "&msqry={'6'.EX.'" & URLEncode([Record ID#]) & "'}"
& "&faid=62"
& "&xlddte=63"
& "&docfmt=pdf"  // merging requires PDF output
& "&stream=n"
& "&apptoken=[Your App Token]"
// Merge into one PDF on this Project record
& "&mrgdbid=" & Dbid()
& "&mrgrid=" & URLEncode([Record ID#])
& "&mrgfa=242"  // file attachment field on Projects
& "&mrgfn=ALL_Project_Employee_Expense_Sheets"
& "&mrgdte=243";  // date/time field on Projects

"<a class='Vibrant Success OpenAsPopup POSTTempToken' data-height=400 data-width=600 data-refresh=true data-temptokendbid='" & [_DBID_PROJECT_EMPLOYEES] & "' href=\"" & $url & "\">Create and merge expense sheets</a>"

• mrgdbid, mrgrid, and mrgfa are the table, record, and file attachment field where the merged PDF is saved.

• mrgfn is the merged file's name. .pdf is added for you. If you leave it out, the file is named Merged_XL_Docs_File.pdf.

• mrgdte is a date/time field on the same record that is set to the date and time the merged file was created.

One workbook with a tab per record

Instead of one file per record, you can put every record into a single workbook, with each record on its own tab. Add sameworkbook=y:

• Each tab is named after the template's tab with a number added (for example, Expense statement_1, Expense statement_2), or after a field's value if you add snfid.

• By default, the workbook is saved to the record in faid. To save it somewhere else—for example, on the Project—add all three of swbdbid, swbrid, and swbfaid.

• Because there is only one file, the workbook can be streamed back to the browser with stream=y (use the ExcelGen_UA.aspx address for streaming; see Output and storage).

& "&sameworkbook=y"
& "&snfid=9"  // name each tab from field 9 (for example, Employee Name)
& "&swbdbid=" & Dbid()  // save the workbook on this Project
& "&swbrid=" & [Record ID#]
& "&swbfaid=155"

Parameters for multiple records

These parameters are added to the usual ones described in Formula URL parameters. Use msqry (or msqid) instead of msid; a request with both is rejected.

Note

When a text field's value is part of a query or file name, wrap it in URLEncode() so special characters reach XL Docs correctly. For query syntax and operators, see Components of a query in the Quickbase help.

Limits and messages

For other messages, see XL Docs messages and troubleshooting.