pickAtime Database Import File List

  • Students import file
  • Teachers import file
  • Classes import file
  • Enrollment import file
  • Parents import file
  • Parent Relationships import file
Import File Requirements
This section provides quick notes for each of the import files. Import files must be saved as tab delimited text files.

Students
- Requires 4 fields
- StudentID must be unique
- Uses date of birth as security value
- Duplicate the student for dual household families.
StudentID
 First
 Last
 SecurityValue
SMITJ011
 Johnny
 Smith
 12/01/2001
SMITA012
 Andrew
 Smith
 12/01/2001
JONEA011
 Alice
 Jones
 03/01/2002
JONEA011-DUAL Alice Jones
 03/01/2002



Teachers
- 7 fields
- TeacherID must be unique
- Use AccessLevel 3 for Resource Admin
- Leave password blank
TeacherID
 First
 Last
 Email
 Password
 Room
 AccessLevel
93
 Nordan
 Oakley
 house2b@ls.org
2B
 3



Classes
- 2 fields
- ClassID must be unique
ClassID
 TeacherID
2B
 93



Enrollment
- 2 fields
- Duplicate the student for dual household families.
StudentID
 ClassID
SMITJ011 2B
SMITA012 2B
JONEA011 2B
JONEA011-DUAL 2B



Parents
- 4 fields
- Use Finalsite ID (Senior Systems EntityNo key) for ParentID. This should appear as the Senior System (new) key within the Finalsite Constituent Manager.
- ParentID and Email must be unique
ParentID
 First Last
 Email
43562
 Ted
 Smith
 ted@domain.co
43563
 Jenny
 Smith
 jenny@domain.co
453 Fred Jones fred@domain.co
454 Martha Jones martha@domain.co



Parent Relationships

- 2 fields

- Use the duplicate student for Parent2 in dual households. This will allow Parent2 to schedule completely separate conferences from Parent1.

ParentID
 StudentID
43562 SMITJ011
43563 SMITJ011
43562 SMITJ012
43563 SMITJ012
453
 JONEA011
454
 JONEA011-DUAL
Data Management
This section provides quick notes to manage the data for each import file. Although a large part of it is easily exported straight from Senior Systems, the data needs to be massaged and double-checked through Excel. Additionally, ParentID keys should be exported straight from Finalsite to eliminate any issues from the SSO.

Export Finalsite Data:
Export the data within the Finalsite Constituent Manager. Contituent Manager >> Settings >> Export Data:


Export to Excel. We will require the SENIORSYSTEMS3_PARENTS key.


Excel Features Used:
Conditional Formatting
 Filter

Quickly highlight duplicate items.
Used when generating dual household StudentID keys.
 Display only the highlighted items.
Used when generating dual household StudentID keys.


Excel Formulas Used:

LOOKUP
 COUNTIF nested inside IF statement
 
=LOOKUP(A1,B:B,C:C)
 
=IF(COUNTIF($A$1:A1,A1)=1,A1&"-DUAL","")
Take the Value from A1, Compare it to Values in column B, if match found display Value from column C.
Quickly filter out specific results. Used when extracting ParentID keys from Finalsite export.
 Take the Value from A1 and count if it matches the Values in Column A. Take the Value of the first occurrence and add"-DUAL" to the end. Do not display anything if not first occurrence.
Used to generate dual household StudentID keys. Having separate StudentID keys assigned to parents allows for completely separate scheduling.

Saving the File:
Once the Excel sheets are prepared, save as CSV and open in a text editor such as TextWrangler. The CSV files needs to be saved as a tab-delimited text file.

Original Data:


Replace the Commas with Tabs. Search >> Find (⌘+F)


Resulting Data:


Save as TXT:

Data Import
The data files are imported through the Importer module (found inside the Reporting module). These files should be imported in order:
1 - Students
  • Data Type: People
  • Field ID: StudentID
2 - Teachers
  • Data Type: People
  • Field ID: TeacherID
3 - Classes
  • Data Type: School
  • Event: choose the current event
  • File Type: classes
4 - Enrollment
  • Data Type: School
  • Event: choose the current event
  • File Type: enrollment
5 - Parents
  • Data Type: People
  • Field ID: ParentID
6 - Parent Relationships
  • Data Type: School
  • Event: choose the current event
  • File Type: parent - student relationship



Resource Admin Setup
The Resource Admin access allows users to view the reporting module and make changes in the scheduling module. Resource Admin should be given to teachers (just to their individual classrooms) and office staff (access to all classrooms). Higher access rights allow users to make changes to the event itself (Event Administrator) and the pickAtime program setup (Administrator).

Edit the Contact Info. for a specific user. Set the Vendor Access to Resource Administrator. While in the Contact Info. section, verify that the ParentID matches the Finalsite ID for SSO.



In the Vendor Access tab, set the Event to the current event and select the Resource (class) to give access.