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.

April 2017 presentation:

My favorite sql scripts from Brian Abeling

 

.

.

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

Tips and advice for districts moving to Infinite Campus

There’s a wave of school districts that are looking to move to Infinite Campus, as we’ve seen an increase in calls and emails with questions seeking advice on the topic.

Later this week, I’ll be presenting “Tips & Advice for district’s moving to Infinite Campus” at the ITEC IT Conference.  In addition, I’ll also be presenting “Automating Tasks in Infinite Campus”, which looks at ways to automate processes within Campus.

Here’s a prelude to the “Tips & Advice for district’s moving to Infinite Campus”

1. Dump your demographic data

Yes, I’m serious, don’t import your demographic data. It’s highly unlikely that your current demographic data (families, addresses, phone numbers) is in the exact format needed, nor is it highly accurate.  This is the perfect time to dump your demographics and start over with a clean slate.

If you think this is asking too much… consider that Clark County, Nevada (which includes Las Vegas) dumped their demographic data to get a clean start.  Considering they have hundreds of thousands of students in Clark County – there’s no reason you should be able to do this as well.

2. Grab as much transcript data as you can.

Compared to demographic data, I feel exactly the opposite on transcript data.  We imported only the minimum when we converted to Campus – and then spent years working on getting the older years converted.  Get as much of the transcript data as possible and even consider paying to get it all.

3. Just use the product…

Don’t start envisioning how you’ll customize report cards, transcripts, and progress reports.  Instead, just use the built in product first.  Customizing these parts of the system can be done, but can be expensive and time consuming.  Our advice – just start by using the built in reports and features first, before worrying about customizations.

4. Learn to manage the updates.

As with most web based SIS systems, there are updates available every few weeks. This doesn’t mean that you should take every update that is available.  In fact, I commonly hear from other districts who claim, “Every update seems to add new features and break one or two existing features”.

Our model has been to only take updates that are needed.  For example, we take an update every summer, any update that is required for state reporting, and the a few sporadic updates if a specific feature set is needed.  Even though incremental updates might add a few new features, you really have to ask yourself if each and every update is really worth it.

5. Keep your own backups

Infinite Campus keeps three days of backups on the database server.  In addition, they copy these backups to their corporate office in Minnesota each night.  However, I feel strongly that you need keep backups longer than 3 days.   In our district, our HR and finance systems are ASP’d, meaning that they are stored off site – which means that our Infinite Campus data is the most important data we have on site.

Since the system runs off Microsoft SQL, the backup is actually one single file – so my recommendation is to connect the system to a shared network drive and script the backup file to another location and store them long term.  We’ve found several situations when something changed or was accidently removed – and it took longer than three days before the issue was reported.

6. Learn to play in the Sandbox

The sandbox is a “copy” of your database system, that also has a web interface that you can log into.  It’s important that you learn how to refresh the sandbox with your current information, as well as how to restore a backup file to the sandbox.

We use the sandbox all the time.  We’ll use it to build mock-ups of custom tabs/fields and to test out automation scripts.  We’ll also use the sandbox to restore backup files for scenarios where staff thinks something is wrong, and they simply want a way to compare their current data with a previous days data set – and the sandbox is the perfect place for this.

7. Don’t turn all the features on right away…

Just because there are a bazillion features in Campus doesn’t mean you should turn them on the first day.  Instead, just turn on the minimal features needed. Over time, you can turn on additional features and you won’t overwhelm anyone and you’ll give the appearance that the system is churning out new features all the time.

8. Spend a lot of time reviewing your security groups.

Infinite Campus will warn you: spend time reviewing your security group structure. And they are serious… do it.  We followed the recommendation of setting up both calendar based groups and user based groups.  We originally tried to create as few groups as possible.  However, over time, we’ve had to create more groups to be able to isolate individuals down to certain rights.  Changing groups and security permissions can be messy, so spend your time on this task up front.

9. The most important field in Campus is…

Yes, I’m being serious, there is one field that I feel is more important that all the others, and it’s in the grade book and is called…  Assigned Date.

For each assignment, there is an Assigned Date and a Due Date.  If teachers are putting the same date in both fields, this means the grade book is a “gotcha”, its only used to report what’s already happened.

On the other hand, if the Assigned Date is prior to the Due Date, the grade book now becomes a communication tool with students and parents.  In fact, the built-in calendar within Campus functions off the Assigned Date field.  Without this information, the calendar is worthless.  Be sure to clarify your district’s expectations related to this field prior to your initial roll out and training.