SAM V1:Students

From CIDI Wiki
Revision as of 14:25, 24 June 2009 by Ben (talk | contribs)
Jump to navigation Jump to search

The student table contains information about the students that receive disability services. A student record is created one time only. The student record is considered the master or parent record. The disabilities, accommodations, consent types, referrals, case notes, tasks, test scores, and class schedules are sub-tables or child records of the student record. The relationship is many children to one parent. In other words, the student record has many disabilities, accommodations, or case notes records related to it.

The student record should not be deleted without first deleting all the child records related to it. Otherwise, the child records would be orphaned.

Back to SAM V1 Tables

Status

The status field can be the value active, inactive, or prospective. It is used to determine if the student is using disability services.

The status field can be reset to inactive for all students at the beginning of each term. Then, as service providers work with students, the status field would be changed to active. By doing so, it would be possible to determine which students are active for the current term.

Fields

The fields of the students table are:

Field Description
Date Created Date record was created.
Date Modified Date record was last updated.
Student ID# Unique ID# of the record.
Record Owner User who owns the record.
Last Modified By User who last modified the record.
Academic Level Academic Level of student: Freshman, Sophomore, Junior, Senior, Graduate, etc.
Address Address of student. Format is address, city, state and zip code.
Alternate Address Alternate address of student. Format is address, city, state and zip code.
Alternate Email Alternative email address of student.
Alternate Phone Alternate phone number of student.
Alternate Phone Type Type of alternative phone number: Home, Cell, Fax, etc.
Campus The primary campus the student attends.
Date of Birth Date of birth of student.
Email Email address of student.
Enrollment Enrollment status of the student: Enrolled, Graduated, Transferred, Withdrawn, etc.
Ethnicity Ethnicity of student: Caucasian, African American, Hispanic, etc.
First Name First name of student. First Name is required.
Gender Gender of student: Male, Female, etc.
ID Unique student identifier. This field could contain university identification number, social security number, or something similar. ID is required.
Last Name Last name of student. Last Name is required.
Middle Name Middle name of student.
Name Formula field that is the concatenation of Last Name, First Name and Middle Name.
NEON Student The NEON student that corresponds to the SAM student. A SAM student is created in NEON if the SAM student receives AMAC services.
NEON Student Data Out of Synch Checkbox that is checked if the student data in NEON does not match the student data in SAM. SAM is considered the authoritative source. SAM and NEON student data must be kept in synch manually.
Notes Notes regarding the student. The notes field is a cumulative field and date stamped with the date, time and user adding the notes.
Phone Phone number of student.
Phone Type Type of phone number: Home, Cell, Fax, etc.
Preferred Name The name the student prefers to go by.
Print Accommodation Notification Use this link to print the general accommodation notification letter for the student. The general accommodation notification letter does not include the instructor and course name.
Service Provider The service provider assigned to the student.
Status Student status: Active, Inactive, Prospective. Status is required. The default value is Active.
US Citizen Checkbox to be checked if student is a citizen of the USA.
Why NEON Student Data Out of Synch A list of fields where the SAM student data does not match the NEON student data. Identifies which fields needs to be addressed in order for SAM and NEON student data to match.

Banner import information for SAMV1

PeopleSoft

To synchronize PeopleSoft data with SAM data, you will perform an import data of a flat .txt or .csv file into SAM. The flat file is essentially the data file provided by PeopleSoft, but with an additional column pre-pended to each row that uniquely identifies the student record in SAM. The additional column is the unique SAM key field Student ID#.

Either the Microsoft Access application ps2sam.mdb or the PeopleSoft to SAM Web Conversion Utility can be used to append the Student ID# to the PeopleSoft data file.

With the addition of the Student ID# column, the import of data into SAM then becomes an update import and updates records in SAM rather than creating new records. If the Student ID# value in the data file row exists within SAM, then the imported row of data updates the existing record in SAM. If the Student ID# value in the data file row does not exist within SAM, then the imported row of data creates a new student record in SAM during the import. Source Data

Source Data

PeopleSoft

The data file received from PeopleSoft is a Microsoft Excel file consisting of a row for each student.

SAM

In SAM, the structure of the student table contains most of the fields referenced in the PeopleSoft file, plus many more. There are fields for Student ID, First Name, Last Name, Address1, Address2, City, State, Zip Code, Email, Phone Number, Academic Level, and Business Plan. However, Student ID is not the key field in SAM. Instead, SAM contains a unique key field on each student record called Student ID#. It is an auto-assigned integer value that is meaningless other than uniquely identifying the student record. To locate or update a student record in SAM, you use the Student ID#.

Mapping

PeopleSoft File Column SAM Student Table field Notes
Student Group Effective Date    
Student Group Status Status A = ACTIVE, I = INACTIVE
PeopleSoft ID Student ID  
Last Name Last Name  
First Name First Name  
Middle Name Middle Name  
Address Address1  
City City  
State State  
Postal Code Zip  
Preferred Phone Number Phone Formatting removed
Alternate Phone Number Alternate Phone Formatting removed
Alternate Phone Type Alternate Phone Type  
Email Email  
Ethnic Grp Ethnicity  
Gender Gender M = MALE, F = FEMALE
DOB Date of Birth  
Cum GPA    
Academic Level Academic Level  
Plan Code Business Plan  
ERRS DSSVD Code ERSS – D Code  
ERRS DSSPS Code ERSS – A Code  

Building the SAM Import File using Microsoft Access

A Microsoft Access application can be used to compare the PeopleSoft and SAM Student files and generate a flat .txt file that can be imported into SAM.

Using the Microsoft Access application assumes the following:

  1. The PeopleSoft data file will not contain the SAM Student ID#.
  2. You are manually maintaining the Student ID in SAM.
  3. If there is not a match on Student ID between SAM and PeopleSoft, the PeopleSoft row will be inserted to SAM.
  4. If there is a match on Student ID between SAM and PeopleSoft, the PeopleSoft row will update the SAM row.

The Access application contains the following objects:

Tables:

PeopleSoft A link table to the PeopleSoft flat file.
Students A link table to the SAM export student table flat file.

You want your tables to be link tables so that all you need to do is replace the underlying files to refresh the table data.

Queries:

Matched Query A query that joins PeopleSoft to SAM using Student ID that selects all PeopleSoft rows and only the SAM rows where there is a match. The output columns are the SAM Student ID# and the columns from the PeopleSoft file that map to the columns in the SAM student table. The status column is changed from A or I to ACTIVE or INACTIVE. The phone number columns have formatting removed. The gender column is changed from M or F to MALE or FEMALE. These are the rows that need to be updated or imported in SAM using the PeopleSoft data.

Macros:

Matched Query Export A macro that runs and exports the match query.

Process

The following process assumes the following:

  • The PeopleSoft file is called peoplesoft.xls.
  • The SAM student export is called Students.txt.
  • The Microsoft Access application is called ps2sam.mdb.
  • The match query output file is called Matched Query.xls.
  • The files and Microsoft Access application reside in folder called peoplesoft.
Step 1
  1. Open the PeopleSoft file peoplesoft.xls in the folder peoplesoft.
  2. In SAM application, use Customize, Import/Export, and export the student table as a tab-delimited file called Students.txt in the folder peoplesoft. Note that you must change the file extension from tsv to txt.
Step 2
  1. Open ps2sam.mdb
  2. Run macro Matched Query Export
  3. Save file in folder peoplesoft and call file Matched Query.xls.
  4. Quit ps2sam.mdb
Step 3
  1. Open the file Matched Query.xls.
  2. Review the file and ensure that a pending student does not already exist in SAM with a blank or different Student ID. Perhaps the student is listed in SAM with the student ID of lastname + firstname but in the PeopleSoft file their 9 digit ID number is listed.
  3. If the student is already in SAM, you need to update the SAM Student ID and start over at step 1. If not, a duplicate SAM student record will be created if you import the query file.
  4. Save the Matched Query.xls file as a tab delimited .txt format file. This is necessary because QuickBase cannot import an Excel file but can import a tab delimited text file.
Step 4
  1. In SAM application, use Customize, Import/Export, and import into the student table the Matched Query.txt file. Check the box that states the first row is a list of field names. The import should state that the import is an update import. Be careful to review the fields that the data will import into and ensure that the columns are in the correct order. Student ID# should be the first columns followed by the columns in this order: Status, Student ID, Last Name, First Name, Middle Name, Address1, City, State, Zip, Phone, Alternate Phone, Alternate Phone Type, Email, Ethnicity, Gender, Date of Birth, Academic Level, Business Plan, ERRS – D Code, ERRS – A Code.
  2. You are not required to import all columns, but you must select to import the Student ID#. This import will update SAM student records with the PeopleSoft data.

Building the SAM Import File using the Web Utility

A web utility can be used to compare the PeopleSoft and SAM Student files and generate a flat .csv file that can be imported into SAM.

The web utility assumes the following:

  1. The PeopleSoft data file will not contain the SAM Student ID#.
  2. You are manually maintaining the Student ID in SAM.
  3. If there is not a match on Student ID between SAM and PeopleSoft, the PeopleSoft row will be inserted to SAM.
  4. If there is a match on Student ID between SAM and PeopleSoft, the PeopleSoft row will update the SAM row.

Process

The following process assumes the following:

  • The PeopleSoft file is called peoplesoft.xls.
Step 1
  1. Save the PeopleSoft file peoplesoft.xls as a CSV file named peoplesoft.csv.
Step 2
  1. Log into the SAM application.
  2. On the dashboard, in the toolbox, click on the PeopleSoft to SAM Conversion Utility link.
Step 3
  1. Attach the peoplesoft.csv file.
  2. Select the option that you want to create a file to be imported into SAM that will update existing SAM student data as well as create new students not currently in SAM but listed in PeopleSoft.
  3. Click the submit button.
  4. Save the file studentimport.csv to your computer.
Step 4
  1. In SAM application, use Customize, Import/Export, and import into the student table the studentimport.csv file. Check the box that states the first row is a list of field names. The import should state that the import is an update import. Be careful to review the fields that the data will import into and ensure that the columns are in the correct order. Student ID# should be the first columns followed by the columns in this order: Status, Student ID, Last Name, First Name, Middle Name, Address1, City, State, Zip, Phone, Alternate Phone, Alternate Phone Type, Email, Ethnicity, Gender, Date of Birth, Academic Level, Business Plan, ERRS – D Code, ERRS – A Code.
  2. You are not required to import all columns, but you must select to import the Student ID#. This import will update SAM student records with the PeopleSoft data.

Further Reading

Import Data from Outside QuickBase