Overview

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.

Activity letters can be produced in bulk by the following methods:

1. 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. 
2. View - From a custom Crystal Report based on vFutureStudentActivities and calling the report from either Crystal Reports Available menu or using the Print button in Future Student Maintenance > Activities 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.

Firstly the lookup table luActivity needs to be configured as follows:

See luActivity configuration documentation for more information.

Adding Activities

In Future Student Maintenance > Activities tab, press Add

Select the required Activity Code and ensure that the Mail Request flag is ticked.

Optionally populate the status, follow up fields and comment.

For more information: Future Student Maintenance - Activity tab

Extracting Data for Activity Letters using crspFutureActivityPrint 

One method of extracting acitivity data and contact information is using a stored procedure via MS Query or Crystal Reports. This would normally be used to bulk-generate letters and not used for individual letters via the Print button in Future Student Maintenance.

Stored Procedure explained - crspFutureActivityPrint 

The stored procedure crspFutureActivityPrint is not used by the Print button on the Activity tab of Future Student Maintenance. It can be used directly by MS Query or Crystal Reports to extract data for letters and update the Mailed Date/By fields after report generation.

Stored procedure: crspFutureActivityPrint 

Description: Return all Future Student Activities which have not been previously printed. Mail Request Flag must be on for the activity (luFutureActivity)

Query update: The FutureStrudentActivity table is updated with MailedBy and MailedDate for any activities matching the activity code and have Mail Request ticked. It will only update records where the MailedDate is blank.

Dataset output:

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:

Extracting data with 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:

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:

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.

Extracting Data with a 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.

Extracting Data for Activity Letters using the Print button

To generate individual letters on the fly from a Crystal Report based on activities.

Crystal Report Requirements

Create a Crystal Report based on a view that contains the following fields: