Overview
This is a report you may wish to produce for reporting or analysis. It shows all Tuition enrolments for a specified year, broken down by Category and Subject.
The process starts with a brief script to help you perform the Find you need. Then it steps through the process to sort and export the records in a useful report form.
Setup
To include Category information in this report, you must have that information in your Tuition enrolment records.
The place to set it up is in the Subjects record. In this file, the field is called Instrument Family.
Go to Home > Setup Files > Subjects
Check that all records have a (correct) entry in Instrument Family
If you had to make any changes, you can use the blue Re-lookup in Tuition button to copy the changes to your Tuition enrolment records.
The Process
The Find Script
Go to Common Tasks > Quick Reports > Tuition enrolments for a specific year
Select the Year you wish to report on from the drop-down in the window that opens
Select Continue
You come to a Find screen in the Tuition enrolments file. Select Perform Find in the top bar.
You will see the found records in the Tuition file.
Sort the Records
Before exporting, you must sort the records by the fields you later intend to group them by.
In the black top bar, select Sort
You will see FileMaker's Sort window
The sort order is shown in the box on the right.
The other available fields are shown in the box on the left.
The Clear and Move buttons in the middle allow you to transfer fields into or out of the Sort Order box.
Select Clear All to remove the current sort fields from the box
In the box on the left, select Category and select Move
Select Subject and select Move
At the bottom of the Sort window, select Sort
The records are now sorted by Category and then by Subject
Export the Records
The next step is to use FileMaker's export process to produce the report with groupings.
In the very top grey bar, select File > Export records
You will see the Save dialogue first.
Name the file
Choose the location to save it to
Select the File Type "Excel Workbooks"
Select Save
You will see an Excel Options dialogue
All fields are optional here. Select Continue
You will then come to the Field Export dialogue
Group by: tick "Category" and "Subject"
Then add field to the Field Export Order box
Add Category
Select Category and select Move
Alternatively, double-click Category
Add Subject
But you also want to include the totals for these counts. They're held in fields called Summary Enrolments, which are not in the current layout, but they are in the current table.
Select the drop-down that says Current Layout ("Tuition List")
Change in to Current Table ("Tuition List")
Add Summary Enrolments to the Field Export Order box
You will see that it adds three fields
Now change the order of these fields so the report makes more sense.
Use the tiny double-arrows at the left-hand end of a field name to drag it up the list.
Position Summary Enrolments by Category after Category
Position Summary Enrolments by Subject after Subject
Position Summary Enrolments at the bottom
Select Export in the bottom right
The spreadsheet will be saved to the location you nominated.
