Email Queries, SES & Certificates: Query — Parameters

Parameters

Each type of job has different parameters that need to be defined in the Scheduler / Job for it to run. Below are the parameters that are used in a mailQuery job.

• Query ID — Input Text, queryid for the required gbqueries record. Includes a search icon to search queries by Name or Description.
• Mail Footer — Check this to use the stored mail footer text in … Attachment Format Choose the required format for the attachment file from 'Office(xlsx))', 'Office(xls)', 'Text (csv)' or 'Text (txt)'. 'Office(xlsx))' is the default value and saves with an xlsx suffix. Further parameter variables are hidden if this format is chosen. 'Office(xls)'Format) enables you to customiseFormat) the presentation of the generated files. Further parameter variables are hidden if this format is chosen. 'Text (csv)' generates a text delimited file and uses the CSV, Comma Separated Variable, format. 'Text (txt)' generates a text delimited file and uses the Text (Tab Delimited) format.
• Create Event — It is enable by default if event key fields i.e. 'eventcontractid', 'eventdriverid', or 'eventclientid' is passed in the query. Based on event key defined, it will create an event that stores the email into a Contract, Client or Driver event. If this is un-checked, "Event Table" and "Event Type" are disabled.
• Event Table — Displays the type of event that will be created. Field is not editable and shows value based on set-up done in the query. Refer Event Table for set-up details.
• Event Type — Select the event you want to create and store the email record against. This is a list of events that belong to the chosen "Event Table". Note: The contract events 'Accident', 'Cost Centre Change', 'Division Change', 'Driver Change' and 'Location Change' are excluded from the drop down list.
• Auto Send Emails — Un-check this if you want to leave the generated emails in 'Pending' status to be reviewed and sent later from the Mail Queue. 'Pending' emails cannot be sent from the events tab. The field is checked by default.
• Mail Limit — Numeric value to set the limit of emails being sent. Default value for this field is 'blank'.

Event Table

Event Table is one of the parameters available to set-up the job which shows the event type it will create. It is not editable and set-up is required to be done within the query.

How it works

To create a mailQuery Job, you first need to write a specially formatted query called a mail query, and save it in the table (this is normally done for you by Catch-e staff). You will now be able to select the query from the search list when setting up the job.

The query contains all the information needed to create the email batch, including the contents of the attachment (if required).

When the scheduler runs the job to send the email batch, it also sends an email notification to confirm it has run. To receive this notification, add your email address in the Mail Recipients section on the Scheduler/Job tab. The notification can have an exceptions file attached. The exceptions file gives details of warnings or errors from generating the email batch (not errors from the scheduled job itself). There are two cases when an exceptions file can be created:

1. If there are any fatal errors, e.g. one of the mandatory Mail Fields is missing, the exceptions file will contain fatal errors. The mail batch will not be created or sent in this case.
2. If there are no fatal errors, the exceptions file will contain warnings, e.g. one of the recipients addresses is empty. The mail batch will be created and sent, but skips the ones with warnings.

If you only want to receive the notification when there are errors or warnings, check on Attachment Required.

The next section, Building the Mail Query, describes how to construct the mail query.

Building the Mail Query

Start by creating a normal query that has all the fields you want to send in the attachment, these are known as the Attachment Fields. Don't worry that it comes out in one big set as the Scheduler will split it by client / supplier at run time.

Query-----SELECT t2.client_id AS client_id, t2.reg_no AS reg_no, t2.contract_start AS contract_startFROM fm_clients AS t1, fm_contracts AS t2WHERE t1.name LIKE 'L%' AND t2.client_id = t1.client_id

If you want to attach a supporting file to the email, add a specially named field called attachment at the begining of the Attachment Fields, like this:

Query-----SELECT '' AS _attachment_, t2.client_id AS client_id, t2.reg_no AS reg_no, t2.contract_start AS contract_startFROM fm_clients AS t1, fm_contracts AS t2WHERE t1.name LIKE 'L%' AND t2.client_id = t1.client_id

Finally, you need to add the fields used to create the emails, these are known as the Mail Fields. They are added to the SELECT statement before the attachment field. Add any new table joins as required. One of the Mail Fields, called mailgroupby, is used to group the data into individual emails, the scheduler splits the data on this field. So you must also change the ORDER BY statement to sort by the mailgroupby field first.

Warning: You MUST change the ORDER BY statement to sort by the mailgroupby field first

The SELECT statement now comprises of two 'sections', the Mail Fields to the left of attachment, and the Attachment Fields to the right.

Note 1: the query can contain Derived Fields and Embedded Fields.

Note 2: Store the email details in gb_templates and join the record into the query. This keeps the query down to a manageable size, keeps email details stored in the one table and makes minor edits to the email text easier.

Here is an example that you can try in your Query Browser:

Query-----SELECT t1.client_code AS 'mail_group_by',t1.contact1_email AS recipient_1_to_address, t1.contact1_name AS recipient_1_to_name,t3.mail_sender_address AS sender_address, t3.mail_subject AS subject, t3.mail_message AS message,'client_contract_list' as attachment_name,'' AS _attachment_,t2.client_id AS client_id, t2.reg_no AS reg_no, t2.contract_start AS contract_startFROM fm_clients AS t1INNER JOIN fm_contracts AS t2 ON t2.client_id = t1.client_idINNER JOIN gb_templates AS t3 ON t3.name = 'inventory_report'WHERE t1.name LIKE 'L%'ORDER BY mail_group_by ASC

Examples of how the output will be displayed is below:

Sample Output-------------Field names:mail_group_by | sender_address | subject | message | recipient_1_to_address | attachment_name | first_name | _attachment_ |client_id | reg_no | contract_start3 Data rows:LS3 | support@catch-e.com.au | Contract List | Dear {first_name}, please find attached your Contract List | pkent@lister.com |client_contract_list | Peter | | 100109 | YSC52S | 2004-03-05LS3 | support@catch-e.com.au | Contract List | Dear {first_name}, please find attached your Contract List | pkent@lister.com |client_contract_list | Peter | | 100123 | ZKX622 | 2004-07-01LR1 | support@catch-e.com.au | Contract List | Dear {first_name}, please find attached your Contract List | jsmith@pos.com.au |client_contract_list | Judith | | 105831 | 1CBR312 | 2005-09-21

Running this in a mailQuery scheduled job, would generate these two emails:

Email 1

Email 2

Mail Fields

These are the fields to the left of the attachment field in the SELECT statement of your query. They are used to create each email in the batch. The mailgroupflag field determines when the next email has been reached.

Most of these fields are manadatory.

• mailgroupby (Mandatory) — A change in value here, determines when the next email has been reached. LRS
• sender_address (Mandatory) — Addess of sender (you). support@catch-e.com.au
• sender_name (Optional) — Name of sender.
• subject (Mandatory) — Subject. Contract List
• message (Mandatory) — Message. Note: This example contains the Embedded Field {firstname} or alternatively #firstname#. Dear {first_name}, please…
• message_format (Optional) — Message format can be either 'text' or 'html', defaults to 'text' 'text'
• attachment_name (Mandatory (if _attachment_ is in use)) — File name of the attachment. You can use the same attachment name for all reports, E.g. 'clientcontractlist' as attachmentname. Alternatively, you can use a field reference as the attachment name E.g. t1.regno as attachmentname. This could be concatenated to be more descriptive. E.g. CONCAT('Your Reg ',t1.regno) as attachmentname. clientcontract_list
• recipient1toaddress (Mandatory) — Address of the recipient. There has to be a minimum of one recipient. See the Recipient Fields section below for more details. clientcontract_list
• replytoaddress (Optional) — Alternative reply to address, if not set will reply to sender_address. This feature is currently disabled (Ref:3676)
• replytoname (Optional) — Alternative reply to name. This feature is currently disabled (Ref:3676)

Note: Standard footer text is automatically added to the end of the message, unless you turn off the Mail Footer? checkbox parameter when setting up the Job. See Attachment Fields below.

Recipient Fields

You can have as many recipients as you like. Each recipient is given a number and must contain the 'address' field.

Here is the standard layout for a recipient field:

recipient#XX_FFFFFF

Where:

Example:

SELECT t1.contact1_email AS recipient_1_to_address, t1.contact1_name AS recipient_1_to_name, 'yes' AS recipient_1_to_receipt_flag,t1.contact2_email AS recipient_2_cc_address, 'no' AS recipient_2_cc_receipt_flag ...

This would generate one To: recipient, e.g. "Gray Quinn"gray.quinn@catch-e.com.au with a read receipt prompt, and one Cc: recipient ian.rawlings@catch-e.com.au without a read receipt prompt.

If you are using a messageformat of 'html', you might find it easier to create a gbtemplates record and get the message from there.

Embedded Fields

Embedded fields are used to add dynamic content to the email subject and message fields. For example, instead of beginning the email message "Dear Sir," you might want to personalise it with "Dear Judith," where "Judith" comes from the givenname field from fmdrivers.

To do this you must first add the given_name field to your SELECT statement e.g.:

SELECT ... t1.given_name AS first_name, ...

This MUST be placed to the left of the attachment field.

Now you can embed the field into the message field as follows:

SELECT ... 'Dear {first_name}, please find attached your Contract List' AS message, ...

You can also use the hash character for field delimiters e.g. #first_name#

Template Attachments

Attachments can be added to a mailQuery by adding a field called templateidattachments to the selection query.
Point this field to the record that's being used to deliver the email sender and content details.

AS template_id_attachments,

The template attachments should be set-up like other system emails, which means creating a record for each required attachment and linking it to the record being used in the mailQuery.

Attached files can be viewed anytime from the Mail Queue "Batch-Item" email pop-up.

If an event was also created, the template attachments can be found in the Contracts / Events ** or Drivers / Events ** email pop-up for the selected record along with any report attachment that was created.

Refer to Email Attachments for further general information about template attachments.

Troubleshooting tips

The template attachments are recalled from the location of the stored source file (see xxx). If this source file is changed, the Mail Query or Event record will display the current file, not the one that was sent.
If the setting is changed to 'inactive' or the attachment location is missing or broken, the email will still send without the template attachment.

If the scheduler jobs "Auto Send Emails" checkbox is un-checked and emails fail, check the of the stored source file.

Query Attachments

A mail query can include an attached report. Add in a blank column called attachment.
The fields that are listed in the query on the right hand side of attachment will be in the report attachment in the order they are listed in the query.

If you do not want to include a report attachment to the email, simply leave attachment off the query.

-- Query attachment fields start here '' AS _attachment_, t3.client_id AS 'Client ID', t1.reg_no AS 'Registration No', DATE_FORMAT(t1.reg_renewal, '%D %M %Y') AS 'Reg Renewal'

The query attachment can have as many fields as you like. However, all the field names in the query must be unique, including the Mail Fields. If any fields you want in the attachment will be are duplicates of other fields used in the query (E.g. regno is a selection column and it's also listed in the query fields), restate one of the columns with an alias to make them unique (e.g. regno AS 'Registration No').

Warning: Also remember that you cannot use the field name record_id as it is a reserved word.

Like other emailed attachments, these will be converted to URLs if they exceed your set size limit.

Report attachments can be viewed anytime from the Mail Queue "Batch-Item" email pop-up.

If an event was also created, the report attachment can be found on the Contracts / Events ** , Drivers / Events ** or Client / Events ** email pop-up for the selected record along with any static attachments that were created.

It's also stored and accessible from the Contracts / Events **,Drivers / Events ** or Client / Events ** column for the selected record.

Report file type

The default file output type is Comma Separated Variable (CSV), though other options are available such as Tab separated. See the Parameters section below:

Required parameters

The following parameters included in the mailQuery are required for event creation and should be included to the left of the attachment field in the SELECT statement of your query.

See also Contract Event Email.

Contract Events

If you are creating a 'Contract' event, the following values can be configured in your query to populate into new event records created by the job.

Use the "Query Field" below in the query to identify the field you are configuring. If the field is not specified in your query, the "Default Value" will be used.

If you don't want users to create events like this from the Contracts / Events tab, make sure the Posting Classes / Details "System Event" check box is checked.