Creating Extracts (Exports)
Click Ad Hoc Reporting in the menu on the left
Select Filter Designer
In the middle of the page, under the Create New section select:
Filter Type: Query Wizard
Data Type: Student or Census/Staff
Finish by hitting the Create button
A new page will load with a blank red text field called *Query Name, this is where you will put in a name to identify the group of fields you are selecting for export, (ex. Connect Staff, Connect Student, Connect Classes, Connect Enrollment, Connect Guardians)
Staff Extract
(Data Type: Census/Staff)
Field Description | Location in All Fields Section | "Selected Fields" |
Staff Number | Person > Demographics > StaffNumber | individual.staffNumber |
First Name | Person > Demographic > firstName | individual.firstName |
Last Name | Person > Demographic > lastName | individial.lastName |
Person > Census > Person Contacts > email | pcontact.email | |
Active Employment | Staff > Assignment > districtEndDate | employment.districtEndDate |
Note: For employment.districtEndDate set the filter Parameters to ISNULL.
We typically use Staff Number as the Unique Identifier for our clients, but please confirm if your school uses any other ID numbers for Staff.
Student Extract
(Data Type: Student)
Field Description | Location in All Fields Section | "Selected Fields" |
Student Number | Student > Demographics > studentNumber | student.studentNumber |
First Name | Student > Demographics > firstName | student.firstName |
Last Name | Student > Demographics > lastName | student.lastName |
Student > Census > Student Contacts > email | pcontact.email | |
Active Enrollment | Student > Demographics > EndDate | student.EndDate |
Building Name | Student > School > Name | sch.name |
Grade Level | Student > Demographics > student.grade | student.grade |
End Year | Student > Demographics > endYear | student.endYear |
Note: For student.EndDate set the filter Parameters to ISNULL.
We typically use Student Number as the Unique Identifier for our clients, but please confirm if your school uses any other ID numbers for Students.
Classes Extract
(Data Type: Student)
Field Description | Location in All Fields Section | "Selected Fields" |
Section ID | Student > Learner > Schedule > Course/Section > SectionID | courseSection.SectionID |
Course Name | Student > Learner > Schedule > Course/Section > courseName | courseSection.courseName |
Description | Student > Learner > Schedule > Course/Section > courseNumber | courseSection.courseNumber |
Building Name | Student > School > Name | sch.name |
Term ID | Check details below | Check details below |
Classes Extract - Creating the Term ID
We will also need to add Term ID. Please follow the directions below to add a Term ID to the classes File.
To create a Term ID, click the Function button on the left side of your screen right under the All Fields list. For the name call this "Term ID", Under Function choose Concatenate. Then proceed to add the fields below. You can find these fields very easily by using the filter by option.
Field Description | Location in All Fields Section | "Selected Fields" |
Term Start | Student > Learner > Schedule > Course/Section > SectionSchedule > termStart | sectionSchedule.termStart |
Term End | Student > Learner > Schedule > Course/Section > SectionSchedule > termEnd | sectionSchedule.termEnd |
End Year | Student > Demographics > endYear | student.endYear |
Enrollment Extract
(Data Type: Student)
This extract should include unique identifiers for classes, students, and staff that can be cross-referenced between extracts.
Field Description | Location in All Fields Section | "Selected Fields" |
Section ID | Student > Learner > Schedule > SectionID | courseSection.SectionID |
Student Number | Student > Demographics > studentNumber | student.studentNumber |
Active Today (Student) | Student > Demographics > student.activeToday | student.activeToday |
Staff Number | Student > Learner > Schedule > Course/Section > StaffNumber | individual.staffNumber |
End Date (Roster) | Student > Learner > Schedule > Course/Section > roster.endDate | roster.endDate |
Note: For student.activeToday set the filter Parameters to TRUE and roster.endDate is set to IS NULL.
Guardian Extract
(Data Type: Student)
Field Description | Location in All Fields Section | "Selected Fields" |
Student Number | Student > Demographics > studentNumber | student.studentNumber |
Guardian First Name | Student > Census > Student Contacts > Household Contact Summary > First Name | contacts.firstName |
Guardian Last Name | Student > Census > Student Contacts > Household Contact Summary > Last Name | contacts.lastName |
Guardian Email | Student > Census > Student Contacts > Household Contact Summary > Email | contacts.email |
Guardian Phone | Student > Census > Student Contacts > Household Contact Summary > Cellphone | contacts.cellphone |
Guardian Status | Student > Census > Student Contacts > Household Contact Summary > Guardian | contacts.guardian |
Note: For contacts.guardian set the filter Parameters to True.
Setting up Scheduled Exports
After you have completed the creation of the export lists you would like to send, use the following steps to set up your scheduled exports
Scroll down in the menu on the left and click System Administration
Select Data Utilities
Select Data Extract Utility
Click the colored New button near the top left corner of the window
Fill out all fields exactly as shown below:
Extract Instance Name: Give the export a unique name like Apptegy_Connect_Staff, Apptegy_Connect_Student, Apptegy_Connect_Classes, Apptegy_Connect_Enrollment, and Apptegy_Connect_Guardian
File Name: Apptegy_Connect_Staff, Apptegy_Connect_Student, Apptegy_Connect_Classes, Apptegy_Connect_Enrollment, and Apptegy_Connect_Guardian
Delivery Mode: SFTP
File Extension: .csv
Column Delimiter: CSV
Include header row?: select
Include double quotes?: select
Next are the credentials needed for sending to our server:
Username: (Found in the secure link sent via email)
Password: (Found in the secure link sent via email)
Server: data-in.apptegy.net (Found in the secure link sent via email)
Port: 22
Path: can be empty
Select AdHoc
AdHoc Filter: using the drop-down bar, select the export that you created in the steps above
To finalize this export, you can now Test Extract, Test Connection, or Run Extract.
Warning: Make sure to hit Save in the top left corner first, otherwise when you select Test Connection or Test Extract, you will receive an error message.
Test Extract: This will download a file to the client's computer showing what the current export looks like.
Test Connection: This will bring up a pop-up confirming if the connection is successful with the credentials you've entered.
Run Extract: This will attempt to send a one-time transfer of the Extract (Export) to the Apptegy server for Apptegy to confirm the file has been received on our side if needed before setting up the scheduled sync.
Setting up your Timing
The final step is to use the final section Data Extract Schedule to set up the scheduled file transfers to the Apptegy server.
This includes the Frequency, Start Date, Time (of day), and Day of the Week.
Frequency is set to daily
Start Date is set to current date
Time is set to anytime overnight. Preferably 12AM to 3AM.