Customising export templates

There are a range of options for creating custom exports in onCourse. What you choose depends on your familiarity with database structures, queries and the Groovy language.

1. Copy an existing export to make a new export file, and then make changes to the copy
2. Create a new export from scratch
3. Create a standalone export that is run manually, or an export that is triggered by a script to pass it certain pre-defined parameters
4. Engage ish or Groovy developer of your choice to create a custom export for your business

onCourse exports are powered by a groovy based scripting language. These scripts run on the server and call the same groovy DSL that you’ll find in the scripting chapter and available here: https://www.ish.com.au/onCourse/doc/latest/dsl/. You can use the full power and expression of the groovy language, and have access the entire onCourse schema. In short, you access the data to export from the variable "records" and just return from the script the formatted text to export.

There is a built-in CSV toolkit, so exporting to CSV is the simplest export type to create or edit without a great deal of prior experience. By default the delimiter is a comma, but you can define a different delimiter like a tab as per the example below:

Groovy

csv.delimiter = '\t'

records.each { AccountTransaction t ->
    csv << [
            "Date"          : t.transactionDate?.format("D/M/Y"),
            "Memo"          : t.transactionDescription,
            "Account Number": t.account.accountCode,
            "Debit Amount"  : t.amount.compareTo(Money.ZERO) > 1 ? t.amount.toPlainString() : Money.ZERO.toPlainString(),
            "Credit Amount" : t.amount.compareTo(Money.ZERO) > 1 ? Money.ZERO.toPlainString() : t.amount.toPlainString()
    ]
}

Creating a new export

The simplest place to start with a new export is by finding an already existing export that’s similar to what you want to make and click the Save as new export template button to create a copy of the export. Give it a unique key code, then hit Save and you’ll be taken to your copied export, where you’ll be able to start editing the code.

Default ish exports cannot be edited, they must be copied first.

For example, if I wanted a new export for Tutor data that started from the Contact entity, I’d choose the default 'Contact CSV export' as my starting point.

1. Go to the Automation window, find the export 'Contact CSV export' under Export Templates and click it to open it.

2. Click the Save as new export template button in the top right, you’ll be prompted to give your new export a new key code. This must be unique, but you can make it up. Once you’ve entered a key, then click Save. You’ll be shown your new, editable export. Make sure you give it a memorable name, as this is what displays when you run it.

   

   Figure 347. The Contact CSV Export

   
   

The first part of the export code...

Groovy

records.each { Contact c ->
csv << [

means that in this file, the letter 'c' will be used as shorthand for the 'Contact' object and the export will create a CSV file. For your export to work correctly, do not modify this header information.

Go to the DSL documentation for the entity so you can see the fields, or instance methods names and values. For the contact entity, this information is available at https://www.ish.com.au/onCourse/doc/dsl/#Contact

Each row that follows defines the column name in the CSV export and where that data comes from in onCourse, and how it is formatted. You can add additional rows to the export, or delete or re-order rows. Note that each row ends with the comma delimiter "," except the last row in the export file. For example, the contact export ends with the resume field value, so there is no comma after this row.

Groovy

            "workingWithChildrenCheckDate"  : c.tutor?.wwChildrenCheckedOn?.format("yyyy-MM-dd"),
            "workingWithChildrenCheckRef"    : c.tutor?.wwChildrenRef,
            "workingWithChildrenCheckExpiry"    : c.tutor?.wwChildrenExpiry?.format("yyyy-MM-dd"),
            "tutorResume"                    : c.tutor?.resume
        ]
}

You can also see above that the export file ends with the bracket closure characters "]" and "}". It is important you don’t remove these.

Joins

If you want to access data from other objects than the entity defined in the export, you need to add joins to the export and structure your query in a way that matches the onCourse DSL and schema. For example, in the contact CSV file, some of the fields are from the student or tutor object. These joins are represented by the full stop character. For example, the Working With Children Check Reference number is part of the tutor object, so the join from the contact to the tutor is 'c.tutor.wwChildrenRef' which means the same as 'contact.tutor.wwChildrenRef'.

Groovy

            "workingWithChildrenCheckRef"    : c.tutor?.wwChildrenRef,

In this example, the addition of the ? character is a safe-navigation operator to avoid the export throwing a null pointer exception. Null pointer exceptions occur when the export cannot follow the join you have defined because it doesn’t exist (i.e. not every contact selected for export will be a tutor, so if there is a non-tutor contact included, adding the safe-navigation operator just means the export will skip past this field for that record.)

Custom Fields in Exports

If you have custom fields you wish to include in your export, you can use the custom field keycode to add these fields. You won’t find these fields defined in the onCourse DSL documentation as they are custom to your database. This is why you should make your key’s relevant and memorable.

For example, if you have a contact custom field with the keycode 'howDidYouHearAboutUs' you could add it your export as follows:

Groovy

"How Did You Hear About Us?"  :  c.howDidYouHearAboutUs?.value

Alternatively, if it was in an enrolment record, you can also use something like below:

Groovy

"Column Name"   : e.customFields.find { cf -> cf.customFieldType.key == "listTest" }?.value

This second method is the best way to retrieve a value from a list of options.

Refer to the generalPrefs-customFields for more information about creating custom fields in onCourse.

Creating and testing your export in onCourse

When you are ready to test your new export in onCourse:

1. Go to any of the windows listed under the Available From section
2. Select a record from the window and hit the Share button.
3. Choose the output type from the left most column, and then select the export, then click Share.

If you attempt to run the export and instead of generating the expected output, it shows an error in the window, then you need to adjust your export code to resolve the error, save the changes and try again. This may be a process you repeat a few times if you are new to creating and editing export files.

When your export runs successfully, check the output is formatted as expected, and make adjustments to your export as needed.

📘

When opening CSV files in a spreadsheet application like Excel, Excel may reformat some of your data based on its own rules. For example, data it thinks are numbers will often be reformatted, so the leading zeros from mobile phones numbers may be stripped . You should run your formatting checks in a text editor like Sublime or Notepad.