Query — Driver Events

Driver Events

The following parameters apply if the Event Table "Driver" is selected in the mailQuery job configuration.

When the mailQuery job is run, the system date is used as the event "Date" for any new record that is created.

Configurable Fields

Name	Type	Default Value	Description
event_driver_id	Mandatory	Automatic	The driver id the event should be assigned to
event_value	Optional	NULL	Value for the Event Value field
event_description	Optional	Selected posting class description	Value for the Event Description field
event_due_date	Optional	NULL	Value for the Event Due Date field
event_completed_flag	Optional	yes	The default value of 'yes' is populated into this field. This means the "Completed" check box of the event will be checked on creation

Client Events

The following parameters apply to events if the selected "Event Table" is 'Client' in the job configuration.

When the mailQuery job is run, the system date is used as the event "Date" for any new record that is created.

Configurable Fields

Name	Type	Default Value	Description
event_client_id	Mandatory	Automatic	The client id the event should be assigned to
event_value	Optional	NULL	Value for the Event Value field
event_description	Optional	Selected posting class description	Value for the Event Description field
event_action_id	Optional	None	Value for the Event Action field
event_due_date	Optional	NULL	Value for the Event Due Date field
event_last_edit	Optional	Last Edit	Last edit date/time
event_user_id_edit	Optional		You can overwrite this record with a particular user's record, so their name will be displayed in the Clients / Events tab and the event will display in their KPIs
event_completed_flag	Optional	yes	The default value of 'yes' is populated into this field. This means the "Completed" check box of the event will be checked on creation

Non-Configurable Fields

Name	Default Value	Description
attachment_id	NULL	This will be populated if a report attachment is created, otherwise it will be NULL

Event Validation

The "Event Type" field is blank for new records. If you forget to select this or remove it from an existing job and the "Create Event" check box is selected, a warning will appear.

Scheduler Behaviour Where 'Auto Send Emails' = 'OFF'

'Create Event' Flag OFF

Under this setting, the expectation is the following:

Emails generated from the query are allocated a batch_no
Sent to the Mail Queue with 'pending' status
No driver event is created

It will then be up to the user to select the created batch_no and manually send the emails from the Mail Queue. Once complete, the status of the batch will be updated to 'sent'.

'Create Event' Flag ON

Under this setting, the expectation is the following:

Emails generated from the query are allocated a batch_no
Sent to the Mail Queue with 'pending' status
One driver event is created with icon

Email will not be able to be sent from the Drivers / Event tab as it is in 'pending' status. The 'Send' option will disappear.

Mail Query for Channels

Mail queries can be set up to send channel specific content. The two types of channel specific content are:

Channel specific logo on Office (xls) report attachments
Channel specific content for the subject and body of the email

To use the channel specific logo on an Office (xls) attachment simply include the field channel_id in the SELECT part of the mail query to the left of attachment if you don't want this field on the report.

To use channel specific mail content from gb_templates simply include channel_id in the table join to gb_templates.

Warning: If gb_templates records have been set up for channels you MUST include channel_id in the table join otherwise you will get duplicate mail.

Example Mail Query for Channels

SELECT t1.contract_id AS mail_group_by, t2.home_email AS recipient_1_to_address, t4.mail_sender_name AS sender_name, t4.mail_sender_address AS sender_address, t4.mail_subject AS subject, t4.mail_message AS message, t4.mail_message_format as message_format, t1.contract_id AS event_contract_id, 'Email to driver' as event_description, t1.contract_id, t1.reg_no, t1.description, t2.given_name AS first_name, DATE_FORMAT(t1.reg_renewal, '%D %M %Y') AS 'reg_renewal', t1.contract_id AS contract, t1.reg_no AS registration, t1.description AS contract_description, t2.given_name, -- Note: Assuming the template_id is itself linked to gb_template_attachments, -- those fixed attachments will be included in the outgoing email t4.template_id as template_id_attachments, -- Note: channel_id only needs to be included when an Office(xls) report -- attachment is being created and you want it to use a channel specific logo t3.channel_id, -- Note: Each attachment produced by the mailQuery can be named by including -- attachment_name in the mailQuery CONCAT('Your Reg is: ', t1.reg_no) as attachment_name, -- Report attachment fields start here '' AS _attachment_, t3.client_id AS client_id, t1.reg_no AS reg_no, t1.contract_start AS contract_start FROM fm_contracts AS t1 INNER JOIN fm_drivers AS t2 ON t2.driver_id = t1.driver_id INNER JOIN fm_clients AS t3 ON t3.client_id = t1.client_id -- Note: channel_id must be included in the join condition if additional gb_templates -- records exist for channels otherwise the mail will double up INNER JOIN gb_templates AS t4 ON t4.name = 'pdf_contract_event_email_message' AND t4.sub_name = 'REGDUE' AND t4.channel_id = t3.channel_id WHERE t1.reg_renewal = DATE_ADD(CURDATE(), INTERVAL 45 DAY) AND t2.home_email != '' AND (t1.suspend_date = '0000-00-00' OR t1.suspend_date > DATE_ADD(CURDATE(), INTERVAL 45 DAY)) ORDER BY t2.driver_id ASC;

System Errors

Details about error codes and troubleshooting information.

Error Code	Error Name	Notes
1	CATCHEEVENTKEYNOT_DEFINED	When the query linked to the job is missing one of the event key fields (event_contract_id, event_driver_id, or event_client_id)
2	CATCHEEVENTMULTIPLEKEYS	When the query linked to the job contains more than one of the event key fields (event_contract_id, event_driver_id, or event_client_id)

Troubleshooting

Visit the main Troubleshooting page for a list of all the available problem-solving tips.