Building a Parent Teacher Interview System using ECT – PART 1 PREPARATION

How to build a simple parent teacher interview system for sharepoint using only
external content types –

PART 1 – PREPARATION

CREATE YOUR TABLES ON YOUR SQL SERVER. You only need three tables!

  1. Teacher table
  2. Schedule table
  3. Interviews table

2. CREATE YOUR VIEWS ON YOUR SQL SERVER.

I created three views: All interviews (Booked and Not Booked), Interview time slots that
have been booked, and interview time slots that have not been booked. These two
views enable privacy of booking information for your parent teacher interview
system.

CREATE YOUR STORED PROCEDURES.

I have created the following stored procedures:

  1. Clear teacher table (I do this because staffing varies from year to year, and therefore we need to refresh the teaching roster for interviews).

 ALTER PROCEDURE [dbo].[pti_Clear_Teacher1]

AS

 — Insert statements for procedure here

Delete

from Teacher1

Fill the teaching table (I do this by pulling the current staff list from the main administration database). Unfortunately maintaining teacher availability is not as easy as just this. There will teaching staff who will be unavailable for interviews either for the whole schedule or for part of the schedule. For those teachers who are away for the whole schedule I manually delete these teachers from the teacher table before I create the Master Interview Booking Schedule. Teachers who are away for part of the schedule can have     those interviews deleted through the sharepoint list. The administrative staff can do this. We do not give teachers the ability to delete their own interviews.

ALTER PROCEDURE [dbo].[pti_fill_teacher1_pcschool]

AS

— Insert statements for procedure here

INSERT INTO [Teacher1]

(Teacher_Code,
TeacherNameForReport)

select teacher_code,
name_for_report

from [pcschool].[dbo].[TEACHER]

where status = ‘t’

order by teacher_code

  1. We don’t need to create any
    stored procedures for the schedule. The schedule will be directly
    maintained through a sharepoint list. Before we can run the next stored
    procedure we need ensure that the ‘schedule’ in sharepoint has been
    updated by the school administration staff.
  2. Create all interview time
    slots (Master Interview Schedule) by combing the teacher table with the
    schedule table using CROSS JOIN.

ALTER PROCEDURE [dbo].[PTI_Creating_InterviewData1]

— Add the parameters for the stored procedure here

AS

INSERT INTO InterviewData1

(Name_for_report, Order#, Time_Slot)

SELECT [Teacher1].[TeacherNameForReport],[Schedule1].[Order#], [Schedule1].[Time_Slot]

FROM Schedule1 CROSS JOIN

TEACHER1

5. I automate all of the above by creating a MASTER STORED PROCEDURE. Each year I just run the one stored procedure that runs all of the other stored procedures combined.

— BEFORE THIS STORED PROCEDURE CAN BE RUN, YOU MUST ENSURE THAT THE SCHEDULES ARE CORRECT!!

ALTER PROCEDURE [dbo].[PTI_MASTER_DATA_PREPARE1_LIVE]

AS

exec pti_Clear_Teacher1

exec pti_Clear_Teacher2

exec pti_fill_teacher1_pcschool

exec pti_fill_teacher2_pcschool

exec PTI_Creating_InterviewData1

exec PTI_Creating_InterviewData2

YOU HAVE NOW BUILT ALL OF SQL INFRASTRUCTURE THAT YOU NEED.

WELL DONE..

Advertisements

About whellmuth

Working with the education software industry to build software with standard arhitectures that allows schools to have extensible and well integrated technologies. Member of Software QLD. Member of Microsoft advisory board on cloud computing. My doctorate specialises in Software Architecture in the Education environment. My Master Research specialised in IT change management.
This entry was posted in Microsoft SharePoint. Bookmark the permalink.

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