Online Administration

Create SQL Mail Merge

The primary function of the SQL Mail Merge facility is to create a file of exported data on the basis of an SQL Select query. The data may also be used for analysis in Microsoft Excel. Only statements that begin with the word Select are allowed, all other commands such as Update, Insert or Delete are prohibited and will fail validation. Statements separated by a semi-colon will also fail validation. After the file has been created, you can export the data to a network location, or to a local machine. The System Administrator must nominate which users and user groups may initiate the query.

Search for an SQL Mail Merge Query

Log in to One v4 Online and click the Administration button to display the System Admin tab, SQL Mail Merge is the default page. From here you can search for existing queries or add a new query.

Add a New SQL Mail Merge Query

  1. Enter the Query Name, this is a mandatory field.
  2. Select the Context from the drop-down list, this is a mandatory field.
  3. Select the Active check box to activate the query.
  4. Click the Save button to save the SQL Mail Merge definition.

Add an SQL Statement

SQL should only be carried out by a System Administrator who is fully aware of its implications. One cannot accept responsibility for data problems caused as a result of following this advice incorrectly.

  1. Enter the select query in the SQL Statement box (only Select statements are allowed).
    To help you with the query, a list of parameters displays in the Parameters panel. The list is populated when the Context is selected.
  2. If required, enter a Parameter value.
  3. Click the Save button.

Click the Export button to display the Save As dialog. Choose a location to save your file. Click the Save button.

Click the Preview button to display the Query Results on the SQL Mail Merge Preview dialog.

Add Users

  1. Select the Users tab to display the Users list.
  2. Click the Edit button to display the Users browse.
  3. To filter the list, enter a User Name.
  4. Select the check boxes for the users who can initiate the SQL query. If you wish to remove a user from a query at a later date, deselect the check box.
  5. Click the Save button.
  6. Click the Close button to return to the Users list.

Add User Groups

  1. Select the User Groups tab to display the User Groups list.
  2. Click the Edit button to display the Groups browse.
  3. To filter the list, enter a Group Name.
  4. Select the check boxes for the groups who can initiate the SQL Query. If you wish to remove a group form a query at a later date, deselect the check box.
  5. Click the Save button.
  6. Click the Close button to return to the User Groups list.

SQL Mail Merge Button

The SQL Mail Merge button displays in many areas of One v4 Client and One v4 Online. Click the button to display the List of SQL Mail Merges dialog.

A Preview button is available to enable you to view the query results.

The Export button enables you to save the files and export the queries.


 

Related Reference Guides: