TECH SUPPORT

Event-Based Conditions

Follow

File Exists

image1195.jpg

 

This condition will cause the schedule to be executed if a file exists.

 

If set to True, the schedule will be executed if the file exists e.g. when it is created.

 

If set to false, the condition will be executed if the file does not exists e.g. if it is deleted.

 

 

Simply browse for the file.

File Has Been Modified

image1196.jpg

 

This condition will cause the schedule to be executed if a file has been modified

 

If set to True, the schedule will be executed if the file has been modified e.g. when it has been amended or updated.

 

If set to false, the condition will be executed if the file has not been modified.

 

 

Simply browse for the file.

 

Database Record Exists

image1197.jpg

 

This condition will cause the schedule to be executed if a database record exists.

 

If set to True, the schedule will be executed if the record exists e.g. when it is created.

 

If set to false, the condition will be executed if the record does not exist.

 

DSN Name:  Drop down and select a DSN.  

  • We strongly recommend System DSNs so that the DSN is visible to SQL-RD even when the user associated with the DSN is logged off.  

  • All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

  • When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in SQL-RD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the SQL-RD NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

  • When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID SQL-RD should use to log on to the database

 

Password:  Enter the password associated with the above user.

 

image1198.jpg

 

image1199.jpg

 

 

 

Query:  Type or paste a query into this field.  Alternatively, click the "Build" button to go to our intuitive and easy to use query builder.  For more information on using "Get Values from Database" module to build queries, click here.

 

image1200.jpg

 

Hint:  If you select "New Records Only", the schedule will only act on records that were created after you wrote the schedule.  And the schedule will only be fired once for each record.  After the first run, it is deemed not to be new any more and the schedule will not fire.  

 

If you choose "Any Matching Records" you need to be very careful about ensuring that your query limits the returned values to just the record you are after.  You must also write a custom action to update the database so that this record no longer fulfills the condition once the schedule has been executed.

 

 

 

For example:  

 

The schedule should execute when a new sale over $1000 is entered.:

  • Select orderid from salestable where amount >= 1000

 

Now limit the query further by adding a second condition:

  • Select orderid from salestable where amount >= 1000 and schedulesent <> 'T'

 

The schedule will only execute if it finds a sale over $1000 and the schedulesent field is not "t".

 

Now write a custom Task to update the database record and set schedulesent to 'T'

  • update salestable  set schedulesent = 'T' where amount >= 1000 and schedulesent <> 'T'

When the schedule has executed, it will mark the record and thus ensure that the schedule does not repeat itself each time SQL-RD polls the database.  For more information on creating Custom Tasks, click here.

 

 

Note.  ChristianSteven Software does not provide training or support for writing SQL queries.  The facility is provided for users who already have a basic knowledge of boolean algebra.  For more information on building queries, please speak to your database administrator.

 

Consultancy is available as well. Please contact your representative for more information.

Database Record Has Been Modified

image1201.jpg

 

This condition will cause the schedule to be executed if a database record is modified in any way.

 

If set to True, the schedule will be executed if the record has been modified.

 

If set to false, the condition will be executed if the record has not been modified.

 

DSN Name:  Drop down and select a DSN.  

  • We strongly recommend System DSNs so that the DSN is visible to SQL-RD even when the user associated with the DSN is logged off.  

  • All communication to databases (including Access databases) is done through ODBC, so you must setup a system DSN to the database before you can use this facility.

  • When setting up DSNs we recommend (if possible) that you use Windows Authentication (Trusted Connection).  This ensures that your username and password are not required to be stored in SQL-RD (more secure) and overcomes a large number of security restrictions which Windows places on DSNs.   When using Windows Authentication, make sure that the SQL-RD NT service user (or background application service user) has full rights to the database otherwise, though you will connect when you are logged in, automated scheduling will fail if the Service user has not got rights to the database.

  • When setting up a DSN to a SQL server, you must ensure that the "Default Database" setting is set to the database you are connecting to (Windows defaults this to "Master").

 

UserID:  Enter the User ID SQL-RD should use to log on to the database

 

Password:  Enter the password associated with the above user.

 

Click Next to build the record selection query.

 

 

 

Query:  Type or paste a query into this field.  Alternatively, click the "Build" button to go to our intuitive and easy to use query builder.  For more information on using "Get Values from Database" module to build queries, click here.

 

image1203.jpg

 

image1204.jpg

 

You can select how SQL-RD should determine modified records.

 

image1205.jpg

 

The condition has now been setup successfully and you can click OK to continue with the schedule wizard.

Process Exists

image1207.jpg

 

This condition will cause the schedule to be executed if a process exists.

 

If set to True, the schedule will be executed if the process exists.

 

If set to false, the condition will be executed if the process does not exist.

 

Type in the window name, or use the dropdown to pick from a list of currently running processes.

 

*Tip: Try setting up the schedule that will monitor for certain key processes on your machine. If those process are not running then SQL-RD can automatically restart them.

 

Window is Present

image1208.jpg

 

This condition will cause the schedule to be executed if a window is present.

 

If set to True, the schedule will be executed if the window is present.

 

If set to false, the condition will be executed if the window is not present

 

Type in the window name, or use the dropdown to pick from a list of currently open windows.

Unread Email Exists

This is possibly one of the most exciting functionality Event-Based schedules offer.  Users can send report requests to a specified email address.  SQL-RD will read the email, and, if the conditions are met, fire off the specified report.

 

Simply fill in your POP server credentials

 

image1209.jpg

 

 Click on Test. If you have entered them correctly, the green next arrow will become available.

 

image1210.jpg

 

Select the folder that will be monitored.

 

image1211.jpg

 

Specify the condition. Select the email fields to determine what would meet your requirements.

 

Forward the Email:  Forward the email to another address.

 

Redirect the Email: Send the incoming mail directly to another address instead of keeping it in the inbox.

 

Save Attachments:  Pull attachments from the email and save them directly to a folder.

 

Remove Matched mail from Server: Choose to clear the email from the inbox once SQL-RD has processed it.

 

If you do use this function with the condition set to false, then be sure to remove the found email from the mailbox so that the report doesn't get fired off again (each time read emails are found).

 

How to parse the values in the body of an email and insert them into SQL-RD fields

 

For an "Unread Email" event, SQL-RD can read the body text of the email and place selected values into Event-based constants.

 

You can also use  the "Exact Match" option which will match the Case of the email text. In addition, when this option

is used, spaces before and after the phrase are NOT removed and form part of the matching string.

  

Here is an example:

 

Mary has an email which will contain values, and she wants the values to update a database.  Here's how she would set it up in SQL-RD.

 

Firstly, SQL-RD will expect the email text to be in the format [Constant]:[Value].  Here is how Mary will set up her email:

 

 

EmployeeID: xxxxx

EmployeeName:yyyyy

EmployeeStartDate:zzzzzzzz

 

As you can see, the required constant is on the left of the colon (:) and the required data is on the right.

 

To insert the data into her database, Mary uses an "Update a database" custom task as shown below:

 

image1212.jpg

 

She inserts Event-Based Constants, selecting a Custom Constant:

 

image1214.jpg

 

She types in the Custom Field Descriptor:

 

 

 

When she drags the Insert, "<[e]Read my Email ;EmployeeID>" is placed in the field:

 

 

image1216.jpg

 

She clicks image18.gif  to add the statement to the list.  And repeats the process for all the custom constants she will need to parse the email.

 

The above instruction tells SQL-RD to read the email body text and to place the value to the right of "EmployeeID:" in the database.  Note:  The colon (:) is only required in the email body.  It is not used as part of the definition process above, but SQL-RD will automatically read the value to the left of the colon and the apply the data to the right of the colon.

 

 

*Tip: Use email body parsing to customize other tasks! Pull information from one email and send an email using the data. Use it to create new database records, and much more!

 

Data is Received On a Port

image1562.jpg

 

SQL-RD can monitor specified ports for data and then execute tasks such as reports or database updates.

 

Name: Name the Condition.

 

If data has been received: Select true if you wish to monitor for data in the given port, or false if you wish to monitor for the absence of data in the given port.

 

Data is received on the following:

 

Port Number: Enter the port number that will be monitored.

 

Monitor on a  Specific IP address: enter the IP address that will be monitored.

 

 

Received Data

Select the data criteria using the lower menu. SQL-RD will monitor for this data for any data matching your criteria.

 

Select OK to complete the condition.

 

Comments