The upload file to generate award letters and/or shopping sheets can either come in as a JSON file or the more popular and easily created comma delimited value (CSV) file. File format aside, the import file you choose to create and import must have the following fields:
- Student’s First Name
- Student’s Last Name
- Student’s ID Number
- Student’s Email Address
- Template Type (i.e. new student, return students, program specific students, etc.)
- Award Year
Before we move on, please note that those don’t have to be the specific column headers – we know that not all Student Information Systems (SIS) produce the same headers, or same values for that matter. We just need to make sure that no matter how the output from your SIS looks, those fields are in file to uniquely identify the student, the award year, and the type of template.
For example, your SIS may display the header and value for the 2016-2017 award year as AID_YEAR = 1617, while another SIS may display it as AWARD = 2016-2017.
Beyond that you can define as many columns in the file as necessary to build the award letter and/or shopping sheet you desire. The additional columns are added as fields in the system to be used by the product. The only formatting requirements are:
- That there are no spaces in the column names.
- That each line in the award letter file will generate an award letter within the application. All student specific financial information should appear on a single line.
Based on your preference, you can set up the award letter to display costs and funding on a per annum basis, broken down by semester, or a mix of the two. If you choose to display on a per annum basis, you will only need one column for each of the costs and funds in the import file. If you choose to display costs and funds by semester, the import file will need to define the cost and/or fund in semesters (two columns for two semesters, three for three semesters).
For example, if you want to break down Pell by two semesters you would need two columns to break down the Pell for the year. You may have something similar to FALLPELL and SPRINGPELL in the import file.
Below this article are examples of award letters displaying per annum, broken down by semester, and a mix of the two. The items shown in grey are just examples of what else could be displayed, but in most circumstance would be hidden. The numbers seen on each award letter map to the excel and JSON files below – these are not correctly formatted files, only used to show mapping.
(Annual AL.png maps to AnnualFileMap excel)
(Semester AL.png maps to SemesterFileMap excel)
(Mix File AL.png maps to MixFileMap.txt)
Below those are correctly formatted files to view (AnnualExample.csv, SemesterExample.csv, and JSONExample.txt)
Each institution sets up their SIS differently that costs and funds have different names or codes. In order to push the process of developing the award letter quicker, please provide a matrix of all of the costs and fund types that are found in your SIS. This should include the column header name found in the import file and the name that you would like it to display on the award letter. Please also list the costs and fund types in the order you wish for them to appear on the award letter. See the example below:
|Type||Column Name||Display Name|
|Direct Cost||TUIT||Tuition and Fees|
|Direct Cost||ROOM||Room and Board|
|Fund||PELL||Federal Pell Grant|
|Fund||UNSUB||Federal Unsubsidized Loans|
|Fund||SUB||Federal Subsidized Loans|
|Fund||CLU||CampusLogic U Grant|
Frequently Asked Questions:
Do you have to use every column that we produce in the file?
No. We will only use the relevant information pertaining to the award letter and/shopping sheet. For example, if you have an extra column for the student’s middle initial and don’t want to display it, we can ignore it.
Will the award letter show every cost/fund type for the students because it’s on the file?
Even if the import file has a cost or fund type because one student in the file has it, it won’t display it for all students. Normally, if a student has either a zero or null (blank) value for a certain cost or fund type, that item will not appear on that student’s award letter. A school can choose to display zeroes if it is a preference. When an award letter implements a semester breakdown, normally if a fund type or cost is only available for one semester, the other semester(s) will appear with a value of zero to remove any confusion.
What happens if I want to add new columns?
You will need to reach out to your Customer Success Manager before adding any new columns to your award letter import file. If you add columns to the import file and upload it into the system, the system will report it as an error as a failsafe measure. Because the new column isn't defined in the system, it won't know if the new column is a cost, fund type, or miscellaneous field to use. Please make sure to allow CampusLogic time to update the system to expect any new columns.