• Share
    • Twitter
    • LinkedIn
    • Facebook
    • Email
  • Feedback
  • Edit
Show / Hide Table of Contents

appointment Table (9)

Some tooltip text!
• 17 minutes to read
 • 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 ●
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

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.
© SuperOffice. All rights reserved.
SuperOffice |  Community |  Release Notes |  Privacy |  Site feedback |  Search Docs |  About Docs |  Contribute |  Back to top