Hello all -I am moving the calendar entries to the sql db using the following script. I need to modify the script so that I get a single entry per date. So if there is a repeating entry for 5 days, I need to generate 1 record for each day that meeting repeats. How do I go about changing my script to accomidate that?
The first column formla(hidden) in the Canendar view displays the Date and Time fothe entry. I need to take this value and push it to the sql db. how do I convert the formula to use it in my script?
Column formula:
StartTime :=@Time(CalendarDateTime);
Datelist:= @If(AppointmentType=“2”;@Explode(@TextToTime(@Text(StartDateTime) + “-” + @Text(EndDateTime))) ;
@Explode(@TextToTime(@Text(CalendarDateTime) + “-” + @Text(EndDateTime))) );
@If(@IsAvailable($CSVersion) & @IsAvailable(TaskType); @TextToTime(@Text(DateList)+ " " + @Text(@Time(CalendarDateTime))); AppointmentType = “0”:“1”:“2”:“3”:“4”;CalendarDateTime;@TextToTime(@Text(DateList) + " " + @Text(StartTime)))
Agent Script:
Sub Initialize
Dim Session As NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim xxAppointmentType As Variant
Dim TaskType As Variant
Dim xxLocation As Variant
Dim nam As NotesName
Dim xxChair As Variant
Dim xxRoom As Variant
Dim xxSubject As Variant
Dim ExpValue As Variant
Dim CalEntry As String
Set Session = New NotesSession
Set db = session.CurrentDatabase
Set View = db.GetView("($Calendar)")
Set doc = view.GetFirstDocument
If con.ConnectTo("RetirementPortal_Dev", "sids", "sidsfeed") Then
’ If con.ConnectTo(“RetirementPortal_Staging”, “sids”, “sidsfeed”) Then
’ If con.ConnectTo(“RetirementPortal_Production”, “sids”, “sidsfeed”) Then
Set qry.Connection = con
Do While Not(doc Is Nothing)
TaskType = doc.getItemValue("TaskType")
Set nam = New NotesName(doc.Chair(0))
xxAppointmentType = doc.AppointmentType(0)
xxRoom = doc.Room(0)
xxChair= nam.Common
xxSubject = doc.Subject(0)
If xxAppointmentType<>"3" Then
Goto flag
Else
xxRoom = "Room: " & doc.Room(0)
End If
If xxAppointmentType = "3" Then
xxChair = "Chair: " & xxChair
Elseif TaskType = "2" Then
xxChair = "Owner: " & xxChair
End If
flag:
If Not Isnull(doc.Location) Then
xxLocation = "Location: " & doc.Location(0)
Else
xxLocation = ""
End If
'Messagebox (ExpValue)
' Update Remedy Last Surveyed date field with the date document was saved
qry.SQL = "Insert into CalendarView_Tbl (StartDateTime,EndDateTime,Subject,Room,Location,Chair) values ('" & doc.StartDateTime(0) &"','" & doc.EndDateTime(0) &"','" & xxSubject &"','" & xxRoom &"','" & xxLocation &"','" & xxChair & "')"
'Messagebox (qry.sql)
fileNumber% = Freefile
Set result.Query = qry
If Not result.Execute() Then
On Error Resume Next
End If
Set doc=view.getNextDocument(doc)
Loop
Status = con.Disconnect
Else
Messagebox("Could not connect to server")
End If
End Sub