How To: Use Workflows


WARNING icon
 Workflows is an Advanced User feature.

Overview

Workflow Reports automatically run SQL Reports in the Data Query screen and the result can then be:

  • Attached to an e-mail template and sent to a distribution list
  • The report can be sent to an FTP/SFTP location for another process to pick up.

The automation can be setup to run at intervals so the system can provide:

  • Feedback on data which is not configured properly.
  • Reports which are time-sensitive to run

Examples of Use

The following are examples of how workflows can be used:

  • You could setup the system to drop a stock catalogue file for a drop-shipment customer.
  • Email the purchasing department to let them know of back-order stock requirements.
  • Remind customers that bought a product that needs reordering.
  • Send out safety data sheets to customers that purchase specific products.
  • Remind customers that they have a proforma order that needs payment.
  • Automatically emailing new contacts to welcome them.
  • Automatically emailing customers to ask them to review your services.
  • Stock levels sent to a trade customer

Where to Start

There are three main steps to using workflows:

  1. SQL, see below.
  2. Create the Email Message Template, this is covered on How To: Setup Email Templates.
  3. Configure the Workflow, see below.

Example SQL

The following SQL will show any quotes that are between 30 and 80 days old.

/*All pending quotes that are at least 30 days old.*/
DECLARE @MIN_DAYS_OLD INT = 30;
DECLARE @MAX_DAYS_OLD INT = 80;
SELECT 
co.company_id,
invcon.contact_id,

SO.SORDER_CODE
 ,CO.COMPANY_NAME
 ,DATEDIFF(D, SO.DATE_CREATED, GETDATE()) AS 'QUOTATION_DAYS_OLD'
 ,(
 (
 CASE INVCON.TITLE
 WHEN NULL
 THEN ''
 WHEN '.'
 THEN ''
 ELSE INVCON.TITLE
 END
 ) + CASE INVCON.FORENAME
 WHEN NULL
 THEN ''
 WHEN '.'
 THEN ''
 ELSE (' ' + INVCON.FORENAME)
 END + CASE INVCON.SURNAME
 WHEN NULL
 THEN ''
 WHEN '.'
 THEN ''
 ELSE (' ' + INVCON.SURNAME)
 END
 ) AS 'CONTACT_NAME'
 ,CASE INVCON.EMAIL
 WHEN NULL
 THEN INVADDR.EMAIL
 WHEN ''
 THEN INVADDR.EMAIL
 ELSE
 INVCON.EMAIL
 END AS 'EMAIL_ADDRESS'
FROM SORDER AS SO
INNER JOIN COMPANY AS CO ON CO.COMPANY_ID = SO.COMPANY_ID
INNER JOIN ADDRESS AS INVADDR ON INVADDR.ADDRESS_ID = SO.INVADDR_ID
INNER JOIN CONTACT AS INVCON ON INVCON.CONTACT_ID = SO.INVCONTACT_ID
WHERE SO.SOTYPE_ID = 3
 AND SO.DATE_CREATED <= (GETDATE() - @MIN_DAYS_OLD)
AND SO.DATE_CREATED >= (GETDATE() - @MAX_DAYS_OLD)
AND (SO.QUOTE_DATE <= '30-DEC-1899' OR SO.QUOTE_DATE IS NULL)
/************************************************************
The date range can be changed in lines 1 and 2 by changing the numbers:
DECLARE @MIN_DAYS_OLD INT = 30;
DECLARE @MAX_DAYS_OLD INT = 80;
At the moment they are between 30 and 80 days.

This data query is provided as a teaching aid and is not
 covered by your support agreement
************************************************************/

To create and run the report:

  1. Create a new query by dragging one or more rows from the field grid into the scripting pane; or
  2. Directly enter a SQL query into the scripting pane (see below for examples); or
  3. Copy and pate SQL into the scripting pane.
  4. Load a previously saved query (from the Reports Pane) into the scripting pane.
  5. The name of the currently loaded query is displayed at the top of the Data Query window prefixed with 'Reports: '.
  6. Click on the button to run the query.
    Note: the keyboard shortcut is F5.

Results

The Results area displays the total number of rows that have been returned in the grid. The following buttons allow you to export the results:

  • : opens the grid menu's "Print Grid" dialog box.
  • : opens the grid menu's "Send to Notepad" enabling the user to export the grid contents as a .csv file.

Workflow

The Email Trigger dialog is displayed by clicking on the Data Query Report Scheduler button button in the top right of the Data Query and is only available when an SQL query has been loaded into the Scripting area. The dialog enables the user to automate the running of the SQL reports and email internally or externally or upload reports to a specific FTP folder.

For details of the various areas on the dialog, see Email Trigger dialog.
Notes:

  • The Template can be any template of the right kind, though it is advised to make specific templates with specific instructions in them to help when the report triggers after 6 months. This will help to tell the users what to do with the report they just received.
  • The Execute Period is in days, you'll need to work out the fraction of a day if you want more frequently than 1-day periods. Also note this is only when it will be scheduled to be run. There must be a corresponding scheduled task process run alongside it for this to work at all.
  • Last/Next Executed is when the system last ran this report and when it will next make it available to execute.
  • Internal Email Address is the mailing distribution list to send the e-mail to - don't send it to one person because when they leave the business, the monitoring of the report will leave with them!
  • File Name Template allows you to provide a template for the naming of the file if you need it to have a specific naming convention.
  • Sequence Format works with File Name Template to create uniquely named files.
  • Trigger Application should be set to Command Line to give control over the running of the reports, to allow the scheduled task to control when the report runs.
  • Send Immediately most of these reports should be checked with this.
  • FTP if you are sending the report via FTP this is the value to tick.
  • File Format should be set to CSV Quoted if the user wants it in a 'CSV format' as this will ensure that excess commas do not change the layout of the report.
  • Force Blank will make the report run even if it has no content.

For the above SQL, we could setup the workflow so that it sends every 14 days:

Workflow dialog setup

See Also


Did you find this article helpful?