appointment table

appointment Table (9)

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

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.

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 attendee; in that case the InvitedPersonId is the person ID of the attendee. 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
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
agenda_text_id	FK	Index
type, activeDate	Enum, DateTime	Index
created_by_workflow_id	FK	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.
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
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
workflow	SuperOffice specific info about a workflow

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.

Overview

Getting started