Advanced subtable features

This page covers advanced options for embedded reports (subtables): grouping rows, filtering records, repeating columns, header and alignment settings, and putting a table inside a table cell. It's for users who already have an embedded report working. To create one, see Insert embedded reports (subtable).

Each section links to the Exact Forms Plus Configurator page that describes the option, then covers how it works and the matching text in the Settings field of the Document Subtables record, for users who edit it directly. Settings are separated by commas, so no value can contain a comma.

Group rows in an embedded report

Grouping splits the rows of an embedded report (subtable) by the value of one field. Each group starts with a heading row and can end with a subtotal row. For example, the line items of a purchase order can be grouped by Category, with the total price of each category.

Example

Line items grouped by Category, with Total Price subtotaled, a subtotal label in the first column, and a grand total at the end:

The group field (Category) doesn't have to be a column in the table. Its value appears in the heading row.

Set up grouping in the EFP Configurator

Grouping is set on step 4 of the embedded report, Sorting and Grouping. Every grouping option is described in the Exact Forms Plus Configurator help:

• The group field, group order, the heading row's look, a different field in the heading, and page breaks: Grouping Options.

• The subtotal and grand total labels and colors: Subtotals and grand totals.

• Which columns are added up (Subtotal and Grandtotal in a column's Advanced Settings, step 3): Column DATA Properties.

To open an existing report in the configurator, see Create or change an embedded report. After you save, the template doesn't need to change: the same Template Reference value (~strid=N~) picks up the new settings the next time a document is created.

How grouping works

• Sorting. Rows are sorted by the group field first, then by your other sort fields, so each group's rows are together.

• Heading row. A heading row is added each time the group field's value changes. It shows the group field's value, or the value of the field you chose in Field to Display for Group, taken from the group's first record.

• Date fields. If you group by a date field, the heading uses the field's display settings in Quickbase: with the day of the week and month name turned on, for example, it reads Monday, March 22, 2010.

• Subtotal rows. A subtotal row is added after each group, but only if at least one column has Subtotal turned on. It totals those columns for the group. A label taken from a field uses that field's value in the group's first record.

• Grand total row. One row at the end of the table, totaling the columns with Grandtotal turned on.

• Without grouping. Subtotal and grand total columns still work: the table ends with one subtotal row (and a grand total row, if set).

• Page breaks. With Include Page break after group by, each group after the first starts on a new page.

Grouping in the Settings field

The configurator writes the same text without the tildes into Settings.

Grouping limits and known issues

The limits shown in the configurator (one group field; no commas or periods in labels) are on Sorting, grouping, and totals. Also:

• You can't group by a field that contains HTML. You can still show an HTML field in the heading with Field to Display for Group.

• Summary reports (Summary and Master Summary embedded reports) keep the grouping of the saved report. Don't add grouping to their Settings field by hand, and don't take their grand total label from a field: the document isn't created.

• Repeat columns can't be grouped. See Repeat columns.

• If your first sort field's ID begins with the group field's ID (for example, grouping by field 6 and sorting by field 61), rows may not stay together and the heading can repeat. Add the group field as the first sort field yourself (slist=6.61).

Filter the records in an embedded report

The filter decides which records appear in the table.

In the EFP Configurator

Filters are set on step 2 of the embedded report, Filter Criteria. The conditions, operators, what you can compare against, and the pre-filled filter for related tables are described in Filtering Criteria. The maximum number of records, skipping records, and new/updated records are in Advanced Filtering Criteria.

Master record fields used in a condition are added to the template's field list automatically when you save.

Filtering in the Settings field

Values from the master record

• Use the master field's exact name, with the same capitalization, in square brackets: [Customer Name]. If the name doesn't match, it isn't replaced and the report usually finds no records.

• The field must be in the template's field list (clist). Record ID# is always available. The configurator adds fields you pick in the filter; if you write the filter by hand, add the field yourself.

• The value is inserted as it's displayed: numbers with thousands separators, dates in the app's date format. Quotes aren't added for you, so put them around text values: {'7'.EX.'[Customer Name]'}. Comparing on Record ID# or a related-record field is the most reliable.

• Values that contain an apostrophe can break the filter, so the report shows no records.

Filtering limits

The limits shown in the configurator (your account's record limit, no commas in filter values, no crosstab reports) are on Create or change an embedded report. If a report finds more records than your account allows, use a lower Maximum Number of records to return, a narrower filter, or the Exact Forms Plus Batch Processor.

Repeat columns

Repeat columns lay records out across the page instead of down it. The setting is Repeat Columns on step 5 of the embedded report; see Miscellaneous Options. In the Settings field it's rpt= and the number of copies, for example rpt=3.

Example: a report with the columns Part and Qty, with columns repeated three times, showing seven records:

Each copy has its own column headings. Row shading (Row Shading and Alternate Row Shading) still applies to each row.

With repeat columns, these options don't work:

• Grouping, subtotals, and grand totals. Don't set a group field on a report with Repeat Columns: the configurator saves both, and the grouping and totals are ignored. If Include Page break after group by is also set, no document is created.

• Dynamic Row Shading and the item counter (Include Item Counter).

• Custom date formats in columns.

• Blank column headings: NULL is printed as the word NULL instead of an empty heading.

Column headings and alignment

In the configurator, the options for all columns are on step 5, Table formatting options (Column headers, Table Alignment, and Miscellaneous Options). The options for one column are in its Advanced Settings on step 3. The table below gives the matching text in the Settings field, and the default when it's left out.

With pagebreak, each page's rows are a separate table with its own heading row.

Put a table inside a table cell

An embedded report in a cell of your template

You can lay out a page with a Word table in your template, for example two columns side by side, and paste a Template Reference value (~strid=N~) into one of its cells. Exact Forms Plus replaces it in the same way as anywhere else in the document, so the embedded report appears inside that cell. Keep the value on its own line in the cell, set the report's column widths so the table fits the cell, and check a test document before using the template.

An embedded report inside another embedded report

Exact Forms Plus has no built-in option to show a second embedded report in each row of the first, for example the tasks of each project in a list of projects. You can get this result with a formula field, but it isn't a supported feature, so test it carefully before you rely on it:

1. In the outer report's table (for example Projects), create a Formula - Text field that builds the inner report's settings, including the row's own Record ID#. For example:

   "~dbid=bxxxxxxxx,qry={'12'.EX.'" & ToText([Record ID#]) & "'},clist=LT6:150.RN7:60~"

   Here bxxxxxxxx is the inner table (Tasks), field 12 is its reference to the project, and the clist lists the inner columns.

2. Add that field to the outer report as a Text column. To leave its heading blank, use NULL as the heading.

3. When the document is created, the outer report fills each cell with that settings text, and Exact Forms Plus then builds an inner table in each cell.

Things to know:

• The inner table's values from square brackets ([Field Name]) come from the document's master record, not the outer row. That's why the formula builds the row's Record ID# into the text.

• A ~strid=N~ value doesn't work here. The full settings text, starting with ~dbid=, is required.

• Each inner table is a separate request and counts separately toward your account's record limit, so large outer reports are slow.

• The inner table uses the outer cell's font. Settings can't contain commas.

• Never let an inner report show the same formula column again. The tables would keep nesting, and the document wouldn't finish.

See also: Insert embedded reports (subtable), Exact Forms Plus Configurator overview, Troubleshooting.