ADXFieldsInformation - engineerica/accudemiaext GitHub Wiki

Introduction

Accudemia Data Exchange (ADX) uploads your CSV files into the Accudemia Amazon cloud server's internal SQL databases that use the following columns. This information should help most users build the import files needed as the field lengths are provided, key fields are identified, and the field formats are shown. If you are developing a plug-in for ADX it might also be extremely helpful:

Table
Field Imported? Notes
____________________________________ __________ __________________
Terms
Name - text (length: 50) Required Primary Key
Start Date - Date (MM/DD/YYYY) Required
End Date - Date (MM/DD/YYYY) Required
____________________________________ __________ __________________
Center
Name - text (length: 255) Required Primary Key
____________________________________ __________ __________________
User Groups
Name - text (length: 255) Required Primary Key
____________________________________ __________ __________________
Classroom
Name - text (length: 255) Required Primary Key
____________________________________ __________ __________________
Student
User ID - text (length: 30) Required Primary Key
First Name - text (length: 255) Required
Middle Name - text (length: 255) Optional
Last Name - text (length: 255) Required
User Alternate ID - text (length: 30) Optional
User Other ID 1 - text (length: 30) Optional
User Other ID 2 - text (length: 30) Optional
User Other ID 3 - text (length: 30) Optional
User Other ID 4 - text (length: 30) Optional
Password - text (length: 512) Optional
Email - text (length: 255) Optional
Phone Number - text (length: 255) Optional
Address - text (length: 255) Optional
City - text (length: 255) Optional
State - text (length: 255) Optional
Zip Code - text (length: 255) Optional
Comments - text (length: unlimited) Optional
Active - boolean (True/False) Optional
____________________________________ __________ __________________
Tutor
User ID - text (length: 30) Required Primary Key
First Name - text (length: 255) Required
Middle Name - text (length: 255) Optional
Last Name - text (length: 255) Required
User Alternate ID - text (length: 30) Optional
User Other ID 1 - text (length: 30) Optional
User Other ID 2 - text (length: 30) Optional
User Other ID 3 - text (length: 30) Optional
User Other ID 4 - text (length: 30) Optional
Password - text (length: 512) Optional
Email - text (length: 255) Optional
Phone Number - text (length: 255) Optional
Address - text (length: 255) Optional
City - text (length: 255) Optional
State - text (length: 255) Optional
Zip Code - text (length: 255) Optional
Comments - text (length: 255) Optional
Active - boolean (True/False) Optional
____________________________________ __________ __________________
Instructor
User ID - text (length: 30) Required Primary Key
First Name - text (length: 255) Required
Middle Name - text (length: 255) Optional
Last Name - text (length: 255) Required
User Alternate ID - text (length: 30) Optional
User Other ID 1 - text (length: 30) Optional
User Other ID 2 - text (length: 30) Optional
User Other ID 3 - text (length: 30) Optional
User Other ID 4 - text (length: 30) Optional
Password - text (length: 512) Optional
Email - text (length: 255) Optional
Phone Number - text (length: 255) Optional
Address - text (length: 255) Optional
City - text (length: 255) Optional
State - text (length: 255) Optional
Zip Code - text (length: 255) Optional
Comments - text (length: 255) Optional
Active - boolean (True/False) Optional
____________________________________ __________ __________________
Subject Areas / Courses
Name - text (length: 255) Required Primary Key
Prefix - text (length: 15) Required Prefix+Code=Secondary Key
Code - text (length: 15) Required Prefix+Code=Secondary Key
Description - text (length: unlimited) Optional
____________________________________ __________ __________________
Class
Registration Number - text (length: 30) Required Primary Key
Short Name - text (length: 100) Required Secondary Key
Postfix - text (length: 15) Required [Subject Area Prefix]+[Subject Area Code]+[ClassPostfix]=ClassID (Tertiary Key)
Class's Days of Week - (Mon/Tue/Wed/Thu/Fri/Sat/Sun) Optional Required for Classroom Attendance
Class's Start Time - military time (HH:MM) Optional Required for Classroom Attendance
Class's End Time - military time (HH:MM) Optional Required for Classroom Attendance
Other - text (length: 20) Optional
Comments - text (length: 200) Optional
Cut-off - number Optional
Tardy - number Optional
Active - boolean (True/False) Optional

If you are using the CSV Data Source, boolean fields can be specified using any of the following value pairs:

1/0, Y/N, T/F, True/False, Yes/No (all case insensitive)

How does the ADX import engine work?

The ADX engine finds duplicates in only the key fields of each entity. That way if you have multiple users with the same name but with different IDs, multiple users are imported.

The key fields used for finding duplicates are the following:


Term:

  • Records with the same Term Name are considered duplicates, and thus only one is kept.

Center:

  • Records with the same Center Name are considered duplicates, and thus only one is kept.

Classroom:

  • Records with the same Classroom Name are considered duplicates, and thus only one is kept.

Student:

  • Records with the same Student User ID are considered duplicates, and thus only one is kept.

Tutor:

  • Records with the same Tutor User ID are considered duplicates, and thus only one is kept.

Instructor:

  • Records with the same Instructor User ID are considered duplicates, and thus only one is kept.

Subject Area / Courses:

  1. Records with the same Subject Area Name are considered duplicates, and thus only one is kept.
  2. Records with the same Prefix and Code are considered duplicates, and thus only one is kept.

Class:

  1. Records with the same Class Registration Number (CRN) are considered duplicates, and thus only one is kept.
  2. Records with the same Class Short Name are considered duplicates, and thus only one is kept.
  3. Records with the same Subject Area Name, Term Name, and Postfix are considered duplicates, and thus only one is kept.

Student Enrollment:

  • Records with the same Student User ID and Class Registration Number (CRN) or Class Short Name are considered duplicates, and thus only one is kept.

Tutor Assignment:

  • Records with the same Tutor User ID, Subject Area Name, and Term Name are considered duplicates, and thus only one is kept.

Instructor Assignment:

  • Records with the same Instructor User ID and Class Registration Number (CRN) or Class Short Name are considered duplicates, and thus only one is kept.

User Group:

  • Records with the same User Group Name are considered duplicates, and thus only one is kept.

User Group Members:

  • Records with the same User Group Name and Student User ID are considered duplicates, and thus only one is kept.

Center Assignment (Subject Areas assigned to Center)

  • Records with the same Subject Area Name and Center Name are considered duplicates, and thus only one is kept.

Export fields

When exporting data you have all the same fields above (that were on the import tables/fields) plus several additional areas based on data collected such as visits, appointments, and more. Check the available fields below:

Table
Field Exportable? Notes
____________________________________ __________ __________________
Session Logs
Log Type Yes This shows if they are "Student" or "Tutor" Logs.
Sign-in Time Yes
Sign-out Time Yes
Center Name Yes
User ID Yes This is the User Primary Key (Student or Tutor).
User First Name Yes
User Middle Name Yes
User Last Name Yes
User Alternate ID Yes
Tutor ID Yes This is the Tutor Primary Key (Only shows on Student Logs).
Tutor First Name Yes
Tutor Middle Name Yes
Tutor Last Name Yes
Instructor ID Yes This is the Instructor Primary Key.
Instructor First Name Yes
Instructor Middle Name Yes
Instructor Last Name Yes
Subject Area / Course Name Yes This is the Subject Area Primary Key and "Unspecified" means they did not make this selection.
Subject Area Code Prefix Yes
Subject Area Code Yes
Class Postfix Yes
Class Reg Number (CRN) Yes
Class Short Name Yes Class Area Primary Key.
Services Yes If "Unspecified" then this means they did not make this selection.
Comments Yes
Log ID Yes Session Log Primary Key
Appointment ID Yes This is the Appointment Primary Key (Only shows if there's an assoc. appt.)
____________________________________ __________ __________________
Appointments
Semester Name Yes This is the Semester Primary Key.
Center Name Yes This is the Center Primary Key.
Student User ID Yes This is the Student Primary Key.
Student First Name Yes
Student Middle Name Yes
Student Last Name Yes
Tutor User ID Yes This is the Tutor Primary Key.
Tutor First Name Yes
Tutor Middle Name Yes
Tutor Last Name Yes
Appt. Start Time Yes
Appt. End Time Yes
Subject/Course Code Yes Combines the "Subject Area Code Prefix" and the "Subject Area Code")
Subject Area Name Yes This is the Subject Area / Course Primary Key.
Is No Show Yes
Is Canceled Yes
Is Voided Yes
Excuse Yes
Created By Yes This is the appointment scheduler.
Notes Yes
Appointment ID Yes This is the Appointment Primary Key (should match with Session Logs Appt. ID).