Solved

Looping help

Posted on 2002-06-10
13
203 Views
Last Modified: 2013-12-18
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!!
0
Comment
Question by:pucktarget2
  • 7
  • 6
13 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
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
0
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
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
0
 

Author Comment

by:pucktarget2
Comment Utility
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

0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 100 total points
Comment Utility
Yes you are on right path.

Examples are little vague for begginers, but as time goes you will pick it up real fast.

Let me know the progress
0
 

Author Comment

by:pucktarget2
Comment Utility
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!!
0
 

Author Comment

by:pucktarget2
Comment Utility
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")))
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
You have to declare the objects like this on top

Dim item, colldesc1, colldesc2 as NotesItem
0
 

Author Comment

by:pucktarget2
Comment Utility
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
0
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
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.
0
 

Author Comment

by:pucktarget2
Comment Utility
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
0
 

Author Comment

by:pucktarget2
Comment Utility
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
0
 

Author Comment

by:pucktarget2
Comment Utility
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.

0
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
So what is the problem here ?
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now