API Integration: Install Munetrix Infinite Campus Plugin

Provides step-by-step guidance to install the Munetrix Infinite Campus plugin to share student, teacher and roster data with Munetrix.

This article provides guidance on how to set up the data extracts required for the Munetrix Academic Module and Early Warning Module integrations.  This article covers setting up a connection to our SFTP server, installing the queries we use to extract data from Infinite Campus and setting up nightly data extractions to Munetrix.  

For each table that Munetrix requires data from we will set up an extraction query that will be scheduled to run nightly.  Each of these extractions will be captured in it’s own Data Extract Utility Instance.  Excepting the SQL query each instance will be set up in the same manner.  First we will define the general settings used for all instance, then we will provide the specific details required for each individual instance. 

 

General Instance Settings 

The steps below define how to set up the generic Data Extract Utility Instance settings that will be used for all data extractions. Follow these steps each time a new extraction instance is being created for each query. Note: Some values defined below will be provided by Munetrix at the time of installation, such as, the username and password. 

 

• Log into the Infinite Campus portal and search for “data extract utility set up” in the in “search campus tools” search bar. 
• Click on “new” to create new instance. 

• Fill all the fields as follows:  
  • Extract Instance Name:  See next section.  Each instance has a unique name. 
  • File Name:  See next section.  Each instance has a unique file name. 
  • Delivery Mode:  SFTP 
  • Column Delimiter:  TAB 
  • Character Set:  UTF-8 
  • Append Date to Filename:  No Date 
  • Check the box for Include Header Row 
  • Check the box for Include Double Quotes 
  • Username:  To be provided by Munetrix 
  • Password:  To be provided by Munetrix 
  • Server:  sftp.dynamo.city 
  • Port:  22 
  • Path:  /raw/<district name>/ 
  • SQL Query:  See next section.  Each instance will have a unique SQL query. 
  • Frequency:  Daily 
  • Start Date:  Today 
  • Start Time:  18:00 
• After filling in the unique fields for each instance (see next section) save the new instance and data will be delivered as scheduled in the details above. 

 

Instance Settings for Each Table Extraction

This section will define the details needed for each individual table extraction to be scheduled from Infinite Campus.  Each sub-section below will include the following details unique to each instance:

• Extract Instance Name 
• File Name 
• SQL Query 

 

Address

Extract Instance Name:  IC_MUN_address 

File Name:  address 

SQL Query: 

SELECT DISTINCT addressID, state, apt, city, zip, number, street, tag, prefix, dir, postOfficeBox, county FROM address; 

 

Attendance 

Extract Instance Name:  IC_MUN_attendance 

File Name:  attendance 

SQL Query: 

SELECT DISTINCT 

    t1.personID, 

    t1.calendarID, 

    t1.attendanceID, 

    t1.status, 

    t1.excuse, 

    t1.comments, 

    t1.date, 

    t2.endYear, 

    t2.schoolID 

FROM 

    (SELECT DISTINCT 

        personID, 

        calendarID, 

        attendanceID, 

        status, 

        excuse, 

        comments, 

        date 

     FROM 

        attendance) AS t1 

INNER JOIN 

    (SELECT DISTINCT 

        calendarID, 

        endYear, 

        schoolID 

     FROM 

        calendar 

     WHERE 

        calendarID IN (99, 100, 101, 102)) AS t2 

ON 

    t1.calendarID = t2.calendarID; 

 

Attendance Detail 

Extract Instance Name:  IC_MUN_attendance_detail 

File Name:  attendance_detail 

SQL Query: 

SELECT DISTINCT personID, calendarID, termID, termName, courseNumber, sectionID 

FROM v_AttendanceDetail  

WHERE v_AttendanceDetail.calendarID IN (99, 100, 101, 102); 

 

Behavior Event 

Extract Instance Name:  IC_MUN_behaviorEvent 

File Name:  behaviorEvent 

SQL Query: 

SELECT DISTINCT incidentID, typeID, calendarID, eventID from behaviorEvent; 

 

Behavior Incident 

Extract Instance Name:  IC_MUN_behaviorIncident 

File Name:  behaviorIncident 

SQL Query: 

SELECT DISTINCT 

    t1.incidentID, 

    t1.calendarID, 

    t1.timestamp, 

    t1.location, 

    t1.description, 

    t1.referralPersonID, 

    t2.endYear, 

    t2.schoolID, 

    t2.districtID 

FROM 

    (SELECT DISTINCT 

        incidentID, 

        calendarID, 

        timestamp, 

        location, 

        description, 

        referralPersonID 

     FROM 

        behaviorIncident) AS t1 

INNER JOIN 

    (SELECT DISTINCT 

        calendarID, 

        endYear, 

        schoolID, 

        districtID 

     FROM 

        calendar 

     WHERE 

        calendarID IN (99, 100, 101, 102)) AS t2 

ON 

    t1.calendarID = t2.calendarID; 

 

Behavior Referral 

Extract Instance Name:  IC_MUN_behaviorReferral 

File Name:  behaviorReferral 

SQL Query: 

SELECT DISTINCT * FROM behaviorReferral; 

 

Behavior Type 

Extract Instance Name:  IC_MUN_behaviorType 

File Name:  behaviorType 

SQL Query: 

SELECT DISTINCT typeID, code, name, districtID from behaviorType; 

 

Calendar 

Extract Instance Name:  IC_MUN_calendar 

File Name:  calendar 

SQL Query: 

SELECT  DISTINCT calendarID, endYear, schoolID, districtID from calendar;  

 

Census Contact Summary 

Extract Instance Name:  IC_MUN_census_contact_summary 

File Name:  census_contact_summary 

SQL Query: 

SELECT DISTINCT personID, studentNumber, districtID, state, apt, city, zip from v_CensusContactSummary;  

 

Contact 

Extract Instance Name:  IC_MUN_contact 

File Name:  contact 

SQL Query: 

SELECT DISTINCT personID, email, secondaryEmail, homePhone, workPhone, cellPhone, pager from contact;  

 

Course 

Extract Instance Name:  IC_MUN_course 

File Name:  course 

SQL Query: 

SELECT DISTINCT 

    t1.courseID, 

    t1.calendarID, 

    t1.number, 

    t1.name, 

    t1.homeroom, 

    t1.honorsCode, 

    t1.scedSequenceMax, 

    t2.endYear, 

    t2.schoolID, 

    t2.districtID 

FROM 

   course AS t1 

INNER JOIN 

    (SELECT DISTINCT 

        calendarID, 

        endYear, 

        schoolID, 

        districtID 

     FROM 

        calendar 

     WHERE 

        calendarID IN (99, 100, 101, 102)) AS t2 

ON 

    t1.calendarID = t2.calendarID; 

 

Employment Assignment 

Extract Instance Name:  IC_MUN_employmentassignment 

File Name:  employmentassignment 

SQL Query: 

SELECT DISTINCT 

    t1.personID, 

    t1.schoolID, 

    t1.grade, 

    t1.teacher, 

    t1.specialed, 

    t1.assignmentID, 

    t1.startDate, 

    t1.endDate, 

    t1.districtID, 

    t2.calendarID, 

    t2.endYear 

FROM 

    (SELECT DISTINCT 

        personID, 

        schoolID, 

        grade, 

        teacher, 

        specialed, 

        assignmentID, 

        startDate, 

        endDate, 

        districtID 

     FROM 

        employmentassignment) AS t1 

INNER JOIN 

    (SELECT DISTINCT 

        calendarID, 

        endYear, 

        schoolID,  

        districtID 

     FROM 

        calendar 

     WHERE 

        calendarID IN (99, 100, 101, 102)) AS t2 

ON 

    t1.schoolID = t2.schoolID AND t1.districtID = t2.districtID; 

 

Employment Credential 

Extract Instance Name:  IC_MUN_employmentcredential 

File Name:  employmentcredential 

SQL Query: 

SELECT DISTINCT personID  from employmentcredential;  

 

Enrollment 

Extract Instance Name:  IC_MUN_enrollment 

File Name:  enrollment 

SQL Query: 

SELECT DISTINCT 

    t1.personID, 

    t1.endYear, 

    t1.calendarID, 

    t1.enrollmentID, 

    t1.serviceType, 

    t1.endAction, 

    t1.disability1, 

    t1.disability2, 

    t1.homeless, 

    t1.migrant, 

    t1.immigrant, 

    t1.grade, 

    t1.mvUnaccompaniedYouth, 

    t1.homeSchooled, 

    t1.districtID, 

    t1.structureID, 

    t2.schoolID 

FROM 

    (SELECT DISTINCT 

        personID, 

        endYear, 

        calendarID, 

        enrollmentID, 

        serviceType, 

        endAction, 

        disability1, 

        disability2, 

        homeless, 

        migrant, 

        immigrant, 

        grade, 

        mvUnaccompaniedYouth, 

        homeSchooled, 

        districtID, 

        structureID 

     FROM 

        enrollment) AS t1 

INNER JOIN 

    (SELECT DISTINCT 

        calendarID, 

        endYear, 

        schoolID, 

        districtID 

     FROM 

        calendar 

     WHERE 

        calendarID IN (99, 100, 101, 102)) AS t2 

ON 

    t1.calendarID = t2.calendarID AND t1.districtID = t2.districtID; 

 

Enrollment End Status Type 

Extract Instance Name:  IC_MUN_enrollment_endstatus_type 

File Name:  enrollment_endstatus_type 

SQL Query: 

SELECT DISTINCT * from EnrollmentEndStatusType; 

 

Enrollment Start Status Type 

Extract Instance Name:  IC_MUN_enrollment_startstatus_type 

File Name:  enrollment_startstatus_type 

SQL Query: 

SELECT DISTINCT code, name from EnrollmentStartStatusType; 

 

Evaluation 

Extract Instance Name:  IC_MUN_evaluation 

File Name:  evaluation 

SQL Query: 

SELECT DISTINCT  personID, type, assessmentCategory, grade from evaluation; 

 

Grade Level 

Extract Instance Name:  IC_MUN_gradelevel 

File Name:  gradelevel 

SQL Query: 

SELECT DISTINCT calendarID, name from gradelevel; 

 

Grading Task Credit 

Extract Instance Name:  IC_MUN_gradingtaskcredit 

File Name:  gradingtaskcredit 

SQL Query: 

SELECT DISTINCT taskID, courseID from gradingtaskcredit; 

 

Graduation 

Extract Instance Name:  IC_MUN_graduation 

File Name:  graduation 

SQL Query: 

SELECT DISTINCT personID, cohortYearNCLB from graduation; 

 

Household Location 

Extract Instance Name:  IC_MUN_householdlocation 

File Name:  householdlocation 

SQL Query: 

SELECT DISTINCT * from householdlocation;  

 

Household Member 

Extract Instance Name:  IC_MUN_householdmember 

File Name:  householdmember 

SQL Query: 

SELECT DISTINCT memberID, householdID, personID, startDate, endDate from HouseholdMember; 

 

Identity 

Extract Instance Name:  IC_MUN_identity 

File Name:  identity 

SQL Query: 

SELECT DISTINCT personID, identityID, gender, firstName, suffix, lastName, middleName, raceEthnicity, hispanicEthnicity, dateEnteredUS from [identity];  

 

Mailing Address 

Extract Instance Name:  IC_MUN_mailingaddress 

File Name:  mailingaddress 

SQL Query: 

SELECT DISTINCT personID, addressLine1, districtID from v_MailingAddress; 

 

Person 

Extract Instance Name:  IC_MUN_person 

File Name:  person 

SQL Query: 

SELECT DISTINCT personID, staffNumber, studentNumber from person; 

 

Plan 

Extract Instance Name:  IC_MUN_plan 

File Name:  plan 

SQL Query: 

SELECT DISTINCT planID, personID, typeID, districtID from [Plan]; 

 

Plan State 

Extract Instance Name:  IC_MUN_planstate 

File Name:  planstate 

SQL Query: 

SELECT  DISTINCT planID, disability1 , disability2, disability1Name, disability2Name from planstate; 

 

Plan Type 

Extract Instance Name:  IC_MUN_plantype 

File Name:  plantype 

SQL Query: 

SELECT DISTINCT typeID, districtID, name From PlanType; 

 

Program Participation 

Extract Instance Name:  IC_MUN_ProgramParticipation 

File Name:  ProgramParticipation 

SQL Query: 

SELECT DISTINCT personID, active, code, name, districtID from v_ProgramParticipation; 

 

Related Pair 

Extract Instance Name:  IC_MUN_relatedpair 

File Name:  relatedpair 

SQL Query: 

SELECT DISTINCT personID1, personID2, guardian, seq, name from relatedpair; 

 

Roster 

Extract Instance Name:  IC_MUN_roster 

File Name:  roster 

SQL Query: 

SELECT DISTINCT sectionID, personID, trialID from roster; 

 

School 

Extract Instance Name:  IC_MUN_school 

File Name:  school 

SQL Query: 

SELECT DISTINCT schoolID, gradeType, type, name, number, city, county, zip, physicalAddress, phone, districtID  from school; 

 

Section 

Extract Instance Name:  IC_MUN_section 

File Name:  section 

SQL Query: 

SELECT DISTINCT sectionID, courseID, trialID from section;  

 

Section Placement 

Extract Instance Name:  IC_MUN_sectionplacement 

File Name:  sectionplacement 

SQL Query: 

SELECT DISTINCT sectionID, termID, trialID from sectionplacement;  

 

Section Staff Hisotry 

Extract Instance Name:  IC_MUN_sectionstaffhistory 

File Name:  sectionstaffhistory 

SQL Query: 

SELECT  DISTINCT personID, sectionID, startDate , endDate, role, trialID, assignmentID from sectionstaffhistory;  

 

Staff Enrollment 

Extract Instance Name:  IC_MUN_staffEnrollment 

File Name:  staffEnrollment 

SQL Query: 

SELECT DISTINCT personID, endYear ,calendarID, enrollmentID, serviceType, endAction, disability1, disability2, homeless, migrant, immigrant, grade, mvUnaccompaniedYouth, homeSchooled, districtID, structureID from enrollment;  

 

Staff Member 

Extract Instance Name:  IC_MUN_staffmember 

File Name:  staffmember 

SQL Query: 

SELECT DISTINCT personID, staffNumber, schoolID, gender, birthdate, firstName, suffix, lastName, middleName from staffmember;  

 

State Grade Level Definition 

Extract Instance Name:  IC_MUN_StateGradeLevelDefinition 

File Name:  StateGradeLevelDefinition 

SQL Query: 

SELECT DISTINCT code, name, description from StateGradeLevelDefinition;  

 

Student 

Extract Instance Name:  IC_MUN_student 

File Name:  student 

SQL Query: 

SELECT DISTINCT personID, identityID, studentNumber, gender, endYear, birthdate, birthCountry, firstName, lastName, middleName, suffix, legalFirstName, legalLastName, legalMiddleName, enrollmentID, calendarID, districtID, raceEthnicity, homePrimaryLanguage , hispanicEthnicity, schoolID, grade, startStatus, endStatus, startDate, endDate, structureID from Student WHERE calendarID IN (99, 100, 101, 102);  

 

Term 

Extract Instance Name:  IC_MUN_term 

File Name:  term 

SQL Query: 

SELECT DISTINCT termID, name, startDate, endDate, seq from term;  

 

Test 

Extract Instance Name:  IC_MUN_test 

File Name:  test 

SQL Query: 

SELECT DISTINCT testID, type, name, parentID from test; 

 

Test Details 

Extract Instance Name:  IC_MUN_TestDetails 

File Name:  TestDetails 

SQL Query: 

select * from v_TestDetail; 

 

Test Score 

Extract Instance Name:  IC_MUN_testScore 

File Name:  testScore 

SQL Query: 

SELECT DISTINCT * from TestScore;  

 

Trial 

Extract Instance Name:  IC_MUN_trial 

File Name:  trial 

SQL Query: 

SELECT DISTINCT 

    t1.trialID, 

    t1.calendarID, 

    t2.endYear, 

    t2.schoolID 

FROM 

    (SELECT DISTINCT 

        trialID, 

        calendarID 

     FROM 

        trial) AS t1 

INNER JOIN 

    (SELECT DISTINCT 

        calendarID, 

        endYear, 

        schoolID 

     FROM 

        calendar 

     WHERE 

        calendarID IN (99, 100, 101, 102)) AS t2 

ON 

    t1.calendarID = t2.calendarID; 

 

Grading Scores 

Extract Instance Name:  IC_MUN_GradingScores 

File Name:  GradingScores 

SQL Query: 

SELECT * FROM v_GradingScores WHERE YEAR(date) > 2023;