TECH SUPPORT

Insert Function

Follow

Inserts is arguably one of the most versatile features in SQL-RD. This feature enables the advanced schedule types to work, deals with variety of parameters, and customizes outputs for recipients.

 

The insert function is activated in two ways:

 

1. SQL-RD will automatically launch the insert module at points where it can be used. (e.g. in a wizard's parameter screens)

2.  In the field into which you wish to insert a function, right-click and select "Insert".

 

image1514.jpg

 

 

The following types of constants are available:

 

  • SQL-RD Constants (@@SQL-RD Constants)

  • User Defined Constants (@@User Defined Constants)

 

SQL-RD Constants:

To use SQL-RD constants, select "@@SQL-RD Constants" from the "Type" list and then select the constant you want from the resulting "Name" list.  Here is a list of constants to choose from:

  • CurrentDate:  Returns the current date

  • CurrentTime:  Returns the current time

  • CurrentDateTime:  Returns the current date and time

  • CurrentMonth:  Returns the current month number

  • Current Year:  Returns the current year

  • CurrentDay:  Returns the current day date

  • CurrentWeekDayName:  Returns the day name of the day the schedule is run

  • CurrentScheduleName:  Returns the name of the current schedule or package

  • MondayThisWeek:  Returns the date for this Monday

  • TuesdayThisWeek:  Returns the date for this Tuesday

  • WednesdayThisWeek:  Returns the date for this Wednesday

  • ThursdayThisWeek:  Returns the date for this Thursday

  • FridayThisWeek:  Returns the date for this Friday

  • SaturdayThisWeek:  Returns the date for this Saturday

  • SundayThisWeek:  Returns the date for this Sunday

  • MondayLastWeek:  Returns the date for last Monday

  • TuesdayLastWeek:  Returns the date for last Tuesday

  • WednesdayLastWeek:  Returns the date for last Wednesday

  • ThursdayLastWeek:  Returns the date for last Thursday

  • FridayLastWeek:  Returns the date for last Friday

  • SaturdayLastWeek:  Returns the date for last Saturday

  • SundayLastWeek:  Returns the date for last Sunday

  • MonthStartLastMonth:  Returns the date of the first day of last month

  • MonthEndLastMonth:  Returns the date of the last day of last month

  • EndLastYear:  Returns the date of the last day of last year

  • StartThisYear:  Returns the date of the first day of this year

  • YearStartLastYear:  Returns the date of the first day of last year

  • Exported File Name:  Returns the name of the exported file

  • Key Parameter Value: Returns the current key parameter value in use by a dynamic schedule

 

For certain SQL-RD constants, you can adjust the constant's value by a certain increment (days, hours, minutes etc).

 

image1585.jpg

 

A key use for this feature is in date/time parameters. For example:

 

image1396.jpg

 

You can adjust a single date value by years.

 

image1397.jpg

 

 

User Defined Constants:  To use User Defined Constants, select "@@User Defined Constants" from the "Type" list and then select the constant you want from the resulting "Name" list.  Or select "New" to create a new constant.  You can insert other constants and create a new formula of your own.

 

Crystal Constants:  For more information on Crystal Constants, please look in your Crystal Reports help file or handbook.

 

Crystal Report Fields: When enabled for the report, use these to insert the value of the Crystal Report Field in the schedule. See Crystal Report Fields

 

Event-Based Data:  Used with event-based schedules.  Data from the database record or email body which triggered off the event can be inserted into emails, reports etc. For instructions on how to parse the body of an email, see the last section of this page.

 

Data-Driven Data: Used with data-driven schedules. Data from the data driver can be inserted in all possible areas to drive the schedule. For more information on Data-driven schedules, please click here.

 

Parameters:  Use these to insert the current report parameter value into an email, report name, etc.

 

 

Database Field:  Construct a SQL statement to collect the information from a database field.  Selecting this option will bring up the "Data Items Module".  Here you can pick an already existing SQL script, or construct a new one to query a database and return a specified value.  For more information on the Data Items module, click here.

 

Default Subject:  This will insert the wording in the Default Subject field from the User Defaults menu (in Options).  For more information on User defaults, click here.

 

Default Message:  This will insert the wording in the Default Message field from the User Defaults menu (in Options).  For more information on User defaults, click here.

 

Default Signature:  This will insert the wording in the Default Signature field from the User Defaults menu (in Options).  For more information on User defaults, click here.

 

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.  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:

 

image1398.jpg image1399.jpg

 

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

 

image1400.jpg

 

She types in the Custom Field Descriptor:

 

image1401.jpg

 

When she clicks Insert, "<[e]Incoming ;EmployeeID>" is placed in the field:

 

image1403.jpg

 

She clicks  image9.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.

 

 

Comments