...
Future student activities can be used in conjunction with a SQL stored procedure to generate a report or dataset used to produce letters or merged documents specific to the activity code. The procedure updates the activity records to indicate when the letters were produced and also outputs a dataset that contains the future student contact information.
Activtity Activity letters can be produced in bulk by the following means:
- From an MS Query/Mail merge based on crspFutureActivityPrint
- Stored Procedure - Using a stored procedure crspFutureActivityPrint to produce a Crystal Report or MS Query/Mail Merge. The benefit of using the stored procedure is that it will back-update the activity records with the mailed date and mailed by (logged in user) fields. This procedure cannot be used with the Print button in Future Student Maintenance > Activities tab.
- View - From a custom Crystal Report based on crspFutureActivityPrintCalling on vFutureStudentActivities and calling the report from the Future Student either Crystal Reports Available menu Linking the report to the 'Print' or using the Print button in Future Student Maintenance > Activity tabActivities tab. The benefit of using this method is that the Print button can be used from the Activities tab in the maintenance programs (eg. Future Student Maintenance).
Activity Configuration
Generally letters may be produced for letter of offer or interview bookings, the following example will set up a code for "Letter of Offer" that can be used to flag students to receive the offer letter.
...
| Field | Vaue |
|---|---|
| Code | OFFER (or other code as required) |
| Description | Letter of offer (or other description as required) |
| MailRequestFlag | Checked - this automatically sets the "Mail Request" flag when adding new activities and the stored procedure specifically only includes activities with this ticked.
|
| ReportCode | If you have created a Crystal Report that uses stored procedure crspFutureActivityPrint then needs to be linked to the Print button of the Future Student Activity tab then enter the report code can be entered here. This is used by the Print buttonhere. The report must be based on a view that contains the field "ActivitySeq", as the Print button will automatically filter on the selected Activity sequence. |
See luActivity configuration documentation for more information.
...
All fields from vFutureContactAddress, activity information, activity follow up details (staff, dates etc). The data returned should be sufficient for generation of a mail merge or Crystal Report that generates letters specific for the future student and addressed to their contacts. If there are multiple contacts with the Mail Flag ticked (Future Student Maintenance > Contacts tab) then a records is returned in the dataset for each contact - for example split families requring a letter to each parent.
Parameters:
| Name | Type | Description |
|---|---|---|
| @Activity | VARCHAR(15) | Activity code as created in lookup table luActivity |
| @MailedDate | DATETIME | Leave as blank to pick up all activities that do not already have a "MailedDate". By default the procedure will return all records with a blank mailed date as well as all records that have a mailed date of the current date (ie. letters were generated today but perhaps need to re-run the merge). |
| @IgnoreMailFlag | BIT | If set to 0 then the stored procedure will only return future student contacts that have the Mail Flag ticked. This is not available from the 'Print' option on the activities tab. |
| @ActivityCreatedBy | VARCHAR(30) | Optional filter to only include activities created by a specific user. This is not available from the 'Print' option on the activities tab. |
| @MailedBy | VARCHAR(50) | Optional override of the Mailed By user name. This is not available from the 'Print' option on the activities tab. |
Adding Activities
In Future Student Maintenance > Activities tab, press Add
...
Extracting Data for Activity Letters
MS Query
This provides the most flexibility over the letter content, as it can be changed on the fly.
Call the stored procedure directly and specify the required parameters.
Here is a sample call:
| Code Block |
|---|
exec [dbo].[crspFutureActivityPrint]
@Activity = 'OFFER',
@MailedDate = NULL,
@IgnoreMailFlag = 0,
@ActivityCreatedBy = '',
@MailedBy = null |
This will pick up all activities with code of "OFFER" that have not already been marked as mailed (with mailed date). It will return ALL activities and a record for all contacts that have the mail flag ticked for the future student that the activity was added to.
Below is a sample result set:
| Code Block |
|---|
OFFER 2018-02-14 00:00:00.000 C 11004 Abdullah Aden Mr Aden Dukker Mr A Abdullah sa (... +all other fields from vFutureContactAddress)
OFFER 2018-02-14 00:00:00.000 C 11004 Abdullah Aden Mr Aden Dukker Mr A Abdullah sa
+records for every activity and corresponding future student contact with mail flag ticked. |
Crystal Report - via Crystal Reports Available
Create a Crystal Report based on crspFutureActivityPrint then create a custom selection screen to match the parameters required by the stored procedure and any other optional paremeters needed to be passed in for formatting or further data filtering.
Crystal Report - via Print button
...
Create a Crystal Report based on a view that contains the following fields:
ActivitySeq