A vertical (or stacked) file is the recommended file format accepted by the AwardLetter application. In a vertical file, the data for each student has multiple lines, rows, or records. In this format, each line, row, or record has information for only one cost or fund type, along with the necessary information to identify the student. These files are easily created as comma delimited value (CSV) files.
An example of a vertical file looks like:
You'll notice you see multiple rows for the same student, but each row breaks out a different fund type by terms.
While additional fields may be added to use on the award letter template, there are nine fields required for each student. Please note that the name of the column must match the list and is case sensitive. The fields are:
STUDENTID - your SIS student ID number
FIRSTNAME - student's first name
LASTNAME - student's last name
EMAIL - student's email address (used as primary address to send award letter to)
TEMPLATETYPE - used to separate type of template created for student (i.e. new student, return students, program specific students, etc)
AWARDYEAR - award year
FUNDCOSTNAME - name of Cost or Fund type, based on its code in your SIS (i.e. your SIS may have the Federal Pell Grant appear as PELL)
FUNDCOSTAMOUNT - amount of the Cost or Fund type
FUNDCOSTTERM - term of the Cost or Fund type
FUNDCOSTSTATUS - this field allows you to send additional details of each cost/fund type, such as the acceptance status or disbursement date. If you plan on adding more than one data point for each cost/fund item, you will need to use a semi-colon separator between each data point. You must also ensure that you consistently use the same amount of semi-colons in the column, even if a particular data point is blank/empty.
ALTEMAIL - used to send a copy of the original email to a student's alternative email address - if more than one alternative email address is desired, you must append additional emails to the field, separated by a semicolon
PHONENUMBER - used to send SMS text message with link to award letter (note that if a valid ten digit number is not found for the student, a text message will not be sent)
Parent Email Address - used to send a copy of the letter to a parent email address
REVISEDFLG - used only for the purposes of Print templates. This column will control whether you want to run a check to bypass reprinting a Print template (0) or override and force a reprint (1). You will need to contact your Customer Success Manager to use this function. See more information in the FAQs below.
Frequently Asked Questions:
Can I add additional columns to use?
Additional fields may be added to the file and named anything you want. Examples of additional fields used by clients include alternative emails, parent emails, estimated balance, etc. You will need to contact your Customer Success Manager to determine in what context the field will be used.
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.
What happens if I want to add new columns after going live?
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, those columns will be ignored. 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.
Tell me more about the REVISEDFLG and it's function.
CampusLogic has developed new functionality to the AwardLetter application in order to potentially stop a student record from reprinting, in the case that a file may be uploaded twice, the student is brought into two separate files, etc. This is to help save the student from accidentally receiving another print and save you the cost of that additional print. With the functionality enabled, the AwardLetter application will automatically not process any record where it finds the same combination of STUDENTID, AWARDYEAR, and TEMPLATETYPE has already been processed. If the student's print is for a new award year or a different template type, the application will know that it is new and process the print. The optional REVISEDFLG on the import file will override the application's decision in the cases where a record with the same STUDENTID, AWARDYEAR and TEMPLATETYPE has already been processed and a reprint is needed. Having a value of null or 0 (zero) will let the application know that it can bypass printing if it finds a duplicate record and a value of 1 will force the application to process this record for reprint. By default the functionality is disabled and all records will be processed for printing, whether a duplicate or not.