Revision History

Overview 

The timetable automatic import extracts information directly from the ‘Timetabler’ software. Currently it supports importing the timetable, yard duties and teacher / room replacements.
Instead of exporting files out of Timetabler and into Synergetic this method allows you to select Timetabler database files to import directly. There are 2 files that are used in this process.
Timetabler database file:

This contains the timetable itself along with the students in the class and the teachers against the class. This also contains yard duties. 

Daily organiser database file:

This contains the staff extras / room replacements. 

Setup Requirements:

• The automatic import first needs to be installed on the SQL server. For Timetabler Version 8 this utilises the Microsoft office Access database engine to connect to the Timetabler databases. There are specific SQL jobs that also need to be created in order for the integration to function.
• The SQL Server service account needs to have access to the folder where the database files are stored. You can view which account this is by opening SQL Configuration Manager. 
• Synergetic needs to be configured to point to the Timetabler database location(s) with settings for when& how to import.

Synergetic Timetabler Configuration

Synergetic needs to be configured to import the files. This is done through Students -> Timetable Maintenance.

Timetable Config Tab 

luTimetableOperationMode

This looking table controls various options around the Automatic Import of the Timetable

Transfer Term Configuration

When you create the new term you will need to copy across the timetable configuration for the previous term.

This is done through Students → Timetable Maintenance in the Timetable Config tab. You will need to select the term you want to copy from first.

1. Select Students > Timetable Maintenance tab > Timetable Config sub-tab.
2. Select an existing timetable in the grid area.
3. Click .
   The Apply Schedule window is displayed.
   
4. Enter the Timetable code for the new timetable.
5. Click .
   The existing timetable schedule settings are copied to the new timetable in the grid area. 

Once the term has been copied you will need to ensure update the ImportStartDateTime & ImportEndDateTime for the new term are set correctly so that they are ready to import for the new term.

You'll also need to check that the configuration options are still valid.

Import Tables

The automatic import processed the Timetabler files into work tables prior to loading them into the live tables. When the SQL jobs are run first the data is populated into the below worktables. It is then transferred to the Following Synergetic Areas:

• Timetable 
• Student Classes
• Class teachers 
• Timetable Substitutes

These are loaded based on these tables and the configuration options set in the timetable config. When importing the Timetable is cleared based on the following fields:

• FileType
• FileYear
• FileSemester
• ClassCampus
• TimetableGroup.

Only Classes where the Never Clear on Timetable Import & Timetable Keep Assessable are NOT set. 

wkxiStudentClasses

Staging table for Student Class data before importing them into the main Synergetic tables

wkxiTimetable

Staging table for Timetable data before importing them into the main Synergetic tables

wkxiTimetableSubstitutes

Staging table for student Substitute Classes before importing them into the main Synergetic tables

Configuration Keys

The following are any Configuration Keys that affect the Automatic Timetabler Import

Action Centre Messages

There are several Action Message Types that have been created to assist with the import process:

TIM_ImportExceptions_I

A report of Imported / Excluded records from the import. This will display the following:

• Number of records imported
• Any staff who could not be mapped using the settings provided
• Any Students who could not be mapped using the settings provided
• Any Timetable records that could not be imported
• Any Student Class records that could not be imported
• Any Substitute records that could not be imported
• The Import Settings used for the import

TIM_ImportFailure_A

If there is an error message when importing the Timetable this action message will be created. This will display the error message along with the Settings used to import the Timetable. See below for example":

SQL Server Service Account 

The Timetabler Automatic Import can import database files from directories either on the SQL server directly or from a network share. If you are wanting to configure the import for use with a network share the SQL Server service account must be setup with a domain account user.

In either case and in order to function the SQL server service account need read/write permission to the folder where the Timetabler database files are located. 

In the example above the service account is CDA_MAIN\SQL_WS. This account would need to be configured with the following permissions to the folders where the Timetabler files are located. 

SQL Jobs

There are 4 jobs that can be scheduled in your SQL server management studio. (This can be configured by our professional support team- please raise a case for a quote should this be required)
These are scheduled as per your requested times in the initial setup. They can be changed to a different schedule if required.

These can also be run manually through SQL Server management studio. You can right click on any of these jobs and select Start Job at Step. 

Troubleshooting

• No teachers against the classes

  • Check the StaffLinkField against Timetable Configuration for the import. 

    Check Work table for any staff that have a StaffID of 0

    SELECT * FROM wkxiTimetable WHERE StaffCode <> '' AND StaffID = 0 AND TimetableCode = '2022T1'

• No Students listed against classes

  • Check the StudentLinkField against Timetable Configuration for the import

    Check Work table for students with an ID of 0

    SELECT * FROM wkxiStudentClasses WHERE StudentCode <> '' AND StudentID = 0 AND TimetableCode = '2022T1'

• Particular staff members do not have classes
  • Error rendering macro 'code': Invalid value specified for parameter 'firstline'

    SELECT * FROM wkxiTimetable WHERE StaffCode = 'ABC' AND TimetableCode = '2022T1'

  • Check Synergetic to make sure the StaffCode exists

    Check Synergetic to make sure the staff code exists

    --SchoolStaffCode
    SELECT * FROM Staff WHERE SchoolStaffCode = 'ABC'  AND TimetableCode = '2022T1' --ID
    SELECT * FROM Staff WHERE CONVERT(VARCHAR(30),ID) = 'ABC'  AND TimetableCode = '2022T1'

  • Check Synergetic to ensure the Staff Code matches the staff Code coming from Timetabler

    Check that the Staff Code is not duplicated with another staff member

    SELECT COUNT(*) FROM Staff WHERE SchoolStaffCode = 'ABC'  AND TimetableCode = '2022T1'

• Particular Students do not have classes
  

  • Check Synergetic to make Sure that the Code exists for the student:

    Check Synergetic to make sure the staff code exists Student Code Exists

    --AlternateCode
    SELECT * FROM Students WHERE AlternateCode = '12345'
    --ID
    SELECT * FROM Students WHERE Convert(VARCHAR(30),ID) = '12345'
    --TimetableCode
    SELECT * FROM Students WHERE TimetableCode = '12345'
    --PreviousSystemCode
    SELECT * FROM Students WHERE N16StuCode = '12345'

  • Check Work Tables for the StudentCode

    SELECT * FROM wkxiStudentClasses WHERE StudentCode = '12345' 

• Nothing is imported

  • Check that the date is within the start date and stop date. 

  • Check the enabled flag is set against each of the imports. 

  • Check that the SQL job is enabled and scheduled

  • Check which Schedules will run

          SELECT
            tc.TimetableCode,
            MAX(tcsc.TimetableType),
            tc.FileYear,
            tc.FileSemester,
            tc.FileType,
            lut.SetExistingNonAssessableFlag,
            lut.SetNewNonAssessableFlag,
            lut.SetStoppedFlag,
            lut.AllowCoreClassClearFlag,
            lut.ClearStaffFlag,
            MAX(tcsc.StudentLinkField),
            MAX(tcsc.StaffLinkField),
            MAX(CASE WHEN tcsc.TimetableType IN ('TIMETABLER','FIRSTCLASS','TIMETABLERYD','CUSTOM','FIRSTCLASSEXT') THEN 1 ELSE 0 END) AS ImportTimetableFlag,--SELECT *
            MAX(CASE WHEN tcsc.TimetableType IN ('TIMETABLEREXT','CUSTOM','FIRSTCLASSEXT') THEN 1 ELSE 0 END) AS ImportExtrasFlag,
            MIN(tcsc.YearLevelFrom),
            MAX(tcsc.YearLevelTo),
            MAX(tcsc.ImportStartDateTime),
            MAX(tcsc.ImportEndDateTime),
            MAX(tcsc.TimetableGroup),
            MAX(CONVERT(INT,lut.CreateLuRoomFlag)),
            MAX(CONVERT(INT,lut.ExplodeObjectsFlag)),
            MAX(CONVERT(INT,tcsc.ImportOrderSeq)),
            MAX(CONVERT(INT,lut.UpdateClassDescriptionFlag)),
            MAX(CONVERT(INT,lut.UpdateFormFlag)),
            MAX(CONVERT(INT,lut.RemoveAsterixFlag)),
            MAX(CONVERT(INT,lut.UpdateTutorFlag))
            --SELECT *
          
          FROM dbo.TimetableSchedule tcsc 
            INNER JOIN dbo.TimetableConfig tc ON (tc.TimetableConfigSeq = tcsc.TimetableConfigSeq)
            LEFT JOIN dbo.luTimetableOperationMode lut ON (lut.Code = tcsc.TimetableOperationModeCode)
          WHERE tcsc.TimetableType IN ('TIMETABLER','FIRSTCLASS','TIMETABLEREXT','TIMETABLERYD','CUSTOM','FIRSTCLASSEXT')
            AND dbo.fnDateOnly(GETDATE()) >= tcsc.ImportStartDateTime
            AND dbo.fnDateOnly(GETDATE()) <= tcsc.ImportEndDateTime
            AND tcsc.EnabledFlag = 1
          GROUP BY 
          tc.TimetableCode,
            tc.FileYear,
            tc.FileSemester,
            tc.FileType,
            lut.SetExistingNonAssessableFlag,
            lut.SetNewNonAssessableFlag,
            lut.SetStoppedFlag,
            lut.AllowCoreClassClearFlag,
            lut.ClearStaffFlag
          ORDER BY MAX(CASE WHEN tcsc.TimetableType IN ('TIMETABLER','FIRSTCLASS','CUSTOM','FIRSTCLASSEXT') THEN 1 ELSE 0 END)  DESC,
            MAX(CONVERT(INT,tcsc.ImportOrderSeq))

• Particular Classes are not imported
  • Check the Never Clear on Timetable Import Flag against the class.
  • Check the Timetable Keep Assessable Flag against the class.
• Period Numbers are out of Sync when importing Extras
  • In the Daily Organiser Period numbers are stored by a code instead of a Number. This can cause issues if the period numbers are different to the codes stored in Timetabler. Please contact Support for a fix.
    Apply the script - I:\SCRIPTS\DBA\Timetable\uspuwkTimetable - Update Period Numbers.sql (I: = \\zeus\Delphi\)
• You can also refer to the Version 9 XML File using the following script to check the contents of the Timetabler XML:
  • I:\SCRIPTS\DBA\Timetable\99_testing.sql