Create or change an embedded report

An embedded report puts a table of records from another table into your document, for example the line items of a purchase order, the tasks of a project, or a summary of hours by person. In the Exact Forms Plus Configurator you build the report once: choose the table, the records, the columns, and the formatting. The configurator saves it as a record in your app's Document Subtables table. You then put that record's short reference, such as ~strid=12~, in the Word template where the table should appear. Every document made from the template gets the table, filled with the records for that document's master record.

This article covers the first two steps of the configurator, saving, and placing the report in the template. The other steps have their own articles: Choose and format columns, Sorting, grouping, and totals, and Table formatting options.

Before you start

• Set the template's master table first. Open the template record, select EFP Configurator, choose the master table on the Master tab, and select Apply Changes. See Choose the master table fields. Without it, the configurator shows The field Master Table DBID is blank in the Document Templates table.

• Know where the records come from. The report's table must be in the same app as the template. It's usually a child table of the master table (for example, Purchase Order Details under Purchase Orders), but it can be any table in the app.

• For a summary, create the summary report first. Summary and Master Summary embedded reports show a saved Quickbase summary report on the table. Create it in Quickbase before you open the configurator.

Open a new embedded report

On the Document Templates record, do one of these:

• Select Add New Exact Forms Plus Subtable. The configurator opens in a new window, on step 1.

• Select EFP Configurator, go to the Configurations tab, and select Add New Subtable Configuration.

The top of the window shows Master Table : and the template's master table. Five numbered tabs across the top are the five steps.

Step 1: Subtable settings

Under Subtable Options:

If you go on without both, the configurator shows Please select subtable and fill configuration name.

Step 2: Filter Criteria

Subtable Type

Important

You can't change the type after you first save the report. To switch, create a new embedded report.

For Summary and Master Summary, choose the report in the list that starts with List the summary reports. Only summary reports on the table appear. Crosstab reports aren't supported. The report decides the columns, so in the later steps you can format the columns but not choose them, and sorting, grouping, the item counter, and repeat columns aren't available.

Filtering Criteria

The filter decides which records the report includes. For a Detail report, a filter is required. For a table related to the master table, it's already filled in, for example Related Purchase Order is equal to the value in the field (parent) Record ID#. That means: the line items whose purchase order is the one the document is for.

To change or add conditions:

1. At the top, choose all or any, followed by 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 add icons to add a condition or a set of conditions. A set has its own all or any, so you can build filters such as "Status is Open, and Type is A or B". Sets can be nested up to three levels. Deleting a set asks Are you sure you want to delete this entire set of filters?

The operators depend on the field type:

What you can compare against:

A filter doesn't have to use the master record. For example, a price list template can show every product whose Status is equal to the value Active, the same in every document.

Advanced Filtering Criteria

For Detail reports only. All are optional.

Save before you go on

On a new embedded report, select Save before you go on to step 3. Choosing the table counts as a change, so the configurator won't move on to Subtable Fields until the report is saved. If you select Next with unsaved changes, the configurator shows Please save your changes before proceeding. A Detail report with no filter shows Please create a filter in Filter Criteria., and a summary with no report shows Please select summary reports.

Save the report

Select Save on any step. The configurator shows Subtable configuration has been saved successfully. The first save creates the Document Subtables record; later saves in the same window update it.

The Document Subtables record has:

• Configuration Name: the name from step 1.

• Settings: everything you chose, as one line of text such as ~dbid=bxxxxxxxx,qry={'8'.EX.[Record ID#]},type=d,…,clist=LT7:200.R$14:80~. Exact Forms Plus reads it each time it creates a document. For what each part means, see Advanced subtable features.

• Template Reference: the reference to put in the template, ~strid= followed by the record's Record ID# and ~, for example ~strid=12~.

• Related Document Template: the template the report belongs to.

• Notes: free text for your own notes. The configurator doesn't fill it in.

Place the report in the template

1. Copy the Template Reference value from the Document Subtables record, for example ~strid=12~.

2. In the Word template, paste it on its own line where the table should appear.

3. Save the Word file and attach it to the template record's Template File again.

You only do this once. When you change the report in the configurator later, the next document uses the new settings. You don't need to attach the template again.

Exact Forms Plus finds the Document Subtables table through the template record's Document Subtable DBID field. The installation wizard adds it as a formula field, so you don't fill it in. It isn't on the template form by default.

For more about how the table looks in the document, see Place the report in the template.

Change an embedded report

Open the report in one of these ways:

• Open the Document Subtables record, or find it in the list of embedded reports on the template record, and select Edit Subtable Configuration.

• On the template record, select EFP Configurator, go to the Configurations tab, and select Edit next to the report. It opens in another window.

Make your changes on any step and select Save.

Important

If you or someone else edited the Settings field by hand to add an option the configurator doesn't have, such as nobreakacrosspages, saving the report in the configurator removes that option. Add it back to Settings after you save, or keep making changes by hand.

Copy or delete an embedded report

• Copy: copy the Document Subtables record in Quickbase. The copy gets its own Record ID#, so its Template Reference is different. Open the copy with Edit Subtable Configuration to change it. If the copy is for another template, change its Related Document Template.

• Delete: remove its ~strid=N~ reference from the Word template and attach the template again, then delete the Document Subtables record in Quickbase. A reference to a deleted record shows ERROR: STRID = N - The subtable record was not found. in the document.

Worked example: purchase order line items

Your app has a Purchase Orders table and a child table, Purchase Order Details, with the fields Item, Quantity, Unit Price, and Line Total. The purchase order template should list each order's items, with a total at the bottom.

1. On the purchase order template record, select Add New Exact Forms Plus Subtable.

2. In step 1, enter Purchase Order Line Items as the Configuration Name and choose Purchase Order Details.

3. In step 2, leave Detail selected. The filter is already filled in with Related Purchase Order is equal to the value in the field (parent) Record ID#. Select Save.

4. In step 3, select Item, Quantity, Unit Price, and Line Total, in that order. Open Advanced Settings for Line Total and select Grandtotal. See Choose and format columns.

5. In step 4, sort by Item, ascending. Under Grandtotal Label Options, show the text Total in the Unit Price column.

6. In step 5, set a header background color and alternate row shading. Select Save, then Close.

7. Open the new Document Subtables record and copy its Template Reference, for example ~strid=12~. Paste it on its own line in the Word template, below the order details, and attach the template again.

A document for a purchase order with three items now includes a table like this:

If the order has no items, the reference is replaced with No detail records found., or with the text in the template's MSG_DetailsNotFound field.

Limits

• The report's table must be in the same app as the template.

• Your account sets the maximum number of records in one embedded report. If a report finds more, no document is created and the error says what the limit is.

• Values in a filter can't contain commas, because commas separate the parts of Settings.

If something doesn't work, see Exact Forms Plus Configurator messages and troubleshooting.

Need help? Submit a technical support case.