Difference between revisions of "SAM V1:Class Schedules"

From CIDI Wiki
Jump to navigation Jump to search
Line 88: Line 88:


[[SAM V1#Tables|Back to SAM V1 Tables]]
[[SAM V1#Tables|Back to SAM V1 Tables]]
=='''Banner Data'''==
There are two SAM tables that can be data imported from Banner: '''student and class schedules''' .  The Banner data extract format can be either a CSV file or a tab-delimited text file.  Ideally the end-user should be able to run the Banner extract on demand and save the data to a local file.  This local file would then be loaded by the end-user into SAM using the QuickBase [http://www.quickbase.com/help/default.html#importing_data.html import data] feature. 
'''EACH DSP SHOULD SUBMIT A COPY OF THE STUDENT AND CLASS SCHELULES DATA TO THEIR BANNER IT REPRESENTATIVE.'''  From that point the Banner IT representative can assemble a script containing all students receiving disability services.  Now the DSP has a list from Banner that contains all of the required fields for SAM.  The Banner IT representative should make the script into a process that the DSP can use weekly to update SAM.
The Banner Student extract should contain a single row for each student.  The student extract would not contain a row for every student in Banner, so a mechanism must be in place within Banner to identify or flag disability services students, as only those students should be included in the extract. 
The Banner class schedule extract should contain a single row for each class of a student.  For example, if a student is taking three classes, there would be three rows of data, one for each class with the same student identifier.  The class schedule extract would not contain a row for every student class in Banner, so a mechanism must be in place within Banner to identify or flag disability services students, as only the class schedules of those students should be included in the extract.
The SAM application contains the follow data elements for class schedule:
{| border="1" cellspacing="1" cellpadding="1"
!Field
!Data Type
!Possible Banner Feed
|-
|Unique Student ID (ss#)
|varchar
|yes
|-
|Term
|varchar
|yes
|-
|Year
|varchar
|yes
|-
|Course
|varchar
|yes
|-
|Description
|varchar
|The name the student prefers to go by
|-
|Instructor
|varchar
|yes
|-
|Instructor Phone
|varchar
|
|-
|Instructor Email
|varchar
|
|-
|Location
|varchar
|yes
|-
|Monday
|boolean
|yes
|-
|Tuesday
|boolean
|yes
|-
|Wednesday
|boolean
|yes
|-
|Thursday
|boolean
|yes
|-
|Friday
|boolean
|yes
|-
|Saturday
|boolean
|yes
|-
|Sunday
|boolean
|yes
|-
|Start Time
|timeofday    HH:MM AM OR PM
|yes
|-
|End Time
|timeofday    HH:MM AM OR PM
|yes
|-
|Notes
|varchar
|
|-
|}
[[SAM V1#Tables|Back to SAM V1 Tables]]
=='''PeopleSoft'''==
Instructions on Synchronizing PeopleSoft and SAM Student Data
To synchronize PeopleSoft data with SAM data, you will perform an [http://www.quickbase.com/help/default.html#importing_data.html import data] of a flat .txt 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#. 
The Microsoft Access application ps2sam.mdb is 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
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 Business Plan
ERRS DSSVD Code ERSS – D Code
ERRS DSSPS Code ERSS – A Code
Cross-Referencing or Building the SAM Import Files
The below process is used to pull PeopleSoft data into SAM.  It 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.
A Microsoft Access application is used to compare the files and generate two flat files that can be imported into SAM.
Microsoft Access Application
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
IF THIS MACRO DOES NOT WORK YOU HAVE TO CHECK YOUR DESIGN VIEWS FOR QUERY. USUSALLY THE SQL PART OF THE QUERY IS CORRUPT. FIRST CHECK MATCHED QUERY. CLICK ON THE QUERY TABLE, RIGHT CLICK MATCHED QUERY, DESING VIEW AND REPLACE THE DATA WITH THE LIST BELOW:
SELECT Students.[Student ID#], IIF(PeopleSoft.[Student Group Status]='A','ACTIVE',IIF(PeopleSoft.[Student Group Status]='I','INACTIVE')) AS Status, PeopleSoft.[PeopleSoft ID] AS [Student ID], PeopleSoft.[Last Name] AS [Last Name], PeopleSoft.[First Name] AS [First Name], PeopleSoft.[Middle Name] AS [Middle Name], PeopleSoft.Address AS [Address 1], PeopleSoft.City AS City, PeopleSoft.State AS State, PeopleSoft.[Postal Code] AS [Zip Code], IIF(ISNULL(PeopleSoft.[Preferred Phone Number]),,REPLACE(REPLACE(REPLACE(PeopleSoft.[Preferred Phone Number],'/',),'-',),' ',)) AS Phone, IIF(ISNULL(PeopleSoft.[Alternate Phone Number]),,REPLACE(REPLACE(REPLACE(PeopleSoft.[Alternate Phone Number],'/',),'-',),' ',)) AS [Alternate Phone], PeopleSoft.[Alternate Phone Type] AS [Alternate Phone Type], PeopleSoft.Email AS Email, PeopleSoft.[Ethnic Grp] AS Ethnicity, IIf(PeopleSoft.Gender='M','MALE',IIf(PeopleSoft.GENDER='F','FEMALE')) AS Gender, PeopleSoft.DOB AS [Date of Birth], PeopleSoft.[Academic Level] AS [Academic Level], PeopleSoft.[Plan Code] AS [Business Plan], PeopleSoft.[ERSS DSSVD Code] AS [ERSS - D Code], PeopleSoft.[ERSS DSSPS Code] AS [ERSS - A Code] FROM PeopleSoft LEFT JOIN Students ON PeopleSoft.[PeopleSoft ID]=Students.[Student ID];
NOW DO THE SAM WITH SAM STUDENTS WITHOUT MATCHING PEOPLESOFT. CLICK ON THE QUERY TABLE, RIGHT CLICK ON SAM STUDENTS WITHOUT MATCHING PEOPLESOFT, DESING VIEW AND REPLACE THE DATA WITH THE LIST BELOW:
SELECT Students.[Student ID#], Students.[Student ID], Students.[First Name], Students.[Last Name], Students.Status FROM Students WHERE NOT Students.[Student ID] IN SELECT PeopleSoft.[PeopleSoft ID] FROM PeopleSoft);
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.
[[SAM V1#Tables|Back to SAM V1 Tables]]
==Digger==
[http://diggersolutions.com/blogs/diggersolutions/about.aspx Digger] is the easiest [http://www.quickbase.com/help/default.html#importing_data.html import data] that can be incorporated into SAM V1. Digger exports into an excel spread sheet which then can be imported into SAM V1.

Revision as of 10:09, 25 June 2009

The class schedules table contains the class schedules for each student. A class schedule record exists for each class taken by the student, so if the student has registered for 3 classes, then the one student parent record will have 3 child class schedule records.

Back to SAM V1 Tables

Fields

The fields of the class schedules table are:

Field Description
Date Created Date record was created.
Date Modified Date record was last updated.
Class Schedule ID# Unique ID# of the record.
Record Owner User who owns the record.
Last Modified By User who last modified the record.
Course Name or number of the course. Course is required.
Description Description of the course.
End Time Time the course instruction ends.
Friday Check if the course is taught on Friday.
Instructor Name of instructor teaching the course.
Instructor Email Email of the instructor teaching the course.
Instructor Phone Phone number of the instructor teaching the course.
Location Physical location (i.e.: building and room number) where the course is taught.
Monday Check if the course is taught on Monday.
Notes Notes regarding the class schedule. The notes field is a cumulative field and date stamped with the date, time and user adding the notes.
Print Faculty Accommodation Notification Use this link to print the faculty accommodation notification letter for the student. The faculty accommodation notification letter contains the instructor and course name.
Saturday Check if the course is taught on Saturday.
Sunday Check if the course is taught on Sunday.
Start Time Time the course instruction begins.
Student ID The student the class schedule is related to. Student ID is required.
Term Term the student is taking the class: Fall, Winter, Spring, Summer, etc. Term is required.
Tuesday Check if the course is taught on Tuesday.
Thursday Check if the course is taught on Thursday
Wednesday Check if the course is taught on Wednesday.
Year Year the student is taking the class. Year is required.

Back to SAM V1 Tables