asked on

Extracting names from Embedded view

Have a form which I am using an embedded view setup to log attendees for training sessions, in the past I have hard coded a table of attendees and used the code below to extract all the attendee names and send a letter asking them to take a survey on their training. Is their a way to extract the attendee names from the embedded view for that particular session? Can I tie it to the unique number field which is what I am tying the embedded view together with the record. Basically pull that unique field and look to all the attendee records with the same number and pull that name.

This is the code I used to send the note in the past.
Sub Initialize
      Dim db As NotesDatabase
      Set s = New NotesSession
      Set db = s.CurrentDatabase

      Dim dc As NotesDocumentCollection
      Set dc = db.UnprocessedDocuments
      Dim varNames(49) As String
      Dim docA As NotesDocument
      Dim docB As NotesDocument
      Dim installDate As NotesDateTime
      Dim installTimeItem As NotesItem
      Dim item As NotesItem
      Dim i As Integer

      On Error Resume Next

      Set docA = dc.GetFirstDocument
      For i = 1 To dc.Count
            Set docA = dc.GetNthDocument( i )
            If docA.form(0) = "conf_call_survey" And docA.survey_sent(0) <> "done" Then ' this is the document you're pulling names FROM
                  Set docB = db.CreateDocument
                  docB.form = "feedback_request" ' the document you want to email
                  docB.Subject = "Please take a moment to provide feedback on the " + docA.topic(0) + " conference call you recently attended. "
                  varNames(0) = docA.name(0)
                  varNames(1) = docA.name_1(0)
...................................................................................
                  varNames(49) = docA.name_49(0)
                  Set installTimeItem = docA.GetFirstItem("date")
                  Set installDate = installTimeItem.DateTimeValue
                  Call installDate.SetAnyTime
                  Call docB.ReplaceItemValue("date",installDate)
                  docB.SendTo = varNames
                  docB.CopyTo = docA.trainer
                  docB.topic = docA.topic
                  docB.topic1 = docA.topic
                  docB.trainer = docA.trainer(0)
                  docA.survey_sent = "done"
                  Call docA.Save(True,True)

                  Call docB.Send (True)
            Else
                  message = "You have already sent a survey request for this class. This request will not be sent."
                  Messagebox message, MB_OK, "Alert!!"
            End If
      Next
End Sub

qwaletee

I don't see what this has to do with embedded views. You have a session document, attendee documents, and each attendee documnt includes the session ID as well, correct? You shoudl be able to look up attendees by session from the session document, in script, using a view of attendee documnents sorted by session.

jforget1

ASKER

The code above was from the sending of the notes last year when all attendees were in the record. I am now using embedded views for the sessions and am not sure how to convert the code into the new method.

SysExpert

Set session = New NotesSession
Set db = session.CurrentDatabase
Set dc = db.UnprocessedDocuments
If dc.Count = 0 Then
Msgbox "There are no documents selected",,"Error"
End
else
docA=dc.getFirstDocument
Do until docA is nothing
' here you need to get the key from DocA, open the view for the response docs, and get all the Attendees
Set docA = docs.getNextDocument(doc)
Loop
End If

Bill-Hanson

Sure, the function you are looking for is a member of the NotesView class and is called GetAllDocumentsByKey. You need to lookup the related documents using the same view and key as the embedded view. For example, if your embedded view is named "AttendeesBySessionId" and the category used to display them in the embedded view is the value of the "SessionId" field, then you can get a NotesDocumentCollection of the documents in the embedded view like this...

Dim vwAttendees As NotesView
dim dcAttendees As NotesDocumentCollection
Set vwAttendees = db.GetView("AttendeesBySessionId")
Set dcAttendees = vwAttendees.GetAllDocumentsByKey(docA.GetItemValue("SessionId")(0), True)
If (dcAttendees.Count > 0) Then ...

... Now you can re-dimension the varNames array to just the right size to hold all the names...

Redim varNames(dcAttendees.Count - 1)

... then just loop over the dcAttendees collection and set the value in the array...

Set docAttendee = dcAttendees.GetFirstDocument()
Do Until docAttendee Is Nothing
      varNames(iAttendeeIndex) = docAttendee.GetItemValue("Name")(0)
      iAttendeeIndex = iAttendeeIndex + 1
      Set docAttendee = dcAttendees.GetNextDocument(docAttendee)
Loop

... That should get you started down the right path.

jforget1

ASKER

OK I have tried to incorporate the code into what I had for this agent,

Getting an illegal parenthisized error on VARNAMES.

Sub Initialize
      Dim db As NotesDatabase
      Dim vwAttendees As NotesView
      Dim dcAttendees As NotesDocumentCollection
      Set s = New NotesSession
      Set db = s.CurrentDatabase

      Dim dc As NotesDocumentCollection
      Set dc = db.UnprocessedDocuments
      Dim varNames As String
      Dim docA As NotesDocument
      Dim docB As NotesDocument
      Dim item As NotesItem
      Dim i As Integer
      On Error Resume Next
      Set docA = dc.GetFirstDocument
      For i = 1 To dc.Count
            Set docA = dc.GetNthDocument( i )
            If docA.form(0) = "fall_2007_training_form" Then ' this is the document you're pulling names FROM
                  Set vwAttendees = db.GetView("fall_2007_attendance")
                  Set dcAttendees = vwAttendees.GetAllDocumentsByKey(docA.GetItemValue("uniqueID")(0), True)
                  If (dcAttendees.Count > 0) Then
                        Set docB = db.CreateDocument
                        docB.form = "fall_2007_training_thank_you"
                        docB.Subject = "Life App Redesign Training Thank you!"
                        docB.trainer =docA.trainer
                        Set docAttendee = dcAttendees.GetFirstDocument()
                        Do Until docAttendee Is Nothing
                              varNames(iAttendeeIndex) = docAttendee.GetItemValue("attendance_name")(0)
                              iAttendeeIndex = iAttendeeIndex + 1
                              Set docAttendee = dcAttendees.GetNextDocument(docAttendee)
                        Loop

                        Redim varNames(dcAttendees.Count - 1)

                        docB.SendTo = varNames
                        docB.Principal = "Service Delivery Group"
                        docB.CopyTo = docA.trainer
                        docB.topic = docA.topic
                        docB.topic1 = docA.topic
                        docB.trainer = docA.trainer(0)
                        docA.survey_sent = "done"
                        Call docA.Save(True,True)

                        Call docB.Send (True)
                  Else
                        message = "You have already sent a survey request for this class. This request will not be sent."
                        Messagebox message, MB_OK, "Alert!!"
                  End If
            End If
      Next
End Sub

Bill-Hanson

The problem is that you declared varNames as a string, then attempted to redim it into an array. This is not allowed in LotusScript. Change the declaration of varNames to...

Dim varNames() As String

ASKER CERTIFIED SOLUTION

Bill-Hanson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial