Scheduled Exports examples

Scenario 1

A births record creation project is under way. As part of the outputs of the project a weekly file is to be produced containing the Registration Number, Child's Given Names, Child's Surname, Date of Birth, Mother's Given Name, Mother's Surname of all records added in the week.

As the file is to be produced weekly, the export frequency required is Daily (as the frequency is less than monthly). The Sunday checkbox should be selected. As no Finish Date is specified, these settings will result in the export running early Sunday morning, once a week, exporting all data from the previous Sunday through to the Saturday.

The Conditions statement needs to query for all records inserted into the Births module in the previous week. As the export is run once a week the #STARTDATE# place holder will be set to the date of the previous Sunday and the #ENDDATE# will be the date of the Saturday immediately before the export is run.

For example, if the export is run on the morning of Sunday, 14 February 2010, then #ENDDATE# will be 13 February 2010 (Saturday) and #STARTDATE# will be 7 February 2010 (the previous Sunday). The required Conditions statement is:

SELECT all
FROM   ebirths 
WHERE  AdmDateInserted >= DATE '#STARTDATE#' 
AND    AdmDateInserted <= DATE '#ENDDATE#'

The Export and Conditions tabs are:

Scenario 2

A quarterly export of all records of deaths where the deceased's age is 18 years or younger is required by the Health Department. The export must list the Registration Number, Deceased's Given Names, Surname, Date of Death and Date of Birth.

Since the export is required every three months, the Monthly export frequency should be chosen. The Month Interval field should be set to 3 to ensure the export only occurs once every three months (that is, quarterly). Set The Day of the Month to 1 as we want the export to include all records up to but not including the first day of the month. Finally the Start Date needs to be set to the first day of a quarterly date boundary. If our quarters start on 1 January, 1 April, 1 July and 1 October, then one of these dates should be chosen.

The Conditions statement needs to retrieve all records where age at death is 18 years or younger. As there are no date restrictions, we do not require a date range as part of the statement. A suitable Conditions statement would be:

SELECT all 
FROM   edeaths
WHERE  DeceasedAge <=18

The Export and Conditions tabs are:

Scenario 3

In order to send a copy of the change in the Marriage Act to all Marriage Celebrants, an export file containing mailing addresses is required. The report is only sent to Marriage Celebrants registered on 31 December in the given year. The export should produce a CSV file.

Since we are dealing with people, the Scheduled Export needs to be created in the Parties module. The data is generated annually, so the Annually export frequency should be used. The Day & Month of the Year should be set to 1 January. As the export is run early in the morning on 1 January, it will include all records up to and including 31 December. As CSV (Comma Separated Values) output is required, the Filter should be set to CSV Format (iso-8899-1).

The Conditions statement needs to find all Marriage Celebrants registered on 31 December. We only need to find Parties records containing a Type of Marriage Celebrant. A suitable Conditions statement is:

SELECT all 
FROM   eparties 
WHERE true and
(
	not
	(
		NamOrganisation contains 'cancelled'
	)
)
and
(
	NamPartyType contains 'MarriageCelebrant'
)

The required Export and Conditions tabs are:

Tip: In this example the Conditions statement was copied from the Show Search dialogue box. See Hint - Determining the TexQL Conditions statement for more details.

Scenario 4

From time to time the Cancer Council requests a data file containing a list of all deceased persons who died of cancer.

The data exports are not required on a regular basis, so the Adhoc export frequency should be used. As a schedule is not defined for Adhoc exports you will need to run an unscheduled export to produce the required data file. See How to run Unscheduled Exports for more details.

The Conditions statement needs to retrieve all cancer related deaths. The Cause of Death field allows us to find the required Deaths records. A suitable Conditions statement would be:

SELECT all 
FROM   edeaths
WHERE ImmediateCauseOfDeath CONTAINS 'Cancer'

The required Export and Conditions tabs are: