Scheduled Exports examples

Tip: See Specifying the TexQL Conditions Statement for a simple method for generating TexQL.

Scenario 1

A record creation project is under way. As part of the outputs of the project a weekly file is to be produced containing the Accession Number, Title and Valuation 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 Catalogue 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 2018, then #ENDDATE# will be 13 February 2018 (Saturday) and #STARTDATE# will be 7 February 2018 (the previous Sunday). The required Conditions statement is:

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

If you were to use the technique for generating the TexQL described previously, you'd enter the following in the Insertion Date: (Insertion Details) field:

Placeholders

and the TexQL statement generated by EMu will appear as shown below.

The Export and Conditions tabs are:

Export Properties

Scenario 2

A quarterly export of all objects with a valuation of at least $500.00 is required by your insurance company. The export must list the Accession Number, Title, Valuation Amount and Date Valued.

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 with a valuation of at least $500.00. 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   ecatalogue
WHERE  ValValuationAmount >= 500.00

The Export and Conditions tabs are:

Export Properties

Export Properties

Scenario 3

In order to send a copy of the Annual Report to all Financial Contributors to your institution, an export file containing mailing addresses is required. The report is only sent to Financial Contributors registered on 30 June 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 July. As the export is run early in the morning on 1 July, it will include all records up to and including 30 June. 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 Financial Contributors registered on 30 June. We only need to find Parties records containing a Role of Financial Contributor. A suitable Conditions statement is:

SELECT all 
FROM   eparties 
WHERE  EXISTS 
(
       Role_tab WHERE Role CONTAINS 'Financial Contributor'
)

The required Export and Conditions tabs are:

Export Properties

Export Properties

Scenario 4

You work in the ornithology department of a natural history museum. From time to time you receive requests from your local environmental authority asking for a data file containing a list of all specimens you hold for a given taxon. The data must be supplied in Darwin Core XML format.

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 an Unscheduled Export for more details. In order to produce the correct output format the Darwin Core XML filter should be used.

The Conditions statement needs to retrieve all specimens for a given taxon. The Darwin Core DarScientificName field allows us to find the required Catalogue records. A suitable Conditions statement would be:

SELECT all 
FROM   ecatalogue 
WHERE  DarScientificName CONTAINS '#TAXON#'

We have added our own #TAXON# place holder in the Conditions statement. Before running an export we need to replace the place holder with the required taxon (simply edit the TexQL on the Conditions tab, replacing #TAXON# with the desired taxon). The required Export and Conditions tabs are:

Export Properties

Export Properties