Automating Tasks in Infinite Campus

I recently presented “Automating Tasks in Infinite Camps at the ITEC IT Conference to share with other districts how the Task Scheduler tool within Campus can be used to automate rules and processes such as student flags.  The following post is to share the slides and the examples with other districts.

My favorite techie feature inside Infinite Campus is, without a doubt, the Task Scheduler. Although it’s buried in System Admin>Preferences section, it’s easily the most useful tool for automating tasks within Infinite Campus.  Basically, the task scheduler lets you insert SQL programming code into the system and set it to run on a schedule (daily, weekly, or manual). In West Des Moines Schools, we use the task scheduler to automate the creation of several student flags, as well as to mark/populate certain data fields based on other fields.

A few notes of caution:

1) I am not a SQL programmer, I did not generate the programming for these examples, instead I’m the idea guy…  I dream up ways we can use the task scheduler to automate various features within Campus. Credit for the scripting goes to our Campus Guru, Susan Tiemens.

2) Using these scripts can be dangerous to your system – once they are run, there is no ‘undo’, so you must be sure that the script is modified for your system needs.

Here’s some of the specific examples and scripts….

Delete Inbox Messages……..

By default, all messages in the inbox stay forever.  Since these messages are already sent to parents by email, we encourage them to keep them in their email if they choose to archive the messages.  We use this script in the task scheduler to automatically remove all messages older than 5 days (or adjust the 5 to the number of days you’d like)

delete pm
from processMessage pm
where pm.postedTimestamp<= (select getDate() -5 days)

Turn off class rank…..

Our district doesn’t use class rank, so we use a task scheduler script to automatically turn off class rank and its runs daily to make sure that class rank is always off for all student records.

UPDATE Enrollment SET classRankExclude = 1 WHERE classRankExclude = 0

Force Password Change

We use this script to ensure that certain, common passwords are never used to help ensure security. When run, if this scripts finds one of these passwords, it automatically marks force password change for that use.   In the area where ‘happy123′ is, you would change this area to reflect the specific passwords that you would like not to be used.

update UserAccount
set forcechangepassword = 1
WHERE UserAccount.password IN (‘happy123′,’Happy123′,’HAPPY123′,’happyface123′,’Happyface123′,’HAPPYFACE123′,’HappyFace123′,’dietcoke2′,’dietcoke3′,’dietcoke4′,’12345′,’123456′)

Delete all social security information

Although there is a field for social security numbers within Campus, our districts’s policy is that we NEVER ask for or store social security numbers.  To help ensure that this happens, we have a script that runs nightly and deletes anything that is stored in these fields – helping to ensure that nothing is recorded.

UPDATE student
SET student.SSN=Null
WHERE student.SSN is not null

UPDATE staffmember
SET staffmember.SSN=Null
WHERE staffmember.SSN is not null

Mark Guardians for portal and messenger access

To ensure that all guardians in the system automatically receive port and messenger access and are never accidently missed, we use the following nightly script. (Keep in mind, parents can still opt out of getting message with their own personal preferences – but this ensure that we aren’t causing the issue in their preferences)

UPDATE RelatedPair
SET messenger= 1, portal=1
From RelatedPair
WHERE relatedpair.guardian=1 and relatedpair.enddate is null

Mark ELP flag

This style of script is used for multiple scenarios for us, all involving marking or flaging students who specific designations or needs.  In this example, we use the state reporting field for talented and gifted to produce a special visual flag for staff.  In this example, the referenece to programID=81 is the reference to our ELP flag, which is number 81 in our system.  This would need to be altered to system ID for the ELP flag that your system has.

/*Remove All Gifted/Talented Flags*/
DELETE from ProgramParticipation
from ProgramParticipation
WHERE ProgramParticipation.programID=81

/*Insert ELP (Gifted/Talented) Program Flag on Students who are marked for Gifted/Talented*/
INSERT INTO ProgramParticipation (programID, personID, districtID, startDate)
SELECT p.programID, e.personID, e.districtID, CONVERT(SMALLDATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))) startDate
FROM Program p
INNER JOIN SchoolYear sy ON sy.active = 1
INNER JOIN Enrollment e ON e.endYear = sy.endYear
INNER JOIN Student s on s.personID=e.personID
LEFT OUTER JOIN ProgramParticipation pp ON pp.personID = e.personID
AND pp.programID = p.programID AND (pp.endDate IS NULL
OR pp.endDate > FLOOR(CONVERT(FLOAT, GETDATE())))

WHERE pp.participationID IS NULL and sy.active = 1
AND s.endDate Is Null and s.activeyear=1 and s.servicetype=’P’
AND p.programid=81 and e.giftedTalented=’1′
GROUP BY p.programID, e.personID, e.districtID

Messenger Teacher Box

We automatically set the preference that allows Teachers to use Messenger to send notifications to parents.

/* This turns on the Teacher box for a Guardian with Messenger Access in the Messenger Preferences on their Census/Demographics Tab*/

UPDATE Contact
SET contact.emailmessenger = 8
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 0 or contact.emailmessenger is null

UPDATE Contact
SET contact.emailmessenger = 9
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 1

UPDATE Contact
SET contact.emailmessenger = 10
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 2

UPDATE Contact
SET contact.emailmessenger = 11
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 3

UPDATE Contact
SET contact.emailmessenger = 12
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 4

UPDATE Contact
SET contact.emailmessenger = 13
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 5

UPDATE Contact
SET contact.emailmessenger = 14
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 6

UPDATE Contact
SET contact.emailmessenger = 15
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 7

UPDATE Contact
SET contact.emailmessenger = 24
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 16

UPDATE Contact
SET contact.emailmessenger = 25
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 17

UPDATE Contact
SET contact.emailmessenger = 26
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 18

UPDATE Contact
SET contact.emailmessenger = 27
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 19

UPDATE Contact
SET contact.emailmessenger = 28
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 20

UPDATE Contact
SET contact.emailmessenger = 29
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 21

UPDATE Contact
SET contact.emailmessenger = 30
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 22

UPDATE Contact
SET contact.emailmessenger = 31
From Contact
INNER JOIN v_CensusContactSummary ccs ON contact.personID=ccs.contactPersonID
WHERE ccs.guardian=1 and ccs.messenger=1 and contact.emailmessenger = 23

 

Mark IEP Flags

/*Clear ALL SPED Program Flags out*/
DELETE from ProgramParticipation
from ProgramParticipation
WHERE ProgramParticipation.programID=10

/*Insert SPED Program Flag on Studentsmarked for SPED*/
INSERT INTO ProgramParticipation (programID, personID, districtID, startDate)

SELECT p.programID, e.personID, e.districtID,
CONVERT(SMALLDATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))) startDate
FROM Program p
INNER JOIN SchoolYear sy ON sy.active = 1
INNER JOIN Enrollment E on sy.endYear=e.endyear and E.SpecialEdstatus=3
LEFT OUTER JOIN ProgramParticipation pp
ON pp.personID = e.personID AND pp.programID = p.programID
AND (pp.endDate IS NULL OR pp.endDate > FLOOR(CONVERT(FLOAT, GETDATE())))
WHERE pp.PersonID IS NULL and e.enddate is Null and p.programID=10
Order by e.personID, p.programID

4 thoughts on “Automating Tasks in Infinite Campus

  1. Brian Abeling Post author

    Another script submitted by another school district…

    Update NGA Cohort End Year in the Graduation Tab…

    update g
    set cohortYearNGA = CASE
    WHEN grade9Date like ‘%2012%’ THEN 2016
    WHEN grade9Date like ‘%2011%’ THEN 2015
    WHEN grade9Date like ‘%2010%’ THEN 2014
    WHEN grade9Date like ‘%2009%’ THEN 2013
    WHEN grade9Date like ‘%2008%’ THEN 2012
    WHEN grade9Date like ‘%2007%’ THEN 2011
    WHEN grade9Date like ‘%2006%’ THEN 2010
    WHEN grade9Date like ‘%2005%’ THEN 2009
    WHEN grade9Date like ‘%2004%’ THEN 2008
    ELSE NULL
    END
    from graduation g
    where g.cohortYearNGA is null

    Reply
  2. Brian Abeling Post author

    Another submitted script –> Enters the preferred language of English which doesn’t fill in if there is no student/contact information entered.

    /*Entering in the preferred language of English (defaults)
    which doesn’t fill in if there is no student/contact information entered */

    INSERT into contact (personID, communicationLanguage)
    SELECT personID, ‘en_US’
    –select *
    from person p
    where not exists (select * from contact c where c.personID = p.personID)

    Reply
  3. Josh Griffis

    Great idea for a presentation! We use task scheduler a lot! Here are a few more examples:

    Remove the student from a sped teacher’s caseload (add an end date on the team member tab) if the student was dismissed from Special Ed.

    update teammember
    set enddate=e.spedexitdate
    from enrollment e
    join teammember tm on tm.personid=e.personid
    join schoolyear sy with(nolock) on sy.endyear=e.endyear and sy.active=1
    where e.spedexitdate is not null
    and (tm.enddate is null or tm.enddate>e.spedexitdate)
    and tm.module=’specialed’
    and not tm.title LIKE ‘%(GUARDIAN)%’
    and not tm.title LIKE ‘%(STUDENT)%’
    and staffpersonid is not null

    /***************************
    add physical therapists to child’s team if physical therapy is on the IEP
    *************************/
    insert into teammember (personid,staffpersonid,districtid,lastname,firstname,title,startdate,[role],module)
    select s.personid, 69884,146,’Doe’,’Jane’,'(SPEDSTAFF)’,getDate(),’Service Provider’,’specialed’
    from student s
    join enrollment e on s.personid=e.personid and s.enrollmentid=e.enrollmentid
    join school sch on s.schoolid=sch.schoolid
    join [plan] p on p.personid=s.personid
    join planserviceprovided psp on p.planid=psp.planid
    join schoolyear sy on sy.endyear=s.endyear and sy.active=1
    where s.endstatus IS NULL
    –and e.specialedstatus=’Y’
    and psp.enddate>getdate()
    and (psp.servicename like ‘F: Physical%’ or psp.positionname like ‘%physical therapist%’)
    and s.personid NOT IN (select personid from teammember where staffpersonid=69884and enddate is null)

    /********************************
    set PO Boxes as mailing addresses
    ********************************/
    update hl
    set mailing = 1
    from householdLocation hl join address a ON a.addressID = hl.addressID
    where a.postOfficeBox = 1

    /**************************************
    Show banner on sandbox
    ***************************************/

    update spalding_sandbox.dbo.useraccount
    set hidebanner=0

    /***************************
    hide banner on live db
    **************************/
    update spalding.dbo.useraccount
    set hidebanner=1

    /*****************************
    update banner on sandbox
    *******************************/

    DELETE FROM spalding_sandbox.dbo.UserNotice
    WHERE (banner = 1)

    INSERT INTO spalding_sandbox.dbo.UserNotice
    (text, districtID, portal, campus, portalLogin,banner)
    VALUES (‘Sandbox‘, 1, 0, 0, 0, 1)

    /*******************************
    update teacher preferences
    *******************************/
    insert into teacherpreference (sectionid,hidedropped,cannedcomments,showids,defaultgradingtaskid)
    select sec.sectionid,1,1,1,2
    from section sec
    join course c on c.courseid=sec.courseid
    join calendar ca on ca.calendarid=c.calendarid
    join schoolyear sy on sy.endyear=ca.endyear and sy.active=1
    join trial t on sec.trialid=t.trialid and t.active=1
    where not exists (select * from teacherPreference tp where tp.sectionID = sec.sectionID)

    update teacherpreference
    set defaultgradingtaskid=2
    –select tp.*
    from teacherpreference tp
    join section sec on sec.sectionid=tp.sectionid
    join course c on c.courseid=sec.courseid
    join calendar ca on ca.calendarid=c.calendarid
    join schoolyear sy on sy.endyear=ca.endyear and sy.active=1
    where isnull(tp.defaultgradingtaskid,0)=2

    /*****************************
    elementary schools – usePureStandards
    ***************************/

    insert teacherpreference (sectionid,usepurestandards)
    select sectionid,1
    from [section] s
    join course c on c.courseid = s.courseid
    join calendar cl on cl.calendarid = c.calendarid
    join school sc on sc.schoolid = cl.schoolid
    where sc.number IN (1050,1550,1750,0192,2050,0199,1052,0195,2552,4052,0103)
    and not exists(select * from teacherPreference tp where tp.sectionID = s.sectionID)

    update teacherpreference
    set usePureStandards=1
    from teacherpreference tp
    join [section] s on tp.sectionid=s.sectionid
    join course c on c.courseid = s.courseid
    join calendar cl on cl.calendarid = c.calendarid
    join school sc on sc.schoolid = cl.schoolid
    join schoolyear sy on sy.endyear=cl.endyear and sy.active=1
    where isnull(usePureStandards,0)=0
    and sc.number IN (1050,1550,1750,0192,2050,0199,1052,0195,2552,4052,0103)

    Reply
  4. Pingback: Programming Automate Daily Task

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s