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.
April 2017 presentation:
.
.
Original Presentation:
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
West Des Moines Community Schools Technology 