Courier for Quick Base
Subtable configuration
Including subtable data in the email message body.
This is a sample of the code string that you can include in the message body of the Courier configuration in order to display data from a related table:
~dbid=bbbbbbbbb,qry={'14'.EX.[Record ID#]}AND{'9'.EX.'Active'},stlist=18,alternaterowcolor,hdr=#4800ff:white,clist=CN44:15:Quantity:.LT54:45::.R$162:20:Monthly Fee:.R$18:20:Total:~
This tells Courier to display the related records from table defined in the dbid parameter. The qry parameter is used to filter the data from the related table. The rest of the parameters and their formatting options are defined below.
Based on the sample code above, this is what appears in the email message body when Courier executes for this configuration.
Related Table Parameters defined
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 |
clist | Column list – defines which columns should appear in the table. The prefix characters are required and tell the script how to format the data. (Justification first, then 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 (Column width) ":xx" – ":" 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 column's width to be 50 points. There are also additional parameters that are optional to control the Column Name and Font attributes. (Column Name) ":XXXXX" - this will allow you to override the column header name. If omitted, the Quickbase field name is used for the column header. | RN10:50.LT11: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 1 field) The format is: groupfid=fieldid::alignment|BUI(BoldUnderlineItalics):::grouprowbackgroundcolor-grouprowfontcolor | groupfid=7::l|B:::#b6eff2-#040404 or groupfid=7::c|BU:::Blue-white | No |
slist | Sort Order - this determines how the data to appear 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 below to use descending. | 7.8.9 | No |
opts | This is the same as per Quickbase's 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 to return. onlynew -- Returns only those records marked with new or updated flags. skp-n -- Skips the first n records returned by the query. A -- Specifies an ascending order. D -- Specifies a descending sort. **When using sort order X, you need to include an option (X) for each field in your slist. NOTE: If you are grouping, you need to include a sort order option for the group as well. Ex. slist=7.8.9, opts=DDD will sort all fields descending. (3 slist fields, 3 sort options) slist=7.8.9,groupfid=20, opts=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)) | num-10 onlynew skp-2 A D Actual setting will read opts=num-10.onlynew.skp-2.AD | No |
stlist | Subtotal list – specify which columns of data should be totaled | 10.14 | No |
alternaterowcolor | Alternate Row Shading – if you want to shade every other row then include this parameter. Omitting it will eliminate shading. | alternaterowcolor | No |
hdr | Control the color of the table header. 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 First Color is the background color second is the font color The format is: hdr=backgroundcolor:fontcolor hdr=#808000:#ffffff or hdr=blue:white | blue or blue:white | No |
orient | The table can be displayed horizontally or vertically. The default is Vertical | v - vertical h - horizontal | No |
width | To control the overall width of the table in the message body include the width parameter | width:80% | No |