Table: Schedule_Item
Each item that can be scheduled has a row in this table.
Columns
NameTypeConstraintsDescription
schedule_item_KEYintNOT NULLAssigned by COMMON_dp_Allocate_Unique_Identifiers. Assigned primary key for this table.
update__staff_KEYintNOT NULL
update_date_utcdatetimeNOT NULL
create_date_utcdatetimeNOT NULL
schedule_item_type_KEYintNOT NULLKey to one of the [Schedule_Item_Type] records. Knowing the type can make finding the associated data easier.
1=Project
2=Task
3=To Do
4=Scheduled Benefit
5=Scheduled Phone Call
6=Meeting
7=Education Course
staff_assignment_method_KEYintNOT NULL1=Assigned Staff
2=Work Queue
priority_KEYintNOT NULLIf Priority does not apply to this type of Schedule_Item, use Normal priority.
998=Low
999=Normal
1000=High
is_all_day_eventbitNOT NULLWhen true, the time on [target_start_date_utc] and [target_complete_date_utc] must be 0:00:00 and may appear offset to whole days in local time for the user.
target_start_date_utcdatetimeThe start of the valid period for this item to be scheduled.
budgeted_hoursdecimal (9, 2)NOT NULLNumber of hours budgeted to this item. Items can have a wide target range specified, but only use a small portion of it.
descriptionnvarchar (250)NOT NULLUser defined description.
schedule_item_notenvarchar (-1)NOT NULLAny comments or notes about the item.
target_complete_date_exclusive_utcdatetimeThe end of the valid period (exclusive) for this item to be scheduled.
current_due_date_exclusive_utcdatetimeNOT NULLCurrent due date (exclusive) of this schedule item.
posted_actual_hoursdecimal (17, 2)NOT NULLCached value of the hours on posted Sheet_Entry rows associated with this row.
posted_actual_amountdecimal (17, 2)NOT NULLCached value of the amount on posted Sheet_Entry rows associated with this row.
remaining_hours_adjustmentdecimal (9, 2)NOT NULLCached value of the hours on Schedule_Item_Budget_Remaining_Entry rows associated with this row.
remaining_hours_adjustment_countintNOT NULLCached count of the Schedule_Item_Budget_Remaining_Entry rows associated with this row.
staff_qualifications__filter_KEYintAn optional Staff filter that represents Staff qualified to work on this item. Some types do not allow Staff qualifications.
target_start__time_zone_KEYsmallint (5)
target_complete__time_zone_KEYsmallint (5)
live_actual_hoursdecimal (17, 2)NOT NULLCached value of the hours on all Sheet_Entry rows associated with this row.
live_actual_amountdecimal (17, 2)NOT NULLCached value of the amount on all Sheet_Entry rows associated with this row.
schedule_item_series_KEYintThe linked [Schedule Item Series].
activity_KEYintThe linked [Activity].
client_KEYintThe linked [Client]. If of__contact_KEY is specified and is the primary contact of a Client, that client_KEY is also this value.
engagement_KEYintThe linked [Engagement].
tracking_description_KEYintThe linked [Tracking_Description].
reminder_date_utcdatetimeThe date the reminder is to be recorded.
benefit_KEYintLink to the benefit for items of type scheduled benefit.
scheduled_benefit_status_KEYintOnly allowed for items of type scheduled benefit.
1=Pending
2=Approved
3=Denied
approved__staff_KEYintThe Staff that approved the item.
approved_datesmalldatetimeThe date the item was approved.
scheduled_phone_call_status_KEYintOnly allowed for items of type scheduled phone call.
1=Scheduled
2=Attempted
3=Completed
with__contact_KEYintFor scheduled phone calls, the contact that is to be called.
of__contact_KEYintFor scheduled phone calls, the association of with__contact_KEY involved - for example which Client if of__contact_KEY is associated to multiple Clients.
is_openbitNOT NULLFalse when this item is closed by state (tracking description or status). May be true for items that are closed now because they have elapsed, like a Meeting in the past.
create__changeset_KEYintNOT NULL
update__changeset_KEYintNOT NULL
education_sponsor_KEYint
education_delivery_format_KEYint
locationnvarchar (250)NOT NULL
instructornvarchar (250)NOT NULL
costdecimal (9, 2)NOT NULL
Primary key
NameColumnsDescription
PK_Schedule_Item$schedule_item_KEYschedule_item_KEYCLUSTERED
Indexes
NameColumnsDescription
IX_Schedule_Item$activity_KEYactivity_KEY
IX_Schedule_Item$approved__staff_KEYapproved__staff_KEY
IX_Schedule_Item$benefit_KEYbenefit_KEY
IX_Schedule_Item$client_KEYclient_KEY
IX_Schedule_Item$education_delivery_format_KEYeducation_delivery_format_KEYIndex supports foreign key FK_Schedule_Item__Education_Delivery_Format__education_delivery_format_KEY
IX_Schedule_Item$education_sponsor_KEYeducation_sponsor_KEYIndex supports foreign key FK_Schedule_Item__Education_Sponsor__education_sponsor_KEY
IX_Schedule_Item$engagement_KEYengagement_KEY
IX_Schedule_Item$is_open$schedule_item_type_KEY$target_complete_date_exclusive_utcis_open, schedule_item_type_KEY, target_complete_date_exclusive_utc
IX_Schedule_Item$of__contact_KEYof__contact_KEY
IX_Schedule_Item$priority_KEYpriority_KEY
IX_Schedule_Item$schedule_item_KEY$current_due_date_exclusive_utcschedule_item_KEY, current_due_date_exclusive_utc
IX_Schedule_Item$schedule_item_series_KEY$current_due_date_exclusive_utcschedule_item_series_KEY, current_due_date_exclusive_utc
IX_Schedule_Item$schedule_item_type_KEY$is_openschedule_item_type_KEY, is_open
IX_Schedule_Item$schedule_item_type_KEY$target_complete_date_exclusive_utc$schedule_item_KEYschedule_item_type_KEY, target_complete_date_exclusive_utc, schedule_item_KEY
IX_Schedule_Item$scheduled_benefit_status_KEYscheduled_benefit_status_KEY
IX_Schedule_Item$scheduled_phone_call_status_KEYscheduled_phone_call_status_KEY
IX_Schedule_Item$staff_assignment_method_KEYstaff_assignment_method_KEY
IX_Schedule_Item$staff_qualifications__filter_KEYstaff_qualifications__filter_KEY
IX_Schedule_Item$target_complete__time_zone_KEYtarget_complete__time_zone_KEYIndex supports foreign key FK_Schedule_Item__Time_Zone__target_complete__time_zone_KEY
IX_Schedule_Item$target_complete_date_exclusive_utctarget_complete_date_exclusive_utc
IX_Schedule_Item$target_start__time_zone_KEYtarget_start__time_zone_KEYIndex supports foreign key FK_Schedule_Item__Time_Zone__target_start__time_zone_KEY
IX_Schedule_Item$target_start_date_utctarget_start_date_utc
IX_Schedule_Item$tracking_description_KEYtracking_description_KEY
IX_Schedule_Item$with__contact_KEYwith__contact_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Schedule_Item__Activity__activity_KEYactivity_KEYActivity
FK_Schedule_Item__Benefit__benefit_KEYbenefit_KEYBenefit
FK_Schedule_Item__Client__client_KEYclient_KEYClient
FK_Schedule_Item__Contact__of__contact_KEYof__contact_KEYContact
FK_Schedule_Item__Contact__with__contact_KEYwith__contact_KEYContact
FK_Schedule_Item__Education_Sponsor__education_sponsor_KEYeducation_sponsor_KEYEducation_Sponsor
FK_Schedule_Item__Engagement__engagement_KEYengagement_KEYEngagement
FK_Schedule_Item__Filter__staff_qualifications__filter_KEYstaff_qualifications__filter_KEYFilter
FK_Schedule_Item__Priority__priority_KEYpriority_KEYPriority
FK_Schedule_Item__Schedule_Item_Series__schedule_item_series_KEYschedule_item_series_KEYSchedule_Item_Series
FK_Schedule_Item__Staff__approved__staff_KEYapproved__staff_KEYStaff
FK_Schedule_Item__Time_Zone__target_complete__time_zone_KEYtarget_complete__time_zone_KEYTime_Zone
FK_Schedule_Item__Time_Zone__target_start__time_zone_KEYtarget_start__time_zone_KEYTime_Zone
FK_Schedule_Item__Tracking_Description__tracking_description_KEYtracking_description_KEYTracking_Description
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Education_Credit__Schedule_Item__schedule_item_KEYschedule_item_KEYEducation_Credit
FK_Project__Schedule_Item__schedule_item_KEYschedule_item_KEYProject
FK_Schedule_Entry__Schedule_Item__schedule_item_KEYschedule_item_KEYSchedule_Entry
FK_Schedule_Item_Assignment__Schedule_Item__schedule_item_KEYschedule_item_KEYSchedule_Item_Assignmenton delete cascade;
FK_Schedule_Item_Assignment_List_Cache__Schedule_Item__schedule_item_KEYschedule_item_KEYSchedule_Item_Assignment_List_Cacheon delete cascade;
FK_Schedule_Item_Budget_Remaining_Entry__Schedule_Item__schedule_item_KEYschedule_item_KEYSchedule_Item_Budget_Remaining_Entry
FK_Schedule_Item_Date_Index__Schedule_Item__schedule_item_KEYschedule_item_KEYSchedule_Item_Date_Indexon delete cascade;
FK_Schedule_Item_Last_In_Series__Schedule_Item__schedule_item_KEYschedule_item_KEYSchedule_Item_Last_In_Serieson delete set null;
FK_Schedule_Item_Pending_Reminder__Schedule_Item__schedule_item_KEYschedule_item_KEYSchedule_Item_Pending_Reminderon delete cascade;
FK_Schedule_Item_Reminder_Recorded_Event__Schedule_Item__schedule_item_KEYschedule_item_KEYSchedule_Item_Reminder_Recorded_Eventon delete cascade;
FK_Task__Schedule_Item__schedule_item_KEYschedule_item_KEYTask
FK_Sheet_Entry__Schedule_Item__education_course__schedule_item_KEYeducation_course__schedule_item_KEYSheet_Entry

Schema diagram