asked on

Looping help

Can anyone point me in the right direction, I am fairly new to Lotus Script.

I am pulling fields through an ODBC connection into a database to fill a form. I built the following code to do that. This is in the postopen event of a form in the notes database and works great at pulling the first record and populating the appropriate fields.

Sub Postopen(Source As Notesuidocument)
Dim uidoc As NotesUIDOcument
Dim note As NotesDocument
Dim Con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet

Set qry.Connection=con
Set result.query=qry
con.ConnectTo("OARFAutomation")

qry.SQL= "SELECT tblMasterOARFControl.BANK," _
& "tblMasterOARFControl.OBLIGOR, tblMasterOARFControl.OBLIGATION, " _
& "tblMasterOARFControl.CUSTNAME, tblMasterOARFControl.COLLTYPE, " _
& "tblMasterOARFControl.MATDATE, tblMasterOARFControl.ITEM, " _
& "tblMasterOARFControl.FirstOfCOLLDESC1, tblMasterOARFControl.FirstOfCOLLDESC2," _
& "tblMasterOARFControl.OFFCRCODE, tblMasterOARFControl.PREPAY, " _
& "tblMasterOARFControl.txt_EMAILADD, tblMasterOARFControl.txtGoodtoSend, " _
& "tblMasterOARFControl.txtOrigSendDate " _
& "FROM tblMasterOARFControl WHERE (((tblMasterOARFControl.txtGoodtoSend)=-1));"
result.execute

If result.IsResultSetAvailable Then
Dim ws As New NotesUIWOrkspace
Dim Obligor As String
Dim Obligation As String
Dim Item As String
Set uidoc = ws.CurrentDocument
Set note = uidoc.Document
result.FirstRow
Set doc = ws.CurrentDocument.Document

Call Source.FieldSetText("txtBankNum",result.GetValue("BANK"))
Call Source.FieldSetText("txtObligor",result.GetValue("OBLIGOR"))
Call Source.FieldSetText("txtObligation",result.GetValue("OBLIGATION"))
Call Source.FieldSetText("txtCustomerName",result.GetValue("CUSTNAME"))
Call Source.FieldSetText("txtCollateralType",result.GetValue("COLLTYPE"))
Call Source.FieldSetText("txtItemNum",Str(result.GetValue("ITEM")))
Call Source.FieldSetText("txtCollDesc1",result.GetValue("FirstofCOLLDESC1"))
Call Source.FieldSetText("txtCollDesc2",result.GetValue("FirstofCOLLDESC2"))
Call Source.FieldSetText("SendTo",result.GetValue("txt_EMAILADD"))
'doc.Save(True)
End If

End Sub

Now the fun part, I need it to move to the next row and evaluate the Obligor, Obligation fields on the info that was pulled in. If the two are equal, I need to to evaluate the Item field, if it is the same, skip the record. If it is different, then scrape the new item #, new colldesc1 and colldesc2 into those fields (which are built to allow multiple values). If the Obligor is different, it will close and send the doc to the addressee (which is scraped in). Then I need to to open another doc and start all over.

I tried to use Lotus Help and a couple of web sites, but I could not find any code that would help. Thanks all!!

HemanthaKumar

obligor = result.GetValue("OBLIGOR")
obligation = result.GetValue("OBLIGATION")
If obligor != obligation then
' Evaluate item
else
' New Item#, new colldesc1, colldesc2 inserted
set item = doc.getFirstItem("txtItemNum")
set colldesc1 = doc.getFirstItem("txtcolldesc1")
set colldesc2 = doc.getFirstItem("txtcolldesc2")

call item.appendTOTextList( Str(result.GetValue("ITEM")) )

call colldesc1.appendTOTextList( result.GetValue("FirstofCOLLDESC1"))

call colldesc2.appendTOTextList( result.GetValue("FirstofCOLLDESC2"))
SendDoc = True
End If

' Insert this at the end of the sub i.e after resulset endif
If SendDoc then
Recipients = "Recipient Name"
Call doc.Send( true, Recipients)
End if

~Hemanth

HemanthaKumar

Forgot to tell you where the I part of the script goes

You have to replace these lines with I part
Call Source.FieldSetText("txtItemNum",Str(result.GetValue("ITEM")))
Call Source.FieldSetText("txtCollDesc1",result.GetValue("FirstofCOLLDESC1"))
Call Source.FieldSetText("txtCollDesc2",result.GetValue("FirstofCOLLDESC2"))

II part will come after last end if statement

Hope I was clear

pucktarget2

ASKER

So this code:

obligor = result.GetValue("OBLIGOR")
obligation = result.GetValue("OBLIGATION")
If obligor != obligation then
' Evaluate item
else
' New Item#, new colldesc1, colldesc2 inserted
set item = doc.getFirstItem("txtItemNum")
set colldesc1 = doc.getFirstItem("txtcolldesc1")
set colldesc2 = doc.getFirstItem("txtcolldesc2")

call item.appendTOTextList( Str(result.GetValue("ITEM")) )

call colldesc1.appendTOTextList( result.GetValue("FirstofCOLLDESC1"))

call colldesc2.appendTOTextList( result.GetValue("FirstofCOLLDESC2"))
SendDoc = True
End If

Takes the place of this already existing code:

Call Source.FieldSetText("txtItemNum",Str(result.GetValue("ITEM")))
Call Source.FieldSetText("txtCollDesc1",result.GetValue("FirstofCOLLDESC1"))
Call Source.FieldSetText("txtCollDesc2",result.GetValue("FirstofCOLLDESC2"))

And the last part goes after the last end if. Is that correct?? Thanks for the help again. I tried Lotus Help to build it myself but the examples were too vague.

Puck

ASKER CERTIFIED SOLUTION

HemanthaKumar

membership

This solution is only available to members.

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

Start Free Trial

pucktarget2

ASKER

Hey Hamanth, Here is what I have so far. But I am getting a error message on the highlighted item below. The debugger gives a "Not an instance name error message.

Do
Call Source.FieldSetText("txtBankNum",result.GetValue("BANK"))
Call Source.FieldSetText("txtRM",result.GetValue("OFFCRCODE"))
Call Source.FieldSetText("txtPrepayCode",result.GetValue("PREPAY"))
Call Source.FieldSetText("txtCustomerName",result.GetValue("CUSTNAME"))
Call Source.FieldSetText("txtCollateralType",result.GetValue("COLLTYPE"))
Call Source.FieldSetText("txtObligor",result.GetValue("OBLIGOR"))
Call Source.FieldSetText("txtObligation",result.GetValue("OBLIGATION"))
Call Source.FieldSetText("SendTo",result.GetValue("txt_EMAILADD"))
obligor = result.GetValue("OBLIGOR")
obligation = result.GetValue("OBLIGATION")
If obligor = obligation Then
' Evaluate item
Else
' New Item#, new colldesc1, colldesc2 inserted
Set item = doc.getFirstItem("txtItemNum")
Set colldesc1 = doc.getFirstItem("txtcolldesc1")
Set colldesc2 = doc.getFirstItem("txtcolldesc2")

Call item.appendTOTextList(Str(result.GetValue("ITEM")))

Call colldesc1.appendTOTextList( result.GetValue("FirstofCOLLDESC1"))

Call colldesc2.appendTOTextList( result.GetValue("FirstofCOLLDESC2"))
SendDoc = True
End If

result.nextrow
Loop

End If
' Insert this at the end of the sub i.e after resulset endif
If SendDoc Then
Recipients = "Recipient Name"
Call doc.Send( True, Recipients)
End If

Thanks!!

pucktarget2

ASKER

Would not allow me to highlight: Here is the line of code that is giving me a problem so far:

Call item.appendTOTextList(Str(result.GetValue("ITEM")))

HemanthaKumar

You have to declare the objects like this on top

Dim item, colldesc1, colldesc2 as NotesItem

pucktarget2

ASKER

Shoulda figured that. Doh!

Will this loop through the entire resultset? I did not notice any looping "do" function in the code you gave. Is it not needed in this instance?

puck

HemanthaKumar

yes it should be in a do loop, I just provided you a chunk of it.. Sorry I am really out of time. So will respond to you later in detail.

pucktarget2

ASKER

Hey Hemanth...I have not forgotten about you. We are short people this week and are in the midst of an upgrade to Windows 2000. I have been assisting with issues caused as a result of the switch. I will be back later this week or early next.

Until then
Puck

pucktarget2

ASKER

Hey Hemanth. I am back. Finally got through the woods after last week and I have been able to do some more work on my code above. Here is what I have right now. I moved the function out of the post open section of the form and into an agent.

Sub Initialize
      Dim s As New notessession
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim Con As New ODBCConnection
      Dim qry As New ODBCQuery
      Dim result As New ODBCResultSet
      Dim item, colldesc1, colldesc2, Obligation, CollType As NotesItem

      Set db=s.currentdatabase
      Set qry.Connection=con
      Set result.query=qry
      con.ConnectTo("OARFAutomation")

      qry.SQL= "SELECT tblMaster.BANK, tblMaster.OBLIGOR, tblMaster.OBLIGATION, tblMaster.CUSTNAME, " _
      & "tblMaster.PROCTYPE, tblMaster.COLLTYPE, tblMaster.MATDATE, tblMaster.ITEM, " _
      & "tblMaster.FirstOfCOLLDESC1, tblMaster.FirstOfCOLLDESC2, tblMaster.OFFCRCODE, " _
      & "tblMaster.PREPAY, tblMaster.txt_EMAILADD, tblMaster.txtReadyToSendInd " _
      & "FROM tblMaster " _
      & "GROUP BY tblMaster.BANK, tblMaster.OBLIGOR, tblMaster.OBLIGATION, tblMaster.CUSTNAME, " _
      & "tblMaster.PROCTYPE, tblMaster.COLLTYPE, tblMaster.MATDATE, tblMaster.ITEM, " _
      & "tblMaster.FirstOfCOLLDESC1, tblMaster.FirstOfCOLLDESC2, tblMaster.OFFCRCODE, " _
      & "tblMaster.PREPAY, tblMaster.txt_EMAILADD, tblMaster.txtReadyToSendInd " _
      & "HAVING (((tblMaster.txtReadyToSendInd)=-1));"
      result.execute

      If result.IsResultSetAvailable Then

            result.FirstRow

            Do

                  Set doc=db.CreateDocument

                  doc.SendTo=result.GetValue("txt_EMAILADD")
                  doc.txtBankNum=result.GetValue("BANK")
                  doc.txtObligor=result.GetValue("OBLIGOR")
                  doc.txtObligation=result.GetValue("OBLIGATION")
                  doc.txtCustomerName=result.GetValue("CUSTNAME")
                  doc.txtCollateralType=result.GetValue("COLLTYPE")
                  doc.txtNoteDate=result.GetValue("MATDATE")
                  doc.txtPrepayCode=result.GetValue("PREPAY")
                  doc.txtRM=result.GetValue("OFFCRCODE")
                  doc.txtItemNum=Str(result.GetValue("ITEM"))
                  doc.txtCollDesc1=result.GetValue("FirstofCOLLDESC1")
                  doc.txtCollDesc2=result.GetValue("FirstofCOLLDESC2")
NextRow:

                  result.nextrow
                  If (Obligor=result.GetValue("OBLIGOR")) And (Obligation=result.GetValue("OBLIGATION")) And (Item=Str(result.GetValue("ITEM"))) Then
                        Goto NextRow
                  End If

                  If (Obligor=result.GetValue("OBLIGOR")) And (Obligation=result.GetValue("OBLIGATION")) And (Item<>Str(result.GetValue("ITEM"))) Then
                        Set item=doc.GetFirstItem("txtItemNum")
                        Set colldesc1 = doc.getFirstItem("txtcolldesc1")
                        Set colldesc2 = doc.getFirstItem("txtcolldesc2")
                        Set CollType = doc.getFirstItem("COLLTYPE")
                        Call item.appendTOTextList( Str(result.GetValue("ITEM")) )
                        Call colldesc1.appendTOTextList( result.GetValue("FirstofCOLLDESC1"))
                        Call colldesc2.appendTOTextList( result.GetValue("FirstofCOLLDESC2"))
                        Call CollType.appendTOTextList( result.GetValue("COLLTYPE"))
                        Goto NextRow
                  Else
                        If (Obligor=result.GetValue("OBLIGOR")) And (Obligation<>result.GetValue("OBLIGATION")) Then
                              Set item=doc.GetFirstItem("txtItemNum")
                              Set colldesc1 = doc.getFirstItem("txtcolldesc1")
                              Set colldesc2 = doc.getFirstItem("txtcolldesc2")
                              Set CollType = doc.getFirstItem("COLLTYPE")
                              Set Obligation = doc.GetFirstItem("OBLIGATION")
                              Call item.appendTOTextList( Str(result.GetValue("ITEM")) )
                              Call colldesc1.appendTOTextList( result.GetValue("FirstofCOLLDESC1"))
                              Call colldesc2.appendTOTextList( result.GetValue("FirstofCOLLDESC2"))
                              Call CollType.appendTOTextList( result.GetValue("COLLTYPE"))
                              Call Obligation.appendTOTextList( result.GetValue("OBLIGATION"))
                              Goto NextRow
                        Else
                              If (Obligor<>result.GetValue("OBLIGOR")) Then

                                    doc.Form = "Automated_OARF"
                                    doc.FormName ="Automated_OARF"
                                    Call doc.Save(True, True)

                              End If
                        End If
                  End If
            Loop Until EndofResultSet
      End If
End Sub

This new code works relatively well with a few exceptions. I want it keep items with the same obligor number on the same document. Once the obligor changes, it can save and close the form (and send out the notification, which will be a different form with a link to the original). Right now it is looping and creating a new document on each record, regardless of the criteria I built. Also, it gets on the last record, repeating it continuously until I ctrl-alt-delete and end the task.

Thanks for the help again!!

Puck

pucktarget2

ASKER

Ok. One issue solved. I was able to stop the loop by changing the Loop until to "Loop until result.EndofData."

I was close before, but no banana.

HemanthaKumar

So what is the problem here ?