appointment Table (9)

•

Environment: onsite, online

• 17 minutes to read

Tasks, appointments, followups, phone calls; and documents (document_id != 0). An appointment always has a corresponding record in VisibleFor specifying who may see this.

Fields

Name	Description	Type	Null
appointment_id	Primary key	PK
contact_id	Contact ID of owning contact, may be 0	FK contact	●
person_id	Person ID of person the appointment is with, may be 0	FK person	●
associate_id	ID of associate whose diary the appointment is in, REQUIRED	FK associate
group_idx	Group of owning associate at the time tha ppnt was created	FK UserGroup
registered	Registered date	UtcDateTime
registered_associate_id	Registered by whom	FK associate	●
done	date + start time this task was done	DateTime
do_by	date + start time planned	DateTime	●
leadtime	Time blocked (minutes) BEFORE starttime	UShort	●
task_idx	Link to Task list, OR to DocTmpl list, if this is a document	FK Task
priority_idx	Link to priority list	FK Priority	●
type	where=no start time,note,docin, docout	Enum AppointmentType	●
status	status=done,started, not started, hidden	Enum AppointmentStatus	●
private	Obsolete, but still maintained field for appointment privacy; denormalization of visiblefor status	Enum AppointmentPrivate	●
alarm	Alarm leadtime, the alarm flag has moved to hasAlarm	UShort	●
text_id	ID of record containing appointment text	FK text	●
project_id	ID of project referred to, may be 0	FK project	●
mother_id	ID of mother appointment; self if booking master, master ID if booking slave, 0 if normal appointment. However, if 0 and assoc_id != reg_id then this is an assigned appointment, indicated vt type = kBooking	FK appointment	●
document_id	Points to document record; 0 for non-document appointments!	FK document	●
color_index	Appointment colour, used only in Japanese versions. Western versions take colour from Task	UShort	●
invitedPersonId	Valid when mother_id != 0 && mother_id != id	FK person	●
activeDate	The date to be used for searching & showing	DateTime	●
endDate	Date + end time planned	DateTime	●
lagTime	as leadtime, but after the end - time blocked for travel etc.	UShort	●
source	For future integration use; source of record	UShort	●
userdef_id	User defined table record 1 (for future use)	FK udappntsmall	●
userdef2_id	User defined table record 2 (for future use)	FK udappntlarge	●
updated	Updated date	UtcDateTime
updated_associate_id	Updated by who	FK associate
updatedCount	Number of updates made to this record	UShort
activeLinks	Number of active links to documents, other appointments, and such	UInt	●
recurrenceRuleId	Pointer to recurrence rule, for recurring appointments, otherwise 0	FK RecurrenceRule	●
location	Location for appointment, defaulted from invited resource of type place and other rules, but you can write anything you want here	String(254)	●
alldayEvent	Is this an all day event: 0 = No, 1 = Yes	Enum AllDayEvent	●
freeBusy	What kind of time is this: 0 = Busy, 1 = Free	Enum FreeBusy	●
rejectCounter	How many invitees have rejected this appointment	UShort	●
emailId	If invitation and status changes should be mailed, this is the ID of the email address used	FK Email	●
rejectReason	Why was this booking or assignment rejected, the RejectReason list is a source of suggestions but you can write anything here	String(254)	●
hasAlarm	Does this appointment have an alarm	Bool	●
assignedBy	Who (last) assigned this appointment to associate_id?	FK associate	●
preferredTZLocation	Preferred timezone location to use when displaying/editing this appointment	FK TZLocation	●
sale_id	Owning sale, if any (may be 0)	FK sale	●
suggestedAppointmentId	Suggested guide item that this appointment is an instance of (Note: NOT VALID for document-type appointments, they have their own link)	FK SuggestedAppointment	●
suggestedDocumentId	Suggested guide item that this document is an instance of (Note: NOT valid for appointments, they have their own link)	FK SuggestedDocument	●
isMilestone	Is this appointment a milestone?	Bool	●
join_videomeet_url	Blank when not a video meeting. Filled with Join Meeting URL when created.	String(2000)	●
centralservice_videomeet_id	GUID for video meeting in central services – this is set when we create meetings from SuperOffice. It is blank for incoming meetings created from inbox.	String(100)	●
original_start_date	The original start date for an appointment in a recurring series	DateTime	●
agenda_text_id	ID of record containing agenda text	FK text	●
internal_notes_text_id	ID of record containing internal notes text	FK text	●
modified_appointment_fields	Which important fields of an appointment have been changed. Used by notifications.	Enum ModifiedAppointmentFields	●
cautionWarning	Status field to indicate appointments that have some sort of problem	Enum AppointmentCautionWarning	●
mother_associate_id	Associate/owner of the mother appointment	FK associate	●

appointment table relationship diagram

Values needed to access the appointment tables

This table must contain special values that would be difficult to guess correctly. These are listed below with a description of how to use them.

Note

Booking means a group reservation where you invite several associates or resources to join.

Appointment Type

Appointment Type	ID	Comment
Undefined	0	The appointment type is not defined- used when initializing
Appointment in Diary	1	Show in diary. If overdue or done today, show in checklist also
Appointment in Check list	2	Appointment is a followup with no specific starttime
Note shown as a paper clip on e.g. contacts	3	Paper clip on diary, expands to "post-it" note when clicked
Incoming Document	4	Document
Saved report	5	Saved Report (i.e., not a Report Definition but the result of an actual report run)
Booking, made for diary	6	When accepted this will change type = 1
Booking, made for check list	7	When accepted this will change type = 2
Document - mail merge draft	8	Document is a template for mailmerge operations
Document - mail merge final	9	Document is a result of a mailmerge operations (there are probably many documents like this!)

Status type

Status type	ID	Comment
Appointment type is a Post-IT	0	Unknown / Appointment is a Post-IT, used together with ApointmentType.Note only
Not started	1	Resources and mother appointments get this status on new appointments, implies the green mark for accepted
Started	2	Marked when the appointment is actually started, but not completed
Completed	3	Completed is set to avoid changes to the appointment
Hidden	4	Appointment is hidden
Booking	5	Appointment is a booking invitation (initial status for a booking)
Booking has moved	6	You may have seen, declined, or accepted the booking, but it has been moved, so you will be asked again.
Booking seen	7	You have seen the booking, but not declined or accepted it.
Booking moved seen	8	The booking has been moved and you have seen the change, but not declined or accepted it.
Booking declined	9	You have declined the booking.
Booking deleted	10	Status of "child" appointment whose "mother" has been deleted (the meeting is canceled)
Assignment	11	You are assigned this appointment (initial status, like Booking(5) )
Assignment seen	12	You have seen the assignment, but not accepted or declined it
Assignment declined	13	You have declined the appointment assigned to you.

Private

Private Type	ID	Comment
Public appointment	0	This appointment can be read by anyone
Private appointment	1	This appointment can only be read/seen by the owner
Private appointment	2	This appointment can only be read by members in my group/dept

Task_idx

Is either template_idx (DocTmpl_id in DocTmpl) or task_idx (Task_id in Task), depending on the type value.

Type	`Task_Idx`	Status
Appointment in diary	`task_idx`	1 = not started, 2 = started, 3 = done
Appointment in check list	`task_idx`	1 = not started, 2 = started, 3 = done
Booking, made for diary	`task_idx`	booking (`mother_id` > 0)
Booking_made for check list	`task_idx`	booking (`mother_id` > 0)
Note – post it
Document in	`template_idx`	1 = not started, 2 = started, 3 = done

Mother_id

`mother_id`	`appointment_id`	Comment
0		This is a simple appointment
> 0	`mother_id` = `appointment_id`	This is a booking, and the appointment is the master booking (the one that is inviting)
> 0	`mother_id` not like `appointment_id`	This is a booking, and the appointment is the slave booking (someone who is invited)

Bookings share a single text record. In SuperOffice the person doing the inviting is the only one allowed to change the text of this booking (appointment). A booking may have associate_id = 0, if it is an invitation to an external participant; in that case the InvitedPersonId is the person ID of the participant. Person_id is always the ID of the person the meeting is with (not of the person who is invited to it).

FreeBusy

FreeBusy Type	ID	Comment
Busy	0	This appointment is marked as busy in the diary
Free	1	This appointment is marked as free in the diary

Documents linked to an appointment are linked in the Relations table. There is no special marking in appointment so always check if the active appointment has valid entries in the Relations table. The relationdefinition table holds the reldef_id for "Link to follow-up", "Link to document", and "Link to sale". Our GUI currently only supports links to documents.

To assign an appointment to someone

Dim apt As IAppointment = so.Database.CreateAppointment()
apt.SetDefaults()
apt.Type = 1 ' or 2
apt.Status = 11
apt.AssignedBy = mamagersId
apt.Person = so.Database.GetPerson(11)
apt.Contact = so.Database.GetContact(3)
apt.Project = so.Database.GetProject(4)
apt.Task = so.Database.GetListItem(SOTableId.enTableTask, 1)
apt.Save()

Accepting an appointment

Dim apt As IAppointment = so.Database.GetAppointment(120)
apt.AcceptInvitation(EnResponse.enResponseAccept)

 ' set status from 11 to 1
 ' set assignedBy = associate-id (WARNING: This may change in the future)

Rejecting an appointment

set status = 13
set associate-id = assigned-by

To create an invitation to an appointment

Create a second appointment as copy of first.
Appnt.Mother_id = appnt.appnt_id
Appnt.invitedPerson = appnt.associate-id's person-id
Appnt2.Mother_id = appnt.appnt_id
Appnt2.Associate_id = invited user
Appnt2.invitedPerson = invited user's
Appnt2.type = 6 or 7
Appnt2.status = 5

On the whole it is best to use the functions on the Appointment object to do this sort of thing:

SoAppointment.AcceptInvitations
SoAppointment.AddParticipant
SoAppointment.RemoveParticipant

Indexes

Fields	Types	Description
appointment_id	PK	Clustered, Unique
contact_id	FK	Index
person_id	FK	Index
group_idx	FK	Index
done	DateTime	Index
do_by	DateTime	Index
text_id	FK	Index
project_id	FK	Index
mother_id	FK	Index
document_id	FK	Index
activeDate	DateTime	Index
source	UShort	Index
userdef_id	FK	Index
userdef2_id	FK	Index
sale_id	FK	Index
suggestedAppointmentId	FK	Index
suggestedDocumentId	FK	Index
project_id, type	FK, Enum	Index
contact_id, type	FK, Enum	Index
associate_id, type, status, done	FK, Enum, Enum, DateTime	Index
associate_id, type, status, do_by	FK, Enum, Enum, DateTime	Index
associate_id, type, endDate, do_by	FK, Enum, DateTime, DateTime	Index
associate_id, type, status, activeDate	FK, Enum, Enum, DateTime	Index
recurrenceRuleId, activeDate, associate_id	FK, DateTime, FK	Index
recurrenceRuleId, activeDate, mother_id	FK, DateTime, FK	Index
associate_id, activeDate, mother_id, appointment_id	FK, DateTime, FK, PK	Unique
mother_id, status, activeDate, registered_associate_id, registered, assignedBy, group_idx, type, associate_id, appointment_id	FK, Enum, DateTime, FK, UtcDateTime, FK, FK, Enum, FK, PK	Unique
project_id, type, activeDate, document_id, associate_id, group_idx, mother_id, assignedBy, appointment_id, registered, registered_associate_id	FK, Enum, DateTime, FK, FK, FK, FK, FK, PK, UtcDateTime, FK	Unique
type, activeDate	Enum, DateTime	Index
associate_id, status, do_by, endDate	FK, Enum, DateTime, DateTime	Index

Relationships

Table	Description
appointment	Tasks, appointments, followups, phone calls; and documents (document_id != 0). An appointment always has a corresponding record in VisibleFor specifying who may see this.
associate	Employees, resources and other users - except for External persons
contact	Companies and Organizations. This table features a special record containing information about the contact that owns the database.
document	Documents, this table is an extension of the Appointment table. There is always a corresponding appointment record; the relation between appointment and document is navigable in both directions. A document-type appointment record always has a corresponding document record and a record in VisibleFor specifying who may see this.
Email	Email addresses for contacts, projects and persons
email_item	Email data
invitation	Invitations to events
person	Persons in a company or an organizations. All associates have a corresponding person record
Priority	Priority list table. Used with appointments: low, high.
project	Projects
QuoteVersion	There may be multiple Versions of a Quote, with one of them active
RecurrenceRule	The recurrency pattern for a recurring event. Each recurring event has an appointment.recurrenceRuleId pointing to its pattern.
sale	Sales For every Sale record edited through the SuperOffice GUI, a copy of the current version of the record will be saved in the SaleHist table. This also applies to editing done through the SaleModel COM interface, but not to editing done through the OLE DB Provider or other channels.
SaleHist	Mirror image of the Sale table, providing a full transaction history. Every time you edit a sale, the current record of the sale is also saved here.
SuggestedAppointment	Defines a suggested appointment for use in a Sale Guide or Project Guide
SuggestedDocument	Unique owner of a set of licensed modules
Task	Task list table, activity types, like Phone, Meeting
text	Long text fields from all over the system
TZLocation	Time zone location
udappntlarge	User-defined fields
udappntsmall	User-defined fields
UserGroup	Secondary user groups
VisibleFor	Visible for rights, who may see this appointment/document, sale, salehist or selection

Replication Flags

Area Management controlled table. Contents replicated to satellites and traveller databases.
Replicate changes UP from satellites and travellers back to central.
Copy to satellite and travel prototypes.
Cache table during filtering.

Security Flags

Sentry controls access to items in this table using user's Role and data rights matrix.
Visibility controlled via matching VisibleFor row.