Thursday, January 12, 2006

Adding the Creator of Calendar appointments to appointments that already exist in a public calendar

A while ago I posted this event sink that can be used in a public calendar folder that will add the person who created the appointment so that it is visible when the appointment is looked at in Outlook or OWA. Being an event sink this will only have an effect on any new appointments that where added to the calendar. Somebody asked this week about modifying existing appointments in a calendar. This can be done using the same type of code and also including a query for a specific date range that will create a recordset of all the items in the calendar and then iterate though that recordset and add the creator to the location field of the appointment if it doesn’t already exist. Because this script use Exoledb it must be run locally on the server where the public folder is located. The date range in the query is set to modify all appointments between December 2005 and December 2006 and will affect both normal and recurring appointments.

I’ve put a downloadable version of the script here the code itself looks like

calurl = "file://./backofficestorage/ folders/calenderfoldername"
call updateappointment(calurl,0)
wscript.echo "Reccuring Appointments"
call updateappointment(calurl,1)

sub updateappointment(CalendarURL,instancetype)

set rec = createobject("ADODB.Record")
set rec1 = createobject("ADODB.Record")
set rs = createobject("ADODB.Recordset")
Rec.Open CalendarURL
Set Rs.ActiveConnection = Rec.ActiveConnection
Rs.Source = "SELECT ""DAV:href"", " & _
" ""urn:schemas:calendar:location"", " & _
" ""urn:schemas:calendar:instancetype"", " & _
" ""urn:schemas:calendar:dtstart"", " & _
" ""urn:schemas:calendar:dtend"", " & _
" """" " & _
"FROM scope('shallow traversal of """ & CalendarURL & """') " & _
"WHERE (""urn:schemas:calendar:dtstart"" >= CAST(""2005-12-01T08:00:00Z"" as 'dateTime'))
" & _
"AND (""urn:schemas:calendar:dtend"" <= CAST(""2006-12-01T08:00:00Z"" as 'dateTime'))"
& _
" AND (""urn:schemas:calendar:instancetype"" = " & instancetype & ")"
If Not (Rs.EOF) Then
Do Until Rs.EOF
ItemURL = Rs.Fields("DAV:Href").Value
wscript.echo ItemURL
creator = " Created by " & Rs.Fields("").Value

wscript.echo creator
if instr(rs.fields("urn:schemas:calendar:location"),"Created by") then
wscript.echo "Creator Exists"
else Rs.Fields("DAV:Href").Value,,3
rec1.fields("urn:schemas:calendar:location") = rec1.fields("urn:schemas:calendar:location")
& creator
wscript.echo "Added Creator"
end if
end if

end sub


No comments: