Time Tracker
Employee Scheduling Software
Scheduling Software
Version 5.1
(c) Copyright 2015
Asgard Systems Incorporated
Revised: September 19, 2015
This guide is copyrighted and all rights are reserved. This document may not, in whole or part, be copied, photocopied, reproduced, translated or reduced to any electronic medium or machine-readable form without the prior written consent of Asgard Systems. All names, companies and data used in examples and illustrations in this guide are fictitious unless otherwise noted.
Copyright
2015Asgard Systems Incorporated
Asgard Systems Incorporated
Phone: (905) 527-4269
Time Tracker
TABLE OF CONTENTS
INTRODUCTION
Overview.
TABLES AND DATA STRUCTURES
Attrempl
Availl
Avails
Emplattr
Employee
Emptype
Generatepk
Lleutime
Leutype
Lieu
Location
Mastbook
Mastrot
Mastrth
Minstf
Othtime
Othtype
Refusals
Reminders
Schedbook
Schedrot
Schedrth
Shift
Sictime
Sictype
Stat
vactime
vactype
INTRODUCTION
The intention of this document is to provide an overview of the databases used within Time Tracker. A degree of familiarity with Time Tracker is expected.
Typically, data files are stored in sub-folders of where Time Tracker is installed. To confirm where specific databases are stored, use the “Organizations” component in Time Tracker. It is accessed in Time Tracker by; selecting the menu option “Organizations” from the “Admin” menu pad, on the main menu.
Overview.
The Time Tracker database is comprised of numerous tables and files of inter-related components. Fundamental to understanding how information or tables are related; is the concept of Primary Keys and Foreign Keys.
Primary Keys are fields that are defined within specific tables. Each record in such a table has it’s own unique key. They are generated by Time Tracker. These fields have names with a suffix of “pk”. For example the employee table “employee.dbf” has a field called “emp_pk”. Each employee record has it’s own unique value for the field “emp_pk”.
Foreign Keys are fields in tables that store Primary Key information from other tables. These fields have names with a suffix of “fk”. For example, the Live Schedule Booking table (“schedbook.dbf”) has field called sbk_empfk. The field sbk_empfk will have the recorded primary key information of an employee. In other words it will have recorded; the contents of the “emp_pk” field from the employee table “employee.dbf” from one specific record. This enables each individual booking record to identify the employee that has been booked.
It should be noted, that each Foreign key’s suffix of “fk” is preceded by 3 characters. In the previous example, the Foreign key sbk_empfk had the characters “emp” preceding the suffix “fk”. These 3 characters help to identify which table that the Foreign Key is actually referring to. The 3 characters in this case refer to an abbreviation of the employee table name. Note that, the prefix of all fields in the employee table begin with “emp”. This same abbreviation and prefixing is applied through out the database.
TABLES AND DATA STRUCTURES
Attrempl
Attribute Definitions
# Field Name Type Len Dec Null
1 ate_pk Character 5 0 N
Comments: Primary Key
2 ate_surck Character 5 0 N
Caption: Code
Comments: Attribute Code
Fp26field: code
3 ate_descck Character 30 0 N
Caption: Description
Comments: Attribute Description
Fp26field: descr
Availl
Employee Location Availabilty
# Field Name Type Len Dec Null
1 avl_empfk Character 5 0 N
Comments: Employee key - links to employee.dbf
2 avl_locfk Character 5 0 N
Comments: Location key - links to location.dbf
3 avl_sun Logical 1 0 N
Comments: Location Availability, for day of the week.
4 avl_mon Logical 1 0 N
Comments: Location Availability, for day of the week.
5 avl_tue Logical 1 0 N
Comments: Location Availability, for day of the week.
6 avl_wed Logical 1 0 N
Comments: Location Availability, for day of the week.
7 avl_thu Logical 1 0 N
Comments: Location Availability, for day of the week.
8 avl_fri Logical 1 0 N
Comments: Location Availability, for day of the week.
9 avl_sat Logical 1 0 N
Comments: Location Availability, for day of the week.
10 avl_pk Character 5 0 N
Comments: Primary Key
Default: PADL(x3genpk("AVAILL","TT"),LENC(avl_pk),"0")
Avails
Employee Shift Availabilty
# Field Name Type Len Dec Null
1 avs_empfk Character 5 0 N
Comments: Employee key - links to employee.dbf
2 avs_shffk Character 5 0 N
Comments: Location key - links to shift.dbf
3 avs_sun Logical 1 0 N
Comments: Shift Availability, for day of the week.
4 avs_mon Logical 1 0 N
Comments: Shift Availability, for day of the week.
5 avs_tue Logical 1 0 N
Comments: Shift Availability, for day of the week.
6 avs_wed Logical 1 0 N
Comments: Shift Availability, for day of the week.
7 avs_thu Logical 1 0 N
Comments: Shift Availability, for day of the week.
8 avs_fri Logical 1 0 N
Comments: Shift Availability, for day of the week.
9 avs_sat Logical 1 0 N
Comments: Shift Availability, for day of the week.
10 avs_pk Character 5 0 N
Comments: Primary Key
Default: PADL(x3genpk("AVAILS","TT"),LENC(avs_pk),"0")
Emplattr
Employee Attributes
# Field Name Type Len Dec Null
1 eat_empfk Character 5 0 N
Comments: Employee key - links to employee.dbf
Fp26fK: id_num,employee.id_num
2 eat_atefk Character 5 0 N
Comments: attribute key - links to attrempl.dbf
Fp26fK: attr,attr.code
Employee
Employee Definition
# Field Name Type Len Dec Null
1 emp_pk Character 5 0 N
Comments: Primary Key
2 emp_first Character 15 0 N
Caption: First Name
Comments: First Name
Fp26field: first
3 emp_last Character 20 0 N
Caption: Last Name
Comments: last name
Fp26field: last
4 emp_number Character 10 0 N
Caption: Employee#
Comments: Employee Number
Fp26field: emplno
5 emp_add1 Character 30 0 N
Comments: Address
Fp26field: addr1
6 emp_add2 Character 30 0 N
Comments: address
7 emp_city Character 15 0 N
Comments: city
Fp26field: city
8 emp_state Character 3 0 N
Comments: province or state
Fp26field: prov
9 emp_zipc Character 15 0 N
Comments: Postal Code or Zip Code
Fp26field: postal
10 emp_dob Date 8 0 N
Comments: Date of Birth
Fp26field: dob
11 emp_phone1 Character 25 0 N
Comments: Phone1
Fp26field: TRANSFORM(phone1,"@R! (999) 999-9999")
12 emp_emrgcont Character 20 0 N
Comments: Emergency Contact
Fp26field: emerg_cont
13 emp_emergphone Character 25 0 N
Comments: Emergency Phone 1
Fp26field: TRANSFORM(phone2,"@R! (999) 999-9999")
14 emp_phone2 Character 25 0 N
Comments: Phone 2
15 emp_email Character 40 0 N
Caption: Emp Email
Comments: Email
16 emp_socins Character 15 0 N
Comments: OHIP or Social Insurance #'s
Fp26field: soc_ins
17 emp_hcard Character 15 0 N
Comments: Health Card
Fp26field: hlth_crd
18 emp_active Logical 1 0 N
Comments: Active
Fp26field: active
19 emp_payr Numeric 10 4 N
Comments: Pay Rate
Fp26field: rateofpay
20 emp_basehrs Numeric 10 2 N
Comments: Base Hours
Fp26field: basehrs
21 emp_dbfhrs Numeric 10 2 N
Comments: Data base hours
Fp26field: dbfhrs
22 emp_etpfk Character 5 0 N
Comments: employee type key - Links emptype.dbf
Fp26fK: rank,empltype.code
23 emp_maxday Numeric 2 0 N
Comments: Maximum consecutive days
Fp26field: maxdays
24 emp_dayoff Numeric 2 0 N
Comments: # of days off - not in use
Fp26field: daysoff
25 emp_overt Logical 1 0 N
Comments: Overtime - not in use
Fp26field: overtime
26 emp_vdays Numeric 10 2 N
Comments: Vacation Days - not in use
Fp26field: vdays
27 emp_vhrs Numeric 7 2 N
Comments: Vacation Hours
Fp26field: vhrs
28 emp_sichrs Numeric 7 2 N
Comments: Sick Hours
Fp26field: sickhrs
29 emp_start Date 8 0 N
Comments: Start of employment
Fp26field: start
30 emp_end Date 8 0 N
Comments: end of employment
Fp26field: end
31 emp_memo Memo 4 0 N
Comments: General Notes
Fp26field: memo
32 emp_sun Logical 1 0 N
Comments: Shift Availability, for day of the week. When checked availabilty is for all days.
sun
Fp26field:
33 emp_mon Logical 1 0 N
Comments: Shift Availability, for day of the week. When checked availabilty is for all days.
mon
Fp26field:
34 emp_tue Logical 1 0 N
Comments: Shift Availability, for day of the week. When checked availabilty is for all days.
tue
Fp26field:
35 emp_wed Logical 1 0 N
Comments: Shift Availability, for day of the week. When checked availabilty is for all days.
wed
Fp26field:
36 emp_thu Logical 1 0 N
Comments: Shift Availability, for day of the week. When checked availabilty is for all days.
thu
Fp26field:
37 emp_fri Logical 1 0 N
Comments: Shift Availability, for day of the week. When checked availabilty is for all days.
fri
Fp26field:
38 emp_sat Logical 1 0 N
Comments: Shift Availability, for day of the week. When checked availabilty is for all days.
sat
Fp26field:
39 emp_lsun Logical 1 0 N
Comments: Location Availability, for day of the week. When checked availabilty is for all days.
.T.
Default:
40 emp_lmon Logical 1 0 N
Comments: Location Availability, for day of the week. When checked availabilty is for all days.
.T.
Default:
41 emp_ltue Logical 1 0 N
Comments: Location Availability, for day of the week. When checked availabilty is for all days.
.T.
Default:
42 emp_lwed Logical 1 0 N
Comments: Location Availability, for day of the week. When checked availabilty is for all days.
.T.
Default:
43 emp_lthu Logical 1 0 N
Comments: Location Availability, for day of the week. When checked availabilty is for all days.
.T.
Default:
44 emp_lfri Logical 1 0 N
Comments: Location Availability, for day of the week. When checked availabilty is for all days.
.T.
45 emp_lsat Logical 1 0 N
Comments: Location Availability, for day of the week. When checked availabilty is for all days.
.T.
Default:
Emptype
Employee Type Definition
# Field Name Type Len Dec Null
1 etp_pk Character 5 0 N
Caption: Etp Pk
Comments: Primary Key
2 etp_surck Character 5 0 N
Caption: Type
Comments: Code
Fp26field: code
3 etp_descck Character 30 0 N
Caption: Type Description
Comments: Description
Fp26field: descr
4 etp_lieu Logical 1 0 N
Caption: Lieu
Comments: Lieu or Comp time may be granted for working Stat. Holiday.
Fp26field: lieu
5 etp_lieuoff Logical 1 0 N
Comments: Lieu or Comp time may be used as Time off.
Fp26field: lieu
6 etp_minhrs Numeric 7 2 N
Comments: Min. work hours
Fp26field: minhours
7 etp_maxhrs Numeric 7 2 N
Caption: Etp Maxhrs
Comments: Max Work Hours
Fp26field: maxhours
8 etp_perwks Numeric 2 0 N
Caption: Etp Perwks
Comments: Hours per week
Fp26field: perweeks
9 etp_color Integer 4 0 N
Caption: Etp_Color
Comments: Colour
Default: 16777215
Generatepk
System-generated Primary Keys
# Field Name Type Len Dec Null
1 gpk_pk Character 128 0 N
Caption: Primary Key
Comments: TableName for which to generate PKs
2 gpk_currentnumber Integer 4 0 N
Caption: Current PK
Comments: Current PK Number in use
3 gpk_currentalpha Character 5 0 N
Caption: Current AlphaNumeric PK
Comments: Current AlphaNumeric PK in use
Lleutime
Lieu or Comp Time Off
# Field Name Type Len Dec Null
1 let_empfk Character 5 0 N
Comments: Employee key - links to employee.dbf
Fp26fK: id_num,employee.id_num
2 let_date Date 8 0 N
Caption: Date
Comments: Date
Fp26field: date
3 let_hours Numeric 6 2 N
Caption: Hours
Comments: hours
Fp26field: IIF(type='L',hours,0)
4 let_paid Numeric 6 2 N
Comments: paid - not used
Fp26field: IIF(type='L',paid_hours,0)
5 let_ltpfk Character 5 0 N
Caption: Let Ltpfk
Comments: Lieu or Comp type primary key - links to leutype.dbf
Leutype
Lieu or Comp Type Time-off Definition
# Field Name Type Len Dec Null
1 ltp_pk Character 5 0 N
Comments: Primary Key
2 ltp_surck Character 5 0 N
Caption: Code
Comments: Code
3 ltp_descck Character 30 0 N
Caption: Description
Comments: Description
4 ltp_absence Logical 1 0 N
Caption: Absence
Comments: Absence
Lieu
Lieu or Comp Time Earned
# Field Name Type Len Dec Null
1 leu_empfk Character 5 0 N
Comments: Employee key - links to employee.dbf
Fp26fK: id_num,employee.id_num
2 leu_date Date 8 0 N
Caption: Comp Date
Comments: Date
Fp26field: date
3 leu_hours Numeric 5 2 N
Caption: Hours
Comments: Hours
Fp26field: hours
4 leu_descr Character 30 0 N
Caption: Description
Comments: Description
Location
Location Definition
# Field Name Type Len Dec Null
1 loc_pk Character 5 0 N
Comments: Primary Key
2 loc_surck Character 5 0 N
Caption: Code
Comments: Code
Fp26field: code
3 loc_descck Character 30 0 N
Caption: Description
Comments: Description
Fp26field: descr
4 loc_color Integer 4 0 N
Caption: Loc Color
Comments: Colour
5 loc_note Memo 4 0 N
Comments: Note
Mastbook
Schedule (formerly master) Pattern bookings
# Field Name Type Len Dec Null
1 sbk_date Date 8 0 N
Comments: date
Fp26field: date
2 sbk_locfk Character 5 0 N
Comments: Location key - Links location.dbf
Fp26fK: home,location.code
3 sbk_shffk Character 5 0 N
Comments: Shift key - Links shift.dbf
Fp26fK: shift,shift.shift
4 sbk_slot Character 5 0 N
Comments: Slot key - associated with field sro_slot in table mastrot.dbf. Ensures only one
booking belongs with each row on the schedule
Fp26field: slot
5 sbk_empfk Character 5 0 N
Comments: Employee key - links to employee.dbf
Fp26fK: id_num,employee.id_num
6 sbk_etpfk Character 5 0 N
Comments: employee type key - Links emptype.dbf
Fp26fK: rank,empltype.code
7 sbk_hours Numeric 5 2 N
Comments: Work Hours
Fp26field: hours
8 sbk_start DateTime 8 0 N
Comments: Start Time
Fp26field: THISFORM.zzdatetime(date,start)
9 sbk_end DateTime 8 0 N
Comments: End Time
Fp26field: THISFORM.zzdatetime(date,end)
10 sbk_note Memo 4 0 N
Comments: Note
Fp26field: note
11 sbk_srhfk Character 5 0 N
Comments: Schedule Pattern key - Links mastrth.dbf
Fp26fK: master,templhdr.code
12 sbk_timestart Numeric 1 0 N
Caption: Sbk Timestart
Comments: Designates the day that the booking is associated with.
Default: 1
Mastrot
Schedule (formerly master) Pattern Rows
# Field Name Type Len Dec Null
1 sro_srhfk Character 5 0 N
Comments: Schedule Pattern key - Links mastrth.dbf
Fp26fK: master,templhdr.code
2 sro_seq Character 5 0 N
Comments: The sequence that the row is displayed in.
Fp26field: STR(sequence,5)
3 sro_locfk Character 5 0 N
Comments: Location key - Links location.dbf
Fp26fK: home,location.code
4 sro_shffk Character 5 0 N
Comments: Shift key - Links shift.dbf
Fp26field: shift
Fp26fK: shift,shift.shift
5 sro_slot Character 5 0 N
Caption: Sro Slot
Comments: Slot key - associated with field sbk_slot in table mastbook.dbf. Ensures only one
booking belongs with each row on the schedule
Fp26field: slot
Mastrth
Schedule (formerly master) Pattern general information
# Field Name Type Len Dec Null
1 srh_pk Character 5 0 N
Comments: Primary Key
2 srh_date Date 8 0 N
Comments: Date
Fp26field: rotation
3 srh_length Numeric 3 0 N
Caption: Srh Length
Comments: Schedule Length
Fp26field: days
4 srh_note Memo 4 0 N
Caption: Srh Note
Comments: Note
5 srh_descr Character 22 0 N
Comments: Description
Fp26field: descr
6 srh_closed Logical 1 0 N
Comments: Closed or open control
Minstf
Staffing levels - applied on the staffing level reports
# Field Name Type Len Dec Null
1 mns_pk Character 5 0 N
Comments: Primary key
Default: PADL(x3genpk("minstf","TT"),LENC(mns_pk),"0")
2 mns_locfk Character 5 0 N
Caption: Mns Locfk
Comments: Location key - Links location.dbf
3 mns_shffk Character 5 0 N
Comments: Shift key - Links shift.dbf
4 mns_sun Numeric 7 0 N
Comments: Staffing level for specified day
5 mns_mon Numeric 7 0 N
Comments: Staffing level for specified day
6 mns_tue Numeric 7 0 N
Comments: Staffing level for specified day
7 mns_wed Numeric 7 0 N
Comments: Staffing level for specified day
8 mns_thu Numeric 7 0 N
Comments: Staffing level for specified day
9 mns_fri Numeric 7 0 N
Comments: Staffing level for specified day
10 mns_sat Numeric 7 0 N
Comments: Staffing level for specified day
Othtime
Other Time Off
# Field Name Type Len Dec Null
1 ott_empfk Character 5 0 N
Comments: Employee key - links to employee.dbf
Fp26fK: id_num,employee.id_num
2 ott_date Date 8 0 N
Caption: Date
Comments: date
Fp26field: date
3 ott_hours Numeric 6 2 N
Caption: Hours
Comments: hours
Fp26field: IIF(type='O',hours,0)
4 ott_paid Numeric 6 2 N
Caption: Paid
Comments: paid - not used
Fp26field: IIF(type='O',paid_hours,0)
5 ott_otpfk Character 5 0 N
Comments: Other type primary key - links to othtype.dbf
Fp26fK: othtime,othtime.code
Othtype
Other Type Time-off definition
# Field Name Type Len Dec Null
1 otp_pk Character 5 0 N
Comments: Primary Key
2 otp_surck Character 5 0 N
Caption: Code
Comments: Code
Fp26field: TRIM('O'+LOWER(code))
3 otp_descck Character 30 0 N
Caption: Description
Comments: Description
Fp26field: descr
4 otp_absence Logical 1 0 N
Caption: Absence
Comments: Absence
Refusals
Refusals to Work
# Field Name Type Len Dec Null
1 rfs_date Date 8 0 N
Comments: Date
Fp26field: date
2 rfs_locfk Character 5 0 N
Comments: Location key - Links location.dbf
Fp26fK: home,location.code
3 rfs_shffk Character 5 0 N
Comments: Shift key - Links shift.dbf
Fp26fK: shift,shift.shift
4 rfs_empfk Character 5 0 N
Comments: Employee key - links to employee.dbf
Fp26fK: id_num,employee.id_num
5 rfs_hours Numeric 6 2 N
Comments: hours
Fp26field: hours
6 rfs_memo Memo 4 0 N
Comments: note
Fp26field: comment
Reminders
Employee Reminders
# Field Name Type Len Dec Null
1 rmd_pk Character 5 0 N
Caption: Rmd Pk
Comments: Primary Key
Default: PADL(x3genpk("REMINDERS","TT"),LENC(rmd_pk),"0")
2 rmd_empfk Character 5 0 N
Caption: Rmd Empfk
Comments: Employee key - links to employee.dbf
3 rmd_date Date 8 0 N
Caption: Date
Comments: Date
4 rmd_warndays Numeric 3 0 N
Caption: Warning
Comments: Number of days of advance warning
5 rmd_title Character 30 0 N
Caption: Reminder
Comments: Title
6 rmd_notes Memo 4 0 N
Caption: Notes
Comments: Notes
7 rmd_complete Logical 1 0 N
Caption: Complete?
Comments: Complete
Schedbook
Live Schedule bookings
# Field Name Type Len Dec Null
1 sbk_srhfk Character 5 0 N
Comments: Live Schedule key - Links Schedrth.dbf
Fp26fK: rotation,schedhdr.rotation
2 sbk_date Date 8 0 N
Comments: Date
Fp26field: date
3 sbk_locfk Character 5 0 N
Comments: Location key - Links location.dbf
Fp26fK: home,location.code
4 sbk_shffk Character 5 0 N
Comments: Shift key - Links shift.dbf
Fp26fK: shift,shift.shift
5 sbk_slot Character 5 0 N
Comments: Slot key - associated with field sro_slot in table schedrot.dbf. Ensures only one
booking belongs with each row on the schedule
Fp26field: slot
6 sbk_empfk Character 5 0 N
Comments: Employee key - links to employee.dbf
Fp26fK: id_num,employee.id_num
7 sbk_etpfk Character 5 0 N
Comments: employee type key - Links emptype.dbf
Fp26fK: rank,empltype.code
8 sbk_hours Numeric 5 2 N
Comments: Work Hours
Fp26field: hours
9 sbk_start DateTime 8 0 N
Comments: Start Time
Fp26field: THISFORM.zzdatetime(date,start)
10 sbk_end DateTime 8 0 N
Comments: End Time
Fp26field: THISFORM.zzdatetime(date,end)
11 sbk_note Memo 4 0 N
Comments: Note
Fp26field: note
12 sbk_timestart Numeric 1 0 N
Caption: Sbk Timestart
Comments: Designates the day that the booking is associated with.
Default: 1
13 sbk_todate Date 8 0 N
Comments: Associated Time-off date. Employee might be removed due to Time-off being taken.
14 sbk_toempfk Character 5 0 N
Caption: Sbk Toempfk
Comments: Originally schedule employee is now taking Time-off. This field stores the Employee
Key of orginal booked employee - links to employee.dbf.
Schedrot
Live Schedule Rows
# Field Name Type Len Dec Null
1 sro_srhfk Character 5 0 N
Comments: Live Schedule key - Links schedrth.dbf
Fp26fK: date,schedhdr.rotation
2 sro_seq Character 5 0 N
Comments: The sequence that the row is displayed in.
Fp26field: STR(sequence,5)
3 sro_locfk Character 5 0 N
Comments: Location key - Links location.dbf
Fp26fK: home,location.code
4 sro_shffk Character 5 0 N
Comments: Shift key - Links shift.dbf
Fp26fK: shift,shift.shift
5 sro_slot Character 5 0 N
Caption: Sro Slot
Comments: Slot key - associated with field sbk_slot in table schedbook.dbf. Ensures only one
booking belongs with each row on the schedule
slot
Fp26field:
Schedrth
Live Schedule general information
# Field Name Type Len Dec Null
1 srh_pk Character 5 0 N
Comments: Primary Key
2 srh_date Date 8 0 N
Comments: Date
Fp26field: rotation
3 srh_length Numeric 3 0 N
Caption: Srh Length
Comments: Length in days
4 srh_note Memo 4 0 N
Caption: Srh Note
Comments: note
5 srh_closed Logical 1 0 N
Comments: Closed or open control
Shift
Shift Definition
# Field Name Type Len Dec Null
1 shf_pk Character 5 0 N
Comments: Primary Key
2 shf_surck Character 5 0 N
Caption: Code
Comments: Code
Fp26field: shift
3 shf_descck Character 30 0 N
Caption: Description
Comments: Description
Fp26field: time
4 shf_start DateTime 8 0 N
Comments: Start Time
Fp26field: THISFORM.zzdatetime(DATE(),start)
5 shf_end DateTime 8 0 N
Comments: End Time
Fp26field: THISFORM.zzdatetime(DATE(),end)
6 shf_hours Numeric 5 2 N
Comments: Work Hours
Fp26field: hours
7 shf_firstlast Numeric 1 0 N
Comments: Designation for whether the shift is the first or last shift in the day.
1
Default: first_last
Fp26field:
8 shf_color Integer 4 0 N
Caption: Shf Color
Comments: Colour
Default: 14869218
Sictime
Sick Time-off
# Field Name Type Len Dec Null
1 sit_empfk Character 5 0 N
Comments: Employee key - links to employee.dbf
Fp26fK: id_num,employee.id_num
2 sit_date Date 8 0 N
Caption: Date
Comments: Date
Fp26field: date
3 sit_hours Numeric 6 2 N
Caption: Hours
Comments: hours
Fp26field: IIF(type='S',hours,0)
4 sit_paid Numeric 6 2 N