Exact Forms Plus!
Including embedded reports or Subtable data in your documents
Subtable data or detail data can be displayed for the master record that the document is being generated for. In other words, if you have a Purchase Order table that has a relationship to a Purchase Order Details table, you can display the related Purchase Order Detail records when creating a Purchase Order document.
This figure shows the relationship from the master table (Purchase Orders) to the detail table (Purchase Order Details).
The detail data appears in tabular format in the generated document. The highlighted section in the figure below represents the detail data associated with a purchase order record. The format of the data can be controlled with special parameters listed in the chart below. You can control the width of each column, the font style including bold, underline, and italics, cell justification (left, center, or right), and alternate row shading. You can also suppress the table header and borders.
Document Subtables
On the Document Subtables table in your app, the field named Settings controls the formatting of the Subtable that appears in the generated document.
Here is a screenshot from one of our demo apps of the settings field. This field is populated by the Exact Forms Plus Configurator utility that is installed in your app.
This utility helps you select the fields that you want to appear in the Subtable as well as the advanced formatting options that we offer in Exact Forms Plus. You can manually edit this
settings field, but be careful if you do so. One wrong character can cause unexpected results, and then it's best to start over.
The best way to use this is to add this string into your template: ~strid=39~ The number to use is the record ID of the record in the Document Subtables table.
Here is a screenshot from one of our demo templates
Related table parameters: Settings field explained
Parameter | Description | Sample Value | Required |
dbid | Database ID—for the related table | bdht34xg7 | Yes |
qry | Query—specifies the filtering criteria for retrieving the records from the related table. | qry={‘6’.EX.[Record ID#]}AND{‘10’.EX.’Active’} | Yes No when using qid. |
type | Subtable Type - Detail, Master Summary or Summary Detail—this is the default type. If type is not in the settings field, then detail is assumed. Detail is a listing of all records that meet the filter in the qry parameter. Master Summary—this is used to display a summary report that will be filtered by the master record that the document is being created for. | d | No |
opts | This is the same as per Quickbase api documentation. Specifies data retrieval options for the query. You can use any or all of the options, separating the options with a period. num-n --Specifies a maximum of n records in the return onlynew --Returns only those records marked with new or updated flags skp-n --Skips the first n records returned by the query sortorder-A --Specifies an ascending order sortorder-D --Specifies a descending sorts. **When using sortorder-X, you need to include an option (X) for each field in your slist. Note: If you are grouping too, you need to include a sort order option for the group. Ex. slist=7.8.9, opts=sortorder-DDD will sort all fields descending. (3 slist fields, 3 sort options) slist=7.8.9,groupfid=20, opts=sortorder-ADDD will sort the grouped data in fid 20 ascending and then sort the other 3 fields descending. (3 slist fields, 4 sort options (include one for the group)) Important: if a field you're trying to sort by is a Text Multiple Choice field, and you have the field property set to Display choices in the order shown here like this:
The sortorder may not appear as expected. However, It will work properly if you have the choices sorted alphabetically. Another option is to create a separate formula text field and set the formula = the Multiple Choice Field and use that field in the sort instead | num-10 onlynew skp-2 sortorder-A sortorder-D | No |
align | Alignment of the Subtable created in the document l-left, r-right, c-center | align=l | No |
clist | Column list—defines the columns that should appear in the subtable and how to format both the column itself and the data to display. Each column is delimited with a "." and each column can have 4 components which are delimited by ":". 1. (Horizontal Alignment and data type) RN—Right justified, Numeric LT—Left justified, Text R$ - Right justified/Currency CD—Center, Date R% - Right(or Left/Center) justify/Percent RF—Right Float (numeric decimal) CC—Center Checkbox CI—Center Image R# - Right Item# Counter -Used to include a column that acts like a item counter. LH - Left HTML -Used to handle text fields that allow html tags (Vertical Alignment - optional - default is middle) T - Top M - Middle B - Bottom Ex: RNT10:50 - Right, Numeric, Top, field 10, width of 50 (FieldID) - this is the numeric value that Quickbase assigns to the field on that table itself and is required. Ex: RNT10 - 10 is the fieldID of the field in the table that holds the data to be dispalyed in the column. 2. (Column width) :xx—the colon is the separator for width of column, xx = width value in points You must at least include the prefix characters, the field ID, and the width for each column. So for a column that will display the values in field id 10 you need at least the following: RN10:50 This reads: Right Justify and format as numeric the data stored in field id #10 and adjust the columns width to be 50 points. There are also additional parameters that are optional to control the Column Name and Font attributes. Note: 1 inch is approximately 72 points (https://www.unitconverters.net/length/point-to-inch.htm) 3. (Column Name) :XXXXX—this allows you to override the column header name. If omitted, the Quickbase field name is used for the column header. Tip: If you use the word NULL, the column name is blank for this column. :NULL 4. (Column Label Alignment) :c—this allows you to control the column label alignment for each column (:l or :r for left and right respectively). If omitted, the value in the ahdr parameter is used for all column labels. If ahdr is omitted as well, the default is center. (Font attributes - use "Pipe" delimiter) The pipe character (|) indicates font definitions to follow for the column—for example, BIU:12:Arial means Bold, Italic, Underline, then font size, then font family, separated by colons. Ex. To define one field you may have the following: R$14:90:AMOUNT|BU:12:Arial * What does this mean? Right justify field 14, display it as currency, give it a width of 90, use AMOUNT as the column name, Bold and Underline it, use Arial font with size 12. Images: To display an image, you may use something like this. CI142:50 - which reads like this....Center and display as an Image the file referenced in field id 142 and size it to 50 pixels. Ex: with all possible settings RNT10:50.LTM:100:QUANTITY:c|BIU:12:Arial | RN10:50.LT11:100 With Vertical Alignment RNT10:50.LTM:100 | Yes |
groupfid | Group by Field ID—if you want the Subtable grouped by a particular field in your related table, specify the field ID of that field here. (You can only group by one field.) You can also request a page break after each grouped value. Include ":pb" after the field id that you want to group on, 17:pb Alignment can be controlled and is found after the 2nd ":". Values are c, l, r Example groupfid=44:pb:c Controlling the Font is also available, those settings are found after the "|" Example groupfid=44:pb:c|BIU:20:Arial:Blue | 17 or 17:pb | No |
slist | Sort Order—this determines how the data that appears in the Subtable is sorted slist=7.8.9 -Primary sort will be field 7, then 8, then 9 - Default is ascending Use opts parameter above to use descending | 7.8.9 | No |
stlist | Subtotal list—specify which columns of data to total. They should be numeric fields. | 10.14 | No |
stlbl | Subtotal Label - If you are subtotaling and you want to display some text in the subtotal row you can specify which column and what text to display. stlbl=1.SUB The text "SUB" will appear in the subtotal row in column 1. stlbl=1.12 The data stored in field 12 will appear in the subtotal row in column 1 | stlbl=1.SUB or stlbl=1.12 | No |
astl | Align the Subtotal Label in the cell that it appears in Left, Center or Right - l,c,r Important: astl must appear after stlbl in the settings field parameter list. | astl=l | No |
alternaterowcolor | Alternate Row Shading—if you want to shade every other row, include this parameter. Omitting it eliminates shading. You can specify a color or an html color code Ex: alternaterowcolor=blue or alternaterowcolor=#808000 If you don't include a specific color then the default or smoke gray is used. | alternaterowcolor or alternaterowcolor=blue | No |
rcfid | Row Color Field ID—dynamically control the color of each row in the subtable based on a field on the table. Set the value of that field to be equal to a color based on specific criteria for each record. Ex: vary the color of each row in the subtable based on the value of the [Product Status] field on each record. Case([Product Status], When in the EFP Configurator, select this new field in the Dynamic Row Shading drop down list on the Subtable Formatting Options tab, in the Row Shading Options section. | rcfid=99 | No |
hdr | Control the color of the table header—background and font colors. Use a color name or an html color code. Valid color names: blue,gray,green,indigo,navy,orange,pink,purple,red, yellow,violet,white hdr=#808000 or hdr=blue Control background AND Font color as well BACKGROUNDCOLOR-FONTCOLOR First Color is the background color second is the font color hdr=#808000-#ffffff or hdr=blue-white | blue or blue-white | No |
sub | Control the color of the Subtable row. Use a color name or an html color code. | blue or blue-white | No |
ahdr | Default column header alignment. Applies to all columns. You can also control each column separately | ahd=l ahdr=r | No |
noheader | No Header—suppress the header from appearing in the table | noheader | No |
noborder | No Border—suppress the table border. | noborder | No |
bdrc | Border Color—you can control the color of the three borders: outside, vertical, horizontal. bdrc=Black-Red-Navy To hide a border set the color to white or set the style to None. You can also use html color codes instead of the text name | bdrc=black-black-black | No |
bdrs | Border Style—you can control the style of the three borders: outside, vertical, horizontal. bdrs=Single-Double-Dot Possible values for the border style are: Cleared DashDotStroker DashLargeGap DashSmallGap Dot DotDash DotDotDash Double DoubleWave Emboss3D Engrave3D Hairline Inset None Outset Single Thick ThickThickThinMediumGap ThickThinLargeGap ThickThinMediumGap ThinThickLargeGap ThinThickMediumGap ThinThickSmallGap ThinThickThinLargeGap ThinThickThinSmallGap ThinThinSmallGap Triple TwistedLines1 Wave | bdrs=Single-Double-Dot | No |
pagebreak | Page Break—how many detail lines should show before going to the next page. | 12 | No |
rpt | Repeat Columns—this is used to display the columns in the clist parameter more than once horizontally. So if you had 3 fields in the clist and set rpt=1 you would get 6 columns in the subtable. The data from the first record returned would appear in the first 3 columns, the data from the second record returned would appear in the last 3 columns...the data from the 3rd record would start on the next line...and so on. This is a way to display data horizontally instead of vertically. Valid values are 1 thru 10. Note: Grouping and subtotaling don't work when using repeat. | rpt=1 | No |
cpad | Cell Padding—padding is applied to all cells in all columns and rows in the subtable. Top: 0.03 Bottom: 0.03 Left: 0.03 Right: 0.03 Spacing between Cells: 0 Auto Resize to Fit: 0 This follows how Microsoft Word controls cell padding | cpad=0.03:0.03:0.03:0.03:0:0 | No |
* Colors: we support the following color names. If the color you want to use is not in this list, you can use html color codes instead. (#FFFFFF)
Red,White,Blue,Black,Navy,Orange,Yellow,Gray,Green,Indigo,Violet,Pink,Purple,Brown,LightGreen,LightBlue
Example:
The following is the parameter string used within the Purchase Order template in our demo app. This is designed to retrieve the related Purchase Order Details records for the Purchase Order record and format the data in a Microsoft Word table in the output document.
Note: The EFPlus Configurator is a utility that can be used to help build this parameter string. If you have an older version of EF+ and you want to use the EFPlus Configurator contact us for an upgrade.
The following code is typically stored in the "settings" field on the Document Subtables table. The EFPlus Configurator will update the "settings" field with the changes made while using the configurator BUT, you can also directly edit that field in Quickbase, without having to use the configurator.
Settings field sample:
~dbid=bdht34xg7,qry={‘6’.EX.[Record ID#]},slist=3,clist=RN10:45.LT11:90|U.LT12:125|I:8:Calibri.CC21:30:*.R$13:75.R$14:115|B:16:Arial.CI22:35:Other FA.CI31:25:FA,groupfid=17:pb,stlist=10.14,alternaterowcolor,hdr=blue~Settings field broken down parameter by parameter:
dbid=dbht34xg7 - This identifies the table to retrieve the related or child data from. This is the id or dbid of the desired table. In this example it refers to the Purchase Order Details table in our demo app.
qry={'6'.EX.[Record ID#]} - This is the filtering criteria that determines the data from the child table that will be selected and included in the document. In this example, the data returned will include all records from the Purchase Order Details table where the value in field id '6' is equal (.EX.) to the current Purchase Order record ([Record ID#]) being processed. Exact Forms Plus uses the standard 'qry' syntax available in the Quickbase API.
slist=3 - This indicates which field to sort the returned records by. In this example, the selected Purchase Order Detail records will be sorted by the value stored in field id 3.
clist= - This identifies which fields from the Purchase Order Details table to include in the Microsoft Word table to be created in the output document. There are various formatting options that can be used for each column of data. Each column is delimited by a ".". Here is a breakdown of each column included in the clist code above:
RN10:45 - Right justify and format as Numeric the data in field id 10. Set the column width to 45 pixels.
LT11:90|U - Left justify and format as Text the data in field id 11. Set the column width to 90 pixels and Underline the data.
LT12:125|I:8:Calibri - Left justify and format as Text the data in field id 12. Set the column width to 125 pixels and Italicize the data. Use font size 8 and use the Calibri font style.
CC31:30:* - Center justify and format as a Checkbox the data in field id 31. Set the column width to 30 pixels. Override the column header name to use *.
R$13:75 - Right justify and format as ($)Currency the data in field id 13. Set the column width to 75 pixels.
R$14:115|B:16:Arial - Right justify and format as ($)Currency the data in field id 14. Set the column width to 115 pixels and Bold the data. Use font size 16 and use the Arial font style.
CI22:35:Other FA - Center justify and format as an Image the data in field id 22. Set the Image Size to 35 pixels. Override the column header name to use Other FA.
CI31:25:FA - Center justify and format as an Image the data in field id 31. Set the Image Size to 25 pixels. Override the column header name to use FA.
groupfid=17:pb - This indicates which field to group the data by. It also indicates that a page break is being requested after each group (pb). In this example the data will be grouped by the data in field 17 of the Purchase Order Details table.
stlist=10.14 - This indicates the fields in the Subtable that should include a subtotal. In this example the column with fields 10 (Quantity) and 14 (Total Price) will include a subtotal in the table in the output document.
alternaterowcolor - This causes the background color of every other row in the Subtable to be shaded smoke gray.
hrd=blue - This indicates what background color to use for the header column of the Subtable in the output document.
Here is the generated output document that used the above Subtable configuration.
Another example of what is possible using the above parameters
