Data Integration Guide version 6

Last Updated:

July 28, 2020

Data Retention

GradesFirst allows clients to determine and manage their own data retention policy. Clients have complete control over their data. The application includes a user interface and data import mechanisms that allow clients to alter and remove information. When information is deleted from the GradesFirst application, it is completely removed from our databases.

Populating Student Data in GradesFirst

Student data is exported from the school's information system and is uploaded directly into the GradesFirst application by a user or agent of the school via automation. After successful authentication into the GradesFirst system, the file is uploaded, the data is validated, and then persisted to the GradesFirst database.  The school does not experience any down time during this process, and the uploaded file is encrypted and kept on the GradesFirst servers in a secured location for 5 days in case they are needed for customer support issues.  After 5 days, the uploaded file is automatically deleted and cannot be recovered.  As with any interaction with GradesFirst, this data is transmitted via an SSL encrypted connection.  Each institution decides how often data is refreshed from the student information system.  Most schools update once daily.

There are a variety of ways to import your data into GradesFirst, and your institution can use whichever one(s) fit your needs best. Non-technical users can easily run one-time imports using the Admin panel inside GradesFirst, or university IT staff can run imports via the GradesFirst API. API imports may use either cURL or SFTP, and they can be run as one-time imports or as automatically recurring imports.  Automatic imports offer the advantage of keeping your data in GradesFirst up-to-date, and they reduce the hassle of manually running imports as the semester progresses.  University IT staff can set up the automated imports or outsource that work to GradesFirst.  Once the automated import is in place, very little if any maintenance is required going forward.

For added security, import files can be encrypted before they are transferred.  GradesFirst provides full documentation and support on setting up encrypted uploads.  Once the file is encrypted, you can upload it to GradesFirst just as you would any other file, and GradesFirst will automatically handle decrypting and processing of the file as normal.

Testing Data Uploads

GradesFirst allows clients to test their data import files on a test system before going to production.  A valid user account will be provided to personnel responsible for testing the data import.  Please contact support@gradesfirst.com for more information on getting your test account set up.

CSV Data Uploads

GradesFirst data uploads use the CSV file format. Please follow this section to ensure data integrity.

Things to Consider

  • The file must be a plain text or flat file with values separated by commas. It needs to be in 8-bit or standard ASCII format, with a file extension of “.csv”.

  • Please enclose every field in double quotes. If any field value contains a comma or apostrophe, and the field value is not enclosed in double quotes, the data of the CSV file will be corrupted.  This is a standard CSV file format rule. Although it’s not an absolute requirement to include all fields in double quotes, since Names, Majors, Courses, and Addresses often contain apostrophes and commas, we highly recommended enclosing every field in double quotes.


Incorrect

GradesFirst Import Version=6
# Object, Action, External ID, Name, Group ID
category,add,wtrack,Woman's Track, GradesFirst University

Correct

GradesFirst Import Version=6
# Object, Action, External ID, Name, Group ID
"category","add","wtrack","Woman's Track","GradesFirst University" 
  • Editing your file in spreadsheet software like Microsoft Excel can automatically reformat and corrupt the data by stripping out leading zero’s, changing numbers to letters, and inserting carriage returns. It is not recommended to edit your data files with Excel unless you are an expert user, because it can cause data corruption and/or failed uploads. For this reason, we recommend editing all CSV files with a plain text editor, such as Notepad on Windows, TextEdit on Mac, or vi on Unix.

  • If any field contains double quotes as part of the actual data, such as a course named: “An In-Depth Study of Shakespeare’s “Hamlet””, you can insert these double quote characters into your data file by replacing each double quote with two consecutive double quote characters. So, the correct field would be: “An In-Depth Study of Shakespeare’s ““Hamlet”””.

    • Here is an example of enclosing the field in double quotes by concatenating double quote characters to the beginning and ending of the returned string value and checking for double quotes in the field value:

SELECT ‘”’ || replace(field_name, ‘”’, ‘””’) || ‘”’
AS my_field_name
FROM table_name

  • If you want to insert the required Header Record or Column Headers into the import file using SQL, one quick way is to use a UNION statement to accomplish this.  (Be sure to account for the syntax of the SQL you are using)

    • Below is another example.

SELECT “Student ID”, “Student First Name”, “Student Last Name”
UNION
SELECT StudentId, StudentFirstName, StudentLastName
FROM Student_Table;

This will result in the first row to contain the required header record (column headers).

NOTE: The SQL above is an example and the Header Record in the actual import files would need to contain all the columns for each file, respectfully.

Document Header

Each uploaded file should contain the following header on the first line: 

GradesFirst Import Version=6

For Campus members, the header should also contain the institution id:

GradesFirst Import Version=6,Institution ID=99999

Comments and Column Headers

Data files may contain comments by beginning any line with a ‘#’ symbol. Previous GradesFirst imports (version 1 manual imports) used Column Headers instead of a Document Header. Although no longer required, Column Headers can still be done using comments.

# Object, Action, External ID, Name, Group ID

Single Sign-On

If your university is using Single Sign-On, you will need to coordinate with the IT staff who handle the SSO setup to ensure that you are both using the same user identifier. This ensures that GradesFirst and your university SSO can correctly identify users when they log in. This user identifier must be unique, so the most common examples are: University ID, Login, CWID, UMID, RAM ID, C number, G number, SKG number, ONLID, No photo ID, Peoplesoft Empl ID, etc. We do not recommend using e-mail because some schools may allow duplicate e-mail addresses over time. Whatever identifier you end up using, you have two different fields where can choose to insert this identifier in your upload – the Primary ID or the Alternate ID. After you add this field to your upload, coordinate with the university IT staff member who is handling the SSO, let them know which identifier you are using, and let them know if you placed it in the primary ID field or the alternate ID field. Be sure the SSO server can return this value for each user when they attempt to login to GradesFirst.

Importing Your Data

There are a variety of ways to import your data into GradesFirst, and your school can use whichever one(s) fit your needs best. Non-technical users can easily run one-time imports using the Admin panel inside GradesFirst, or university IT staff can run imports via the GradesFirst API. API imports may use either cURL or SFTP, and they can be run as one-time imports or as automatically recurring imports. Recurring imports offer the advantage of keeping your data in GradesFirst up-to-date, and they reduce the hassle of manually running imports as the semester progresses. To set up recurring imports, your university IT staff will need to write a script to export the data from your student information system, format the data according to this guide, and then upload the data file via the GradesFirst API. For recurring data uploads, schools may upload at their discretion: nightly, weekly, monthly.  We ask that schools schedule recurring imports after 7pm CST, but they must be finished by 5am CST.

Manual Imports inside GradesFirst

Perhaps the easiest way to import into GradesFirst is to simply upload your import file using the Admin panel.

  1. Log into GradesFirst, click the Admin Tab, and click on Data Import.
  2. Select the Term, choose the CSV file, and click Import My Selected File.
  3. Verify that the data appears correct, then click Data Sample is Valid.
  4. You will receive an email confirmation with the results of your import.

API Imports

You can upload API imports using cURL or SFTP. To be sure your systems allow traffic to GradesFirst servers, please see Firewall Exceptions.

Important Note:  When selecting a time to schedule your automated upload, please do not schedule your file to import between 5:00am – 6:00am CST.   This is our deployment window and all services are stopped during this time.  So please ensure all uploads are finished by 5:00am CST.

API Imports with SFTP

You can set up SFTP imports with the help of your GradesFirst trainer during your school’s implementation of GradesFirst. If your implementation has already been completed, simply contact support and we’ll be glad to help out. Here’s the process:

  1. Add GradesFirst’s IP addresses to your school’s firewall whitelist as outlined above.

  2. E-mail us the SSH RSA Public Key(s) for the computer(s) that will be sending the import files via SFTP.

  3. We’ll assign you a username and Internet address where you can send the imports.

  4. (Optional) If you wish to receive import status e-mails after each import, log into GradesFirst, click on the Admin tab, click on School Settings, and enter your e-mail address under “API Imports E-mail Address”. Click the Update Settings button to save.

  5. Upload the import file with SFTP.

API Imports with cURL

You can also run API imports using an open-source command line tool named cURL. (Although we recommend cURL, GradesFirst is not responsible for it, and cannot be held liable for damages due to installing or using it.)

Installing cURL on Windows

1. Download the appropriate version of cURL for your version of Windows.

2. Unzip the file.

3. Copy the curl.exe file into C:\Windows\System32

4. To test cURL:

1. Go to Start Menu > Run

2. Type cmd and hit Enter.

3. Type curl --help and hit Enter.
You should see information about the various options cURL provides.

Basic Automated Upload Example for Windows

1. Create a folder at C:\gradesfirst

2. In the C:\gradesfirst folder, create a folder named current and another folder named archive so that you have a folder structure like this:

C:\gradesfirst\current

C:\gradesfirst\archive

In this example, the current folder is where new files are placed each day by some automated process. The archive folder is where files will be placed after they are uploaded.

3. In the C:\gradesfirst folder, create a file called upload.bat

In this file, you'll want to include your script for uploads.

An example of what this script might look like is:

CD C:\gradesfirst\current
for %%f in (*.csv) do @curl -u <username>:<api-key> -F "data=@%%f" https://<environment>.gradesfirst.com/api/imports
move \-y "C:\gradesfirst\current\*.csv" "C:\gradesfirst\archive\"

Be sure to replace the tokens above in your script:

<username> - the username of the GradesFirst user you'll be using for imports.

<api-key> - the GradesFirst import user's API Key. You can see this key by logging in as the import user and navigating to Admin > View My User's API Key)

<environment> - Your school's GradesFirst subdomain for either the training or production site, depending on the site to which you'd like to send information. 

Training Site Example: coastalcollege-training.gradesfirst.com 

Production Site Example: coastalcollege.gradesfirst.com

So, the curl command in your script might look like:

curl -u johndoe:7rjto38f0swk4j598 -F "data=@%%f" https://coastalcollege-training.gradesfirst.com/api/imports

4. To test your script:

1. Place one of the sample import test .csv files in the C:\gradesfirst\current folder 

2. Go to Start Menu > Run

3. Type cmd and hit Enter.

4. Run your upload.bat file by typing C:\gradesfirst\upload.bat

Within a minute or two, your file should show up in GradesFirst on the Data Import screen (Admin > Data Import).

5. Create a Windows scheduled task so that the upload occurs automatically on a schedule of your choosing. We recommend uploading once a day between the hours of 10 PM and 5 AM central time.

How to create a scheduled task on Windows 7 (or higher):

  1. Open Task Scheduler by clicking the Start button , clicking Control Panel, clicking System and Security, clicking Administrative Tools, and then double-clicking Task Scheduler.   If you're prompted for an administrator password or confirmation, type the password or provide confirmation.

  2. Click the Action menu, and then click Create Basic Task.

  3. Type a name for the task and an optional description, and then click Next.

  4. Select a schedule based on the calendar by clicking Daily, Weekly, Monthly, or One time, click Next; specify the schedule you want to use, and then click Next.

  5. To schedule a program to start automatically, click Start a program, and then click Next.

  6. Click Browse to find the program you want to start (your upload.bat file at C:\gradesfirst\upload.bat), and then click Next.

  7. Click Finish.

Create your API Key

After you’ve installed cURL ,you need to create an API key. (If you are already running automated uploads, you already have an API key. Do not change your API key without good reason, because you must also change the cURL command accordingly.)

  1. Download cURL. (see above instructions for Windows machines)

  2. Add GradesFirst’s IP addresses to your school’s firewall whitelist as outlined above.

  3. (Optional) If you wish to receive import status e-mails, log into GradesFirst, click on the Admin tab, click on School Settings, and enter your e-mail address under “API Imports E-mail Address”. Click the Update Settings button to save.

  4. After logging into GradesFirst, click the Admin tab, and click "Create your API Key" (on the right).

  5. Copy the key provided; it will be referred to as <api-key>

  6. Use the following cURL statement for uploading:

    curl -u <username>:<api-key> -F "data=@<file>" https://<environment>.gradesfirst.com/api/imports

    <username>       The user login of the user you want to do the import.
    <api-key>           The key that you received in step 3.
    <file>                  The full local path and name of the file you want to upload.
    <environment>   The GradesFirst environment you are uploading to. "app" and "training" are the
                               most frequent options with "app" being the production environment.

  7. Run your command to upload the file.

GradesFirst Data Layouts

The following are the formats for the different types of records within GradesFirst.  The easiest way to run your imports is to create a single CSV file that contains all the different types of records, but you can also import one file for each type of record if you wish.  In either situation, you need to follow a set order as you are importing your records. This order is outlined below – if you are using a single CSV file for your entire upload, place the records in this order. If you are using one CSV file for each upload type, you need to run the imports in this order.

Important Note
:  If you are using the data integration guide version 1 to import your student records, and only using version 4 (or higher) to supplement the import, you do not need to import all of the below required record types.  For example, if you were simply using version 4 (or higher) to import your Student/Advisor assignment, you would simply need to create, and import, the Relationship file.

  1. Term *
  2. Major
  3. Category
  4. Course *
  5. Section *
  6. User *
  7. Grouping *
  8. Authorize
  9. Registration *
  10. Enrollment *
  11. Instruction *
  12. Declaration
  13. Categorize
  14. Tag
  15. Absence
  16. Relationship
  17. Note
  18. Reminder
  19. Alert

* Signifies required record types for using version 6 to import all your student information.

Instruction Conventions

Below are tables that describe the various object imports. Descriptions include:

  • Columns: easily referable column name; has no purpose as imports are column order specific
  • Required: Either “yes”, “no”, or a list of actions for which the field is required.
  • Description: simple description of the content for the column

Anytime you see “External ID” for an object, that is the Unique Key at the University.  Anytime you see “<object_name> ID”, that is a reference to the other object’s “External ID.”

Combining multiple types of records in a single file

If you don’t want to run one import of each type, thereby having 10-20 different imports, it is much easier to simply include multiple types of imports in a single file. As long as you follow the correct order noted on the previous page, you’ll be fine.

Example

GradesFirst Import Version=6
#All types of imports in this upload
"term","add","spr2015",'Spring 2015","01/07/2015","05/25/2015"
"major","add","ENG","English"
"category","add","fbl","Football","GradesFirst University"
"course","add","ENG101","ENG101","English Composition","3"
"section","add","ENG101-spr2015-001","ENG101","spr2015","English Composition"
"user","add","905123",Y","Y","Y","Tony","Stark","","Student","tony.stark@gradesfirst.com"
"user","add","905456",Y","Y","Y","Kim","Weston","","Professor","kim.weston@gradesfirst.com"
"user","add","905789",Y","Y","Y","John","Doe","","Advisor","john.doe@gradesfirst.com"
"grouping","add","GradesFirst University","905123"
"authorize","add","tutor","905477"
"registration","add","spr2015","905123","GradesFirst University","SR","4.0"
"enrollment","add","905123","ENG101-spr2015-001","505123","605123"
"instruction","add","ENG101-spr2015-001","905456"
"categorize","add","fbl","905123"
"tag","add","linebackers","GradesFirst University","905123"
"absence","add","905123","ENG101-spr2015-001","01/07/2015","This is an excused absence","Y"

Clearing Data Fields

To enable capturing certain data points via manual data entry, as well as combining imports from disparate systems, certain data fields require adding a dash ("-") to the import file in order to clear the value from the system. Those fields are:

Classification

  • Overall GPA
  • Term GPA

Enrollment

  • Midterm Grade
  • Final Grade

User:

  • SSO ID
  • First Name
  • Last Name
  • Email
  • Alternate Email
  • Home Phone
  • Cell Phone
  • Work Phone
  • Address 1
  • Address 2
  • City
  • State
  • Zip
  • High School Zip
  • Household Income
  • Total Credit Hours
  • Total Credit Hours Attempted
  • Gender
  • Ethnicity
  • Date of Birth
  • Enrollment Status
  • Enrollment Goal
  • User PIN
  • Alternate External ID

Types of Imports (Alphabetical)

Absence

This is the file that will be used to assign absences to students for an enrollment.   Not required.  The unique key for this record type is the combination of Primary User ID, Term ID, Course External ID, Section Name, and Date.

Columns

Required

Data TypeMax LengthForeign Key

Description

"absence"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to create an absence

Primary User ID

Yes

varchar255user:primary_user_id

The unique identifier for the user that will be given the absence.

Term IDYesvarchar255term:external_idThe unique identifier of the term for the section this absence occurred in.
Course
External ID
Yesvarchar255course:external_id

The unique identifier of the course for the section this absence occurred in. Note: this is the external ID of the course, not the Course ID / Name.

Section Name

Yes

varchar255section:nameFor example ... "1", "A", etc.

Date

Yes

date10

The date of the absence.  Example, mm/dd/yyyy

Comment

No

varchar255


Excused

No

varchar1

"Y" for excused and "N" for unexcused. This always defaults to unexcused.

 Example

GradesFirst Import Version=6
# Object, Action, User ID, Term ID, Course ID, Section Name, Date, Comment, Excused
"absence","add","905123","Spring2012","ENG101","13","01/07/2010","You are ok because this is excused.","Y"

Attendance

This is the file that will be used to assign absences to students for an enrollment.   Not required.  The unique key for this record type is the combination of Primary User ID, Term ID, Course External ID, Section Name, and Date.

Columns

Required

Data TypeMax LengthForeign Key

Description

"attendance"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to create an attendance, “delete” to delete an attendance.

Primary User ID

Yes

varchar255user:primary_user_id

The unique identifier for the user that will be given the absence.

Term IDYesvarchar255term:external_id

The unique identifier for the user that will be given the attendance.

Course
External ID
Yesvarchar255course:external_id

The unique identifier of the course for the section this attendance occurred in. Note: this is the external ID of the course, not the Course ID / Name.

Section Name

Yes

varchar255section:nameFor example ... "1", "A", etc.

Date

Yes

date10

The date of the absence.  Example, mm/dd/yyyy

StateYesvarchar

"present" or "absent"

Comment

No

varchar255


Excused

No

varchar1

"Y" for excused and "N" for unexcused. This always defaults to unexcused.

 Example

Example:
GradesFirst Import Version=6
# Object, Action, User ID, Term ID, Course ID, Section Name, Date, State, Comment, Excused
"attendance","add","905123","Spring2012","ENG101","13","01/07/2010",“absent”, "You are ok because this is excused.","Y"

Alert

This is the file that will be used to create alerts for students. Not required. There is no duplication checking, so alerts imported multiple times will result in multiple alerts being created. To associate the alert with a specific course, you must include the Term ID, Course External ID, and Section Name.

Columns

Required

Data TypeMax LengthForeign Key

Description

"alert"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to create an alert.

Group ID

Yes

varchar255group:external_id

The unique identifier for a group. Groups are created and configured through the Admin user interface.

Primary User ID

Yes

varchar255user:primary_user_id

The unique identifier for the student related to the alert.

Alert Reason External IDYesvarchar255alert_reason:external_idThe unique identifier for the alert reason configured. Alert reasons are created and configured through the Admin user interface.

Issued By

Yes

varchar255user:primary_user_id

The Primary User ID of the alert creator.

Term IDNovarchar255term:external_idThe unique identifier of the term for the section this absence occurred in.
Course
External ID
Novarchar255course:external_id

The unique identifier of the course for the section this absence occurred in. Note: this is the external ID of the course, not the Course ID / Name.

Section Name

No

varchar255section:nameFor example ... "1", "A", etc.

Comment

No

varchar255

The text or comment of the alert

 Example

GradesFirst Import Version=6
# Object, Action, Group ID, User ID, Alert Reason, Issued By, Term ID, Course ID, Section Name, Comment
"alert","add","GradesFirst University","905123","Alert123","908761","Spring2012","ENG101","13","This student is having trouble in my class"

Authorize

This is the file that will assign users to a specific Role. Sets a particular user to be a student, advisor, professor, etc. in GradesFirst. Not required.  The unique key for this record type is the combination of Role ID and Primary User ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"authorize"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to add a role to a user

Role ID

Yes

varchar255role:external_id

The unique identifier for the role.  This identifier has to be added to the role through the administrative section of GradesFirst.

Primary

User ID

Yes

varchar255user:primary_user_id

The unique identifier for the user that will be given the role

Example 1

GradesFirst Import Version=6
#Object, Action, Role_Id, User_Id
"authorize","add","tutor","905123"

Example 2

GradesFirst Import Version=6
#Object, Action, Role_Id, User_Id
"authorize","delete_all","","905123"

Category

Creates categories in GradesFirst and assigns them to a group.  For example, our athletic clients use categories to identify which team their students are on (e.g. Football, Baseball, Softball). See “Categorize” for instructions on assigning students to categories.  Dropping a category will cause all users to be removed from that category.  Not required.  The unique key for this record type is the External ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"category"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to add or update a category

External ID

Yes

varchar255

A unique identifier for this category. Ex. “fbl”.

Name

Yes

varchar255

The name of the category. Ex. “Football”.

Group ID

Yes

varchar255group:external_id

The unique identifier for a group. Groups are created and configured through the Admin user interface.

Example

GradesFirst Import Version=6
#Object, Action, External_Id, Name, Group_Id
"category","add","fbl","Football","GradesFirst University"
"category","delete","fbl"

Categorize

Links the students to the categories.  Categories must be previously defined in GradesFirst either via import “Category” above or manual via the website.  Not required.  The unique key for this record type is the combination of Category ID and Primary User ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"categorize"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to add a categorization to a user 

Category ID

Yes/No

varchar255category:external_id

The unique identifier for the category. Ex. “fbl”.  This field is NOT required if you wish to remove all categories linked to a student ID (see example 2, line 3).

Primary

User ID

Yes/No

varchar255user:primary_user_id

The unique identifier for the user being categorized.   This field is NOT required if you wish to remove all students from a specific category (see example 2, line 4).

Example 1

GradesFirst Import Version=6
#Object, Action, Category_Id, User_Id
"categorize","add","fbl","905123"

Example 2

GradesFirst Import Version=6
#Object, Action, Category_Id, User_Id
"categorize","delete_all","","905123"
"categorize","delete_all","fbl"

Course

Creates a record of a Course. Required.  The unique key for this record type is the External ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"course"

Yes

varchar

Record type.

Action

Yes

varchar

This value will be "add" to add or update a course

External ID

Yes

varchar255

A unique identifier for this course.

Name/Course ID

Yes

varchar255

For example ... CS-101, ENG-101

Title

Yes

varchar255

For example ... "Introduction to Computers"

Credit Hours

No

integer2


Example:

GradesFirst Import Version=6
#Object, Action, External_Id, Name, Title, Credit_Hours
"course","add","133425","ENG101","English Composition","3"

Declaration

Links the student to additional majors not listed in the User record.  Majors must be previously defined in GradesFirst either via import “Major” or manual via the website.  Not required.  The unique key for this record type is the combination of Primary User ID and Major ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"declaration"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to add or update a major 

Primary

User ID

Yes

varchar255user:primary_user_id

A unique identifier for this user.

Major ID

Yes / No

varchar255major:external_id

A unique identifier for this major. Ex. “ENG”. This field is NOT required if you wish to remove all majors linked to a student ID.

Example

GradesFirst Import Version=6
#Object, Action, Primary_User_Id, External_Id
"declaration","add","905123","ENG"

*This record type was previously known as “user_major.” While this terminology may still be used, we encourage our users to use the terminology “declaration” moving forward.

Enrollment

Links the students to the course Section.  Required.  Use the “drop_all” action to drop all enrollments for a user for a term.  This is typically used to drop all enrollments for a student, then subsequently add back the “undropped courses.”  Using “drop_all” ensures the university doesn’t have to track “dropped” enrollments, and only has to track those students still enrolled.  The unique key for this record type is the combination of Primary User ID, Term ID, Course External ID and Section Name.

Columns

Required

Data TypeMax LengthForeign Key

Description

"enrollment"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to add or update an enrollment, "drop" to drop an enrollment, “drop_all” to drop an enrollment

Primary User ID

Yes

varchar255user:primary_user_id

The unique identifier of the student to be enrolled in the section.

Term ID

Yes

varchar255term:external_id

The unique identifier for the term which includes the section the student is enrolled in.

Course External IDYes/No *varchar255course:external_id

The unique identifier of the course for the section the student is enrolled in. Note: this is the external ID of the course, not the Course ID / Name.

* This column is required for "add", "drop" or "drop_all'

Section NameYes/No *varchar255section:name

For example ... "1", "A", etc.

* This column is required for "add", "drop" actions but not for "drop_all".

Midterm Grade

No

varchar255

The midterm grade, i.e. "A", "NC".  (Empty if using “drop_all” action)

Final Grade

No

varchar255

The final grade, i.e. "A", "NC". (Empty if using “drop_all” action)

Dropped At DateNodate10
This is a date field. A valid date should be mm/dd/yyyy
WithdrawnNovarchar1

Valid values are “Y” or “N”.  If marked “Y”, the enrollment will be marked as withdrawn.  

The following version of the enrollment record will drop all the enrollments in a term.  This is used for when the school cannot create explicit drop records for students who have dropped enrollments.

Columns

Required

Description

"enrollment"

Yes

Record type.

Action

Yes

The value will be "drop_all_for_term".

Term ID

Yes

The unique identifier for the term which includes the section the student is enrolled in.

Example

GradesFirst Import Version=6
# Object, Action, User_ID, Term_ID, Course_ID, Section_Name, Midterm_Grade, Final_Grade, Dropped_At, Withdrawn
"enrollment","add","905123","Spring2012","ENG101","13","A","B","10/20/2017","N"
"enrollment","drop_all_for_term","Spring2012"

Grouping

Links the students to a specific Group. Students may be linked to multiple groups. Required.  The unique key for this record type is the combination of Group ID and Primary User ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"grouping"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to add a user to a group 

Group ID

Yes

varchar255group:external_id

The unique identifier for the group. Groups are created and configured through the Admin user interface.

Primary

User ID

Yes

varchar255user:primary_user_id

The unique identifier for the user that will be added to the group.

Example

GradesFirst Import Version=6
#Object, Action, Group_Id, User_Id
"grouping","add","GradesFirst University","905123"

Instruction

Links professors as instructors for course Section.  Required.  The unique key for this record type is the combination of Term ID, Course External ID, Section Name and Primary User ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"instruction"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to add an instructor to a section

Term IDYesvarchar255term:external_idThe unique identifier for the term which includes the section the professor is instructing.

Course

External ID

Yesvarchar255course:external_idThe unique identifier of the course for the section the professor is instructing. Note: this is the external ID of the course, not the Course ID / Name.

Section Name

Yes

varchar255section:name

For example ... "1", "A", etc.

Primary

User ID

Yes/No *

varchar255user:primary_user_id

The unique identifier for the professor.

* This column is required for "add" 

Example

GradesFirst Import Version=6
# Object, Action, Term_ID, Course_ID, Section_Name, User_ID
"instruction","add","Spring2012","ENG101","12","905456"

Major

Creates a record of a Major, but does not link the Major to any students.  That is done during the User import.  The Majors must be imported first or appear ahead of the Users.  Not required.  The unique key for this record type is the External ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"major"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to add or update a major

External ID

Yes

varchar255

A unique identifier for this major. Ex. “ENG”.

Name

Yes

varchar255

The name of this major. Ex. “English”.

Example

GradesFirst Import Version=6
#Object, Action, External_Id, Name
"major","add","ENG","English Literature"

Notes

Creates a Note record for a student. The unique key for this record type is the combination of Primary User ID, Group ID, and Note Body.

Columns

Required

Data TypeMax LengthForeign Key

Description

“note”

Yes

varchar

Record type.

“add”

Yes

varchar

This value must be "add" to add a Note.

Primary User ID

Yes

varchar255user:primary_user_id

The unique identifier of the student for whom this note will be created.

Group ID

Yes

varchar255group:external_id

The unique identifier of the group being registered into.

Is Private?

Yes

varchar1

Valid values are “Y” or “N”.  If marked “Y”, the note will only be viewable by the note creator.

Is Student Viewable

Yes

varchar1

Valid values are “Y” or “N”.    If marked “Y”, the note will be viewable by the student.

Show on Student Report

Yes

varchar1

Valid values are “Y” or “N”.  This option allows this note to be visible on the Student Development report.

Created on

Yes

date10

This is a date field.  A valid date should be mm/dd/yyyy

Created by

Yes

varchar255user:primary_user_id

This value should be the Primary User ID of the note creator.

Note Body

Yes

varchar65535

This field contains the body (or details) of the note.

URL

No

varchar255

This field can contain a website URL

Note ReasonsNovarchar65535
A comma-separated list of note reasons
Rich TextNovarchar1
Valid values are "Y" or "N". If marked "Y", the note will be viewed in STC as rich text, meaning it will render any HTML values included in the body. If marked "N", the note will not be viewed as rich text, and any HTML values will be printed as text. The default value if no value is included is "N".

Example

GradesFirst Import Version=6
#Object, Action, Primary_Student_Id, Group_Id, Is_Private?, Student_View?, Show_on_Reports?, Created_On, Created_By, Body, URL, Note Reasons, Rich_Text
"note","add","905123","GradesFirst University","Y","Y","Y","01/07/2015","905789","Andrew received excellent tutoring at our tutor center","www.gfu.org","Success Coaching,Academic Plan","Y"

Registration

Creates a link between a Student and a Term.  If students are showing under classes, yet do not show in searches, typically there are errors in the Registrations. Required.  The unique key for this record type is the combination of Term ID, Primary User ID and Group ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"registration"

Yes

varchar

Record type.

Action

Yes

varchar

This value will be "add" to add or update a registration 

Term ID

Yes

varchar255term:external_id

The unique identifier of the term being registered into.

Primary

User ID

Yes

varchar255user:primary_user_id

The unique identifier of the student being registered.

Group ID

Yes

varchar255group:external_id

The unique identifier of the group being registered into.

Classification

No

varchar255

Valid values are FR,FRSH,FRESHMAN=Freshman; SO,SOPH,SOPHOMORE=Sophomore; JR,JUNR,JUNIOR=Junior; SR,SENR,SENIOR=Senior; GR,GRAD,GRADUATE=Graduate; AE,ADLT,ADULT=Adult Education; CE,CONT,CONTINUING=Continuing Education; SK,SKIL,SKILLS=Skills; CR,CRED,CREDIT=Credit; OT,OTHR,OTHER=Other; PR,PRSP,PROSPECTIVE=Prospective

Overall GPA

No

float9


Term GPANofloat9

Example

GradesFirst Import Version=6
#Object, Action, Term_Id, Primary_User_Id, Group_Id, Classification, Overall_GPA, Term_GPA
"registration","add","spr2015","905123","GradesFirst University","SR","3.86","3.97"

Relationship

Creates a relationship between a single parent Advisor / Coach / Professor / Tutor and a child Student.  This file is used to link students to an advisory type user.  For example, if you wish to import your student’s advisor assignment from your SIS (Student Information System) so you do not have to manually assign those in GradesFirst.  Typically you would not use this file to assign professors to students unless you are using faculty advising in GradesFirst.  Not a required file.  The unique key for this record type is the combination of Name, Parent Primary User ID, Child Primary User ID and Group ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"relationship"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to create a relationship 

Type

Yes

varchar

The name of the relationship.  Must be one of the following: "advisor", "coach", "professor", "tutor".  

Parent Primary User ID

Yes

varchar255user:primary_user_id

The unique identifier for the parent user in the relationship, i.e. the advisor or coach.

Child Primary User ID

Yes

varchar255user:primary_user_id

The unique identifier for the child user in the relationship, i.e. the student.

Group ID

Yes

varchar255group:external_id

The unique identifier for a group.

Example 1

GradesFirst Import Version=6
#Object, Action, Type, Parent_User_Id, Child_User_Id, Group_Id
"relationship","add","advisor","905789","905123","GradesFirst University"

Example 2

GradesFirst Import Version=6
#Object, Action, Type, Parent_User_Id, Child_User_Id, Group_Id
"relationship","add","advisor","905788","905111","GradesFirst Athletics"
"relationship","delete_all","advisor","","","GradesFirst Athletics"
"relationship","delete_all","advisor","905788","","GradesFirst Athletics"
"relationship","delete_all","advisor","","905111","GradesFirst Athletics"


Reminder

Creates a Todo record for a student.  Todos are for faculty and staff to keep up with things they want to do for students.  There is not a unique key for this record type, todos can only be created.

Columns

Required

Data TypeMax LengthForeign Key

Description

“reminder”

Yes

varchar

Record type.

“add”

Yes

varchar

This value must be "add" to add a reminder.

Staff Primary User IDYesvarchar255user:primary_user_idThe unique identifier of the faculty or staff member for whom this reminder will be created.

Student Primary User ID

Yes

varchar255user:primary_user_id

The unique identifier of the student for whom this reminder will be created.

Created Date

No

date10

This is a date field.  A valid date should be mm/dd/yyyy . If blank, defaults to today.

Due DateYesdate10
This is a date field. A valid date should be mm/dd/yyyy
Cleared DateNodate10
The date when this reminder was cleared. A valid date should be mm/dd/yyyy

Reminder Description

Yes

varchar255

This field contains the body (or details) of the reminder.

Example 1

GradesFirst Import Version=6
# Object, Action, Staff_ID, Student_ID, Created_Date, Due_Date, Cleared_Date, Description
"reminder","add","2938949","2598579","10/10/2015","10/20/2015","10/20/2015","Buy Anthony a birthday present."

Example 2

GradesFirst Import Version=6
# Object, Action, Staff_ID, Student_ID, Created_Date, Due_Date, Cleared_Date, Description
"reminder","add","2938949","2598579",,"10/20/2015",,"Buy Anthony a birthday present."

Section

Creates the course Section for each Course.   Courses must be created prior to creating the course Section.  Required.  The unique key for this record type is the combination of Term ID, Course External ID and Section Name.

Columns

Required

Data TypeMax LengthForeign Key

Description

"section"

Yes

varchar

Record type.

Action

Yes

varchar

This value will be "add" to add or update a section 

Term ID

Yes

varchar255term:external_id

The unique identifier of the term for this section.

Course External ID

Yes

varchar255course:external_id

The unique identifier of the course for this section. Note: this is the external ID of the course, not the Course ID / Name.

Section Name

Yes

varchar255section:name

For example ... "1", "A", etc.

Section Tags

No

varchar255section_tag:name

This data is used to create Section Tags within GradesFirst.  Section tags are used to automate absence notifications (e.g. emails) to students, professors and/or advisors via an absence email template.

Section Type *Novarchar255

For example ... "Lab", "Lecture", etc.

* This column will be implemented in the winter-2017 release of SSC Campus.  

Example

GradesFirst Import Version=6
# Object, Action, Term ID, Course ID, Section Name, Tags
"section","add","Spring2012","ENG101","1","xyx,abc","Lecture"

Section Meeting

Creates meeting times for each section.   Sections must be created prior to creating the Section meeting times.

Columns

Required

Data TypeMax LengthForeign Key

Description

"section_meeting"

Yes

varchar

Record type.

Action

Yes

varchar

This value will be "add" to add or update section meeting times. 

Term ID

Yes

varchar255term:external_id

The unique identifier of the term for this section.

Course External ID

Yes

varchar255course:external_id

The unique identifier of the course for this section. Note: this is the external ID of the course, not the Course ID / Name.

Section Name

Yes

varchar255section:name

For example ... "1", "A", etc.

Begin Date

No

date10

Value must be in mm/dd/yyyy format.

End Date

No

date10

Value must be in mm/dd/yyyy format.

Start Time

No

varchar5

24 hour clock: hh:mm

End Time

No

varchar5

24 hour clock: hh:mm

Meeting Days

No

varchar9

Any grouping of the following: SuMTWRFSa

Location

No

varchar255

Example would be "Harris Hall - Room 213-A".

Example

GradesFirst Import Version=6
"section_meeting","add","Spring2012","ENG101","1","01/07/2012","03/01/2012","08:00","09:15","MWF","Bruno Hall"
"section_meeting","add","Spring2012","ENG101","1","03/15/2012","05/01/2012","08:00","09:15","MWF","Bruno Hall"

In the example above, the English course has a 2 week break from 3/1 to 3/15

Tag

Creates and assigns Tags to particular students in a particular Group. Tags can be used to identify types of students. For example, an Athletic department might have a Category of Football, but if they want to be even more precise, they could create a tag called “Linebackers”, and assign these players this tag. This would allow them to do things like e-mail all their Linebackers at the click of a button. Not required.  The unique key for this record type is the combination of Tag, Group ID and Primary User ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"tag"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to add a tag to a user  

Tag

Yes

varchar255

A tag to add to the user, i.e. "trio", ""

Group ID

Yes

varchar255group:external_id

The unique identifier for a group.

Primary User ID

Yes/No

varchar255user:primary_user_id

The unique identifier for the user being tagged.

Example

GradesFirst Import Version=6
#Object, Action, Tag, Group_Id, User_Id
"tag","add","linebackers","GradesFirst University","905123"

Term

This record set creates terms in GradesFirst.  Required.  The unique key for this record type is the External ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"term"

Yes

varchar

Record type.

Action

Yes

varchar

Currently only the "add" action is supported which adds or updates the term.

External ID

Yes

varchar255

A unique identifier for this term.

Name

Yes

varchar255

For example "Spring 2010"

Begin Date

Yes

date10

The date classes begin for the term in the format mm/dd/yyyy.

End Date

Yes

date10

The date classes end for the term in the format mm/dd/yyyy.

Example

GradesFirst Import Version=6
#Object, Action, External_Id, Name, Begin_Date, End_Date
"term","add","spr2015","Spring 2015","01/07/2015","05/25/2015"

User

Adds, modifies, or deletes users in GradesFirst. Does NOT assign the user to a Group or Term, which is performed in Grouping and Registration respectively. Simply importing students does not make them viewable.  Required.  The unique key for this record type is the Primary User ID.

Columns

Required

Data TypeMax LengthForeign Key

Description

"user"

Yes

varchar

Record type

Action

Yes

varchar

This value will be "add" to add or update a user 

Primary

User ID

Yes

varchar255

A unique identifier for this user.

Is Active?

Yes

varchar1

"Y" for active and "N" for inactive.

Can Login?

Yes

varchar1

If marked “Y”, imported users will be able to access GradesFirst once they have activated their GradesFirst account (see Send Activation Email below for more details).  If “N”, then specified users will not have the ability to access GradesFirst. 

Note: professors will NOT need the ability to log into GradesFirst if they will only be participating in progress report campaigns. 

Send Activation Email

Yes

varchar1

“Y” to send or “N” to not send activation email.  In order for users to gain access to GradesFirst, they will first need to activate their account.  This is done when the user clicks the “Activate my GradesFirst account” link in their activation email.  If this option is marked “Y”, users will automatically receive an activation email when they are imported into GradesFirst.  Once a user has been activated, they will not receive a second activation email even if this option is set to “Y”.
 

Most schools default this option to “N” to prevent these activation emails from being sent out until they are ready for users to begin logging into GradesFirst.  When ready, these activation emails can be sent out via the GradesFirst user interface.

First Name

Yes

varchar255


Last Name

Yes

varchar255


Alternate User ID

No

varchar255

A secondary identifier for the user that must also be unique among all other Secondary and Unique Ids.

Role ID

Yes

varchar255role:external_id

The initial GradesFirst role for the user.

Email

No

varchar255

While emails are not required, not providing them will greatly limit the functionality in GradesFirst (e.g. Progress Reports/Early Alerts, Communications).  Therefore we strongly urge you to include all emails in your import file.

Alternate Email

No

varchar255


Address 1

No

varchar255


Address 2

No

varchar255


City

No

varchar255


State

No

varchar255


Postal Code

No

varchar255


Home Phone

No

varchar255

Format should be: 555-123-9876

Note: If your school is located outside the US, please do not import phone numbers that include your country code.

Cell Phone

No

varchar255

Format should be: 555-123-9876

Note: If your school is located outside the US, please do not import phone numbers that include your country code.

Work Phone

No

varchar255

Format should be: 555-123-9876

Note: If your school is located outside the US, please do not import phone numbers that include your country code.

Gender

No

varchar255

Valid values include “M”, “MALE”, “F”, “FEMALE”, “O”, “OTHER”

Ethnicity

No

varchar255


Date Of Birth

No

date10

Value must be in mm/dd/yyyy format.

Total Credit Hours

No

float9


Credit Hours Attempted

No

float9


Major ID

No

varchar255major:external_id

The unique identifier for the major for this student.

Student Enrollment Status

No

varchar255

Custom text that described a student’s enrollment status

Student Enrollment Goal

No

varchar255

Custom text that described a student’s enrollment goal

PIN

No

varchar255

A number used as a digital signature mechanism for staff members who take attendance.

SSO ID

No

varchar255

A unique user identifier that can be used with Single Sign-On.

ACT Total

No

integer11

This should be a numeric value

ACT English

No

integer11

This should be a numeric value

ACT Reading

No

integer11

This should be a numeric value

ACT Math

No

integer11

This should be a numeric value

ACT Science

No

integer11

This should be a numeric value

SAT Total

No

integer11

This should be a numeric value

SAT Verbal

No

integer11

This should be a numeric value

SAT Math

No

integer11

This should be a numeric value

High School GPA

No

float9

Valid values include:  3.12, 0.1234

1st Generation College Student?

No

varchar1

Supported values: “Y” or “N”

Father Education

No

varchar255

Supported values:    "None", "High School Diploma", "2 Year College Degree", "4 Year College Degree", "Graduate Degree"

Mother Education

No

varchar255

Supported values:    "None", "High School Diploma", "2 Year College Degree", "4 Year College Degree", "Graduate Degree"

High School Zip Code

No

varchar255

This should be a numeric value

Household Income

No

integer11

This should ONLY be a numeric value, excluding commas and symbols (e.g. “$”).  Valid examples are: 100000, 85000, 63500

Single Parent Family?

No

varchar1

Supported values: “Y” or “N”

Transfer GPA

No

float9

Valid values include:  3.12, 2.1234

Home College

No

varchar255


Receive Text MessagesNovarchar1

Support values: "Y" or "N"
This value defines whether a student will, by default, receive text messages.

Exchange Email

No

varchar255
The value used to synchronize this user's Exchange calendar when Exchange calendar integration is enabled.
Card Swipe IDNovarchar255
A secondary identifier for the user that must also be unique among all other Secondary and Unique Ids.
Pell EligibleNovarchar1
"Y" for eligible and "N" for ineligible.
Unmet NeedNofloat

10


This should ONLY be a decimal value, with two places after the decimal. Valid examples are:  300000.12, 0.12
PronounsNovarchar35
This should be a string value that includes only alphabet characters and a "/". The pattern "word/word" must be followed. All pronouns are converted to lowercase on import. Valid examples include: "she/her", "he/him".
Last FAFSA YearNovarchar25
This should be a year value. Valid examples are: 1995, 2005.
Legal First NameNovarchar255
The legal first name of the user, if it's different than what is entered for the user's first name. Valid examples are: "Harriet", "Steve".

Example

GradesFirst Import Version=6
#Object, Action, Primary_User_Id, Is_Active?, Can_Login?, Send_Activation_Email, F_Name, L_Name, Alt_User_Id, Role_Id, Email, Alt_Email, Add1, Add2, City, State, Zip, H_Phone, C_Phone, W_Phone, Gender, Ethnicity, DOB, Total_Credits, Total_Credits_Att, Major_Id, Enroll_Status, Enroll_Goal, PIN, SSO_Id, ACT_Total, ACT_Eng, ACT_Read, ACT_Math, ACT_Sci, SAT_Total, SAT_Verbal, SAT_Math, HS_GPA, 1st_Gen?, F_Ed, M_Ed, HS_Zip, Income, Single_Family, 
Transfer_GPA, Home_College, Rec_Text?, Pell_Eligible, Unmet_Need, Pronouns, Last_Fafsa_Year, Legal_First_Name

"user","add","905123","Y","Y","N","Frank","Dux","FD5123","Student","frank.dux@gradesfirst.com","alt.email@gradesfirst.com","100 Main Street","Apt 200","Home Town","AL","35004","205-123-1234","205-987-9876","","M", "Caucasian","04/03/1971","60","62","ENG","Full Time Student","Graduate","1234","SSO_1234","30","29","31","30","30","2100", "2200","2000","4.10", "Y","BS","BA", "35004","120000","Y","","","Y","Y","10000.00","he/him","2010","Franklin"

Visit

Represents a visit or check-in to the system for the specified user, location, service, and course.

Columns

Required

Data TypeMax LengthForeign Key

Description

"visit"

Yes

varchar

Record type.

Action

Yes

varchar

The value will be "add" to add a visit  

Location Name

Yes

varchar255location:name

The name of the location where the student checked in.

Primary User ID

Yes

varchar255user:primary_user_id

The unique identifier for the user associated with the visit.

Student Service NameYesvarchar255
The name of the student service associated with the visit.

Group ID

Yes

varchar255group:external_id

The unique identifier for a group.

Check-in DateTimeYesdatetime16
Value must be in mm/dd/yyyy hh:mm format.
Check-out DateTimeYesdatetime16
Value must be in mm/dd/yyyy hh:mm format.
Course NameNovarchar255course:nameThe name of the course associated with the visit.
Care Unit NameNovarchar255care_unit:nameThe name of the care unit associated with the visit. Required if student service name matches services in multiple care units.

Example

GradesFirst Import Version=5
#Object, Action, Location_Name, Primary_User_ID, Student_Service_Name, Group_ID, Check_In_DateTime, Check_Out_DateTime, Course_Name
"visit","add","Student Center","012345","Career Counseling","GradesFirst University","01/23/1984 12:30","01/23/1984 13:30","Mathematics"

Imports Dashboard

The Imports Dashboard allows you to view all your current imports and a 7-day history of your imports. It displays imports that are currently running, imports that are waiting on the server queue, recent imports that were successful, and recent imports that had errors.

To open the imports dashboard:

  1. Log into GradesFirst
  2. Click on the Admin Tab
  3. Click the “Data Import” link in the Import/Data API section

Frequently Asked Questions

Who owns the data that a school uploads and inputs into the GradesFirst system?     

Clients own their data contained within GradesFirst.  This is clearly stated in the service agreement as well.

When classes are dropped from a student’s schedule, either through the data upload or GradesFirst interface, does GradesFirst mark classes as dropped or delete them from the student’s schedule entirely?

GradesFirst will mark the class as dropped so the user can tell which classes have been dropped during the term.

 If a student that is currently in the GradesFirst system and is not included in a subsequent file upload, will the student’s record be affected in any way?

No.  If a student is not included in the data import file, their record in GradesFirst will not be affected.  Removing a student from GradesFirst requires action from a user.

Changes From Version 5

Section Meeting

  1. Added a new record type for section meetings.

Section

  1. Removed the Begin Date column.
  2. Removed the End Date column.
  3. Removed the Start Time column.
  4. Removed the End Time column.
  5. Removed the Meeting Days column.
  6. Removed the Location column.