IN PROGRESS

Conceptual Overview

The approach here is fairly manual, but relatively straight-forward:

  1. One Time Steps
    • Create a SQL Report in Control to Export the Data
    • Create a Map File in Outlook to Import the Data (if required)
    • [Optional] Set up Outlook to Sync With Google Calendar
    • [Optional] Add a shortcut in Control to open the Calendar within Control
  2. Repeated Steps
    • Export the Data from Control. (Mark the data as exported.)
    • Import the Data into Outlook.
Technical Notes
  • By default, Outlook matches the column heading name in the import file with its own field of the same name. Therefore, if you name the export column header the same name as the Outlook field it will be able to automatically match it up automatically, eliminating the need for this step.
    • Create a Map File in Outlook to Import the Data
Detailed Steps - One Time

The following items must be done once in order to set Control up to export the data to Outlook.

Create a SQL Report in Control to Export the Data

  1. Click on Setup | Reporting Setup
  2. Expand Reports on Main Menu
  3. Expand “User Reports” group (or another place if desired)
  4. Click on “User Reports”.
  5. Click on the down-arrow on the Add button and choose New SQL Report
  6. Enter a Name, such as “Export My Calendar”
  7. Click the radio button for “SQL”
  8. Paste the SQL below into the SQL Statement memo area.
  9. Set Options at the bottom of the panel to Create View and Save.
  10. Click on the Options tab.
  11. Click on the Show Detail button under Execute.
  12. Change the File Type to Comma Delimited.
  13. Choose a Name for the file to be created. You can use merge fields. My Preference for this one is Calendar .csv, so that it puts the users name and the Date and Time in the name of the export.
  14. Select an alternate destination for this file if you want the file saved somewhere else. Remember that others might run the report so make sure the path works for everyone. (See Report Folder Aliases for some hints.)
  15. Save the SQL Report by clicking Save on the Action Toolbar.
-- Set the name of the calendar you want to access ...
DECLARE @LastName VARCHAR(50);
DECLARE @FirstName VARCHAR(50);
DECLARE @Delimiter VARCHAR(7);
DECLARE @AddressDelimiter VARCHAR(7);
SET @LastName  = 'St.Cyr';
SET @FirstName = 'Scott';
DECLARE @CalendarID INT;
SET @CalendarID = (SELECT ID FROM Employee WHERE LastName = @LastName AND FirstName = @FirstName);
DECLARE @MinDate datetime;  -- Set the absolute earliest date you want to query
DECLARE @MaxDate datetime;  -- Set the absolue latest date you want to query
DECLARE @ZeroDate DATE;
SET @MinDate   = GETDATE()- 30;
SET @MaxDate   = GETDATE()+ 30;
SET @ZeroDate  = '12/30/1900';
SET @Delimiter = ' //'+CHAR(13)+CHAR(10);
SET @AddressDelimiter = ', ';
SELECT
  J.ID AS [KeyID],
  CONVERT( VARCHAR(10), COALESCE(J.StartDateTime, J.QueryStartDateTime), 101) AS [START DATE],
  CONVERT( VARCHAR(12), CONVERT(TIME, CASE WHEN (CA.AllDayEvent=1 OR CA.IsTimeless=1) THEN '00:00' ELSE COALESCE(J.StartDateTime, J.QueryStartDateTime) END), 0 ) AS [START TIME],
  J.Description AS [Subject],
  RTrim(Address.StreetAddress1 + COALESCE(' ' + Address.StreetAddress2, ''))
  + @AddressDelimiter + COALESCE(Address.City + ', ', '') + COALESCE(Address.State + '  ', '') + COALESCE(Address.PostalCode, '')
  + ' (' + Account.CompanyName + ')'
  AS [Location],  
 
  Account.CompanyName 
  + ' (' + AC.FirstName + ' ' + AC.LastName 
  + COALESCE(' ' + Phone.FormattedText, '') + ') ' 
  AS [Meeting Organizer],
 
  'Company:  '+ Account.CompanyName 
  + @Delimiter + 'Contact:  ' + (AC.FirstName + ' ' + AC.LastName)
  + @Delimiter + 'Phone:    ' + Phone.FormattedText
  + @Delimiter + 'Employee: ' + Employee.LastName +', '+Employee.FirstName
  + @Delimiter + 'Notes:    ' + CAST(J.Notes AS VARCHAR(1024))
    AS [Description],
  CONVERT( VARCHAR(10), COALESCE(J.EndDateTime, J.QueryEndDateTime), 101) AS [END DATE],
  CONVERT( VARCHAR(12), CONVERT(TIME, CASE WHEN (CA.AllDayEvent=1 OR CA.IsTimeless=1) THEN '23:59:59.9' ELSE COALESCE(J.EndDateTime, J.QueryEndDateTime) END), 0 ) AS [END TIME],
  CASE WHEN (CA.AllDayEvent=1 OR CA.IsTimeless=1) THEN 'TRUE' ELSE 'FALSE' END AS [ALL DAY Event],
  CASE WHEN J.ReminderPrompt = 1 THEN 'TRUE' ELSE 'FALSE' END AS [Reminder ON/Off],
  CASE WHEN J.ReminderPrompt=0 THEN NULL ELSE CAST(J.ReminderDateTime AS DATE) END AS [Reminder DATE],
  CASE WHEN J.ReminderPrompt=0 THEN NULL ELSE CAST(J.ReminderDateTime AS TIME) END AS [Reminder TIME],
  CASE WHEN CA.PrivateEvent = 1 THEN 'TRUE' ELSE 'FALSE' END AS [Private],
  CA.PriorityText AS [Priority],
  Employee.LastName +', '+Employee.FirstName AS [Employee Name],
  Account.CompanyName AS [Company Name],
  (AC.FirstName + ' ' + AC.LastName) AS [Contact Name],
  Phone.FormattedText AS [Phone NUMBER],
  J.Notes AS [Notes ONLY],
  @CalendarID AS CalendarID,
  J.ModifiedDate AS [LAST Modified],
  J.AccountID AS AccountID,
  J.TransactionID AS TransHeaderID,
  J.ID AS JournalID
FROM
  Journal J
  JOIN ContactActivity CA     ON J.ID = CA.ID
  JOIN CalendarLink CL        ON J.ID = CL.JournalID
  LEFT JOIN TransHeader TH    ON J.TransactionID = TH.ID
  LEFT JOIN Account           ON J.AccountID = Account.ID 
  LEFT JOIN AccountContact AC ON J.ContactID = AC.ID
  LEFT JOIN Employee          ON CL.CalendarID = Employee.ID
  LEFT JOIN Address           ON COALESCE(AC.ShippingAddressID, Account.BillingAddressID) = Address.ID
  LEFT JOIN PhoneNumber Phone ON COALESCE(AC.MainPhoneNumberID, Account.MainPhoneNumberID) = Phone.ID
WHERE
  EmployeeID = @CalendarID
  AND J.QueryStartDateTime BETWEEN @MinDate AND @MaxDate
  AND J.CompletedDateTime IS NULL
  -- There is no field to save whether this record was exported or not. 
  -- In order to put this some place, we are going to use two depricated integer fields in the CalendarLink table.
  -- The CalendarStoreID will store the days from the "Zero Date" since the activity was last modified.
  -- The JournalStoreID will store the minutes from midnight since the activity was last modified.
  -- After the export we will need to run a query to set these values!
  AND ( (J.ModifiedDate - COALESCE(CL.CalendarStoreID, 0) > @ZeroDate)
        OR (DatePart(HOUR, J.ModifiedDate) * 24 + DatePart(MINUTE, J.ModifiedDate) > COALESCE(CL.JournalStoreID, 0)) )

Create a Map File in Outlook to Import the Data

Note: If you want it to sync with Google, you must use the default or main calendar.

  • Possible Map Fields:
    • Subject
    • Start Date
    • Start Time
    • End Date
    • End Time
    • All Day Event
    • Reminder on/off
    • Reminder Date
    • Reminder Time
    • Billing Information
    • Categories
    • Description
    • Location
    • Mileage
    • Priority
    • Private
    • Sensitivity
    • Show time as

[Optional] Set up Outlook to Sync With Google Calendar

[Optional] Add a shortcut in Control to open the Calendar within Control

Detailed Steps - Repeated

The following items must be done frequently (as often as desired) once in order actually send the data from Control to Outlook.

Export the Data from Control.

Import the Data into Outlook.

You could leave a comment if you were logged in.