Solved

Looping help

Posted on 2002-06-10
13
209 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
ID: 7068324
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
ID: 7068377
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
ID: 7068413
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
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 100 total points
ID: 7068957
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
ID: 7073563
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
ID: 7073564
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
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7073836
You have to declare the objects like this on top

Dim item, colldesc1, colldesc2 as NotesItem
0
 

Author Comment

by:pucktarget2
ID: 7073897
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
ID: 7073927
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
ID: 7090534
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
ID: 7108906
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
ID: 7116225
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
ID: 7125316
So what is the problem here ?
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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