pickAtime Database Import – NAVI Works Blog

Database setup notes for the appointment/conference reservation system.

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	Ashley	baker	abaker@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: