Pulling in info from a outside dataset

Good morning all.

I am trying to pull information from an ODBC dataset into Lotus Notes.  Eventually this info will transfer into a form.  I built the code below and when I ran a test, it stated that it was going to run on 9812 documents.  Good thing I ran a test.  Any help would be appreciated.  I tried to build this from scratch hoping I could teach myself a little bit about Lotus Notes Script.

Thanks

Option Public
Uselsx "*lsxodbc"

Sub Initialize
     Dim Con As New ODBCConnection
     Dim qry As New ODBCQuery
     Dim result As New ODBCResultSet
     Dim msg As String
     Dim BankNumber As String
     Dim Obligor As String
     Dim Obligation As String
     Dim CustName As String
     Dim CollType As String
     Dim Item As String
     Dim CollDesc1 As String
     Dim CollDesc2 As String
     Dim PrePay As String
     Dim EMailAdd As String
     Dim GoodtoSend As String
     Dim OrigDate As String
     
     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
          Do
               result.NextRow
               BankNumber = result.GetValue("BANK", BankNumber)
               Obligor= result.GetValue("OBLIGOR",Obligor)
               Obligation=result.GetValue("OBLIGATION",Obligation)
               CustName=result.GetValue("CUSTNAME",CustName)
               CollType=result.GetValue("COLLTYPE",CollType)
               Item=result.GetValue("ITEM",Item)
               CollDesc1=result.GetValue("FirstoFCOLLDESC1",CollDesc1)
               CollDesc2=result.GetValue("FirstoFCOLLDESC2",CollDesc2)
               EMailAdd=result.GetValue("txt_EMAILADD",EMailAdd)
               
          Loop Until result.IsEndofData
          result.close(DB_CLOSE)
     Else
          Messagebox "There were no OARFs available for distribution from the previous date."
          Exit Sub
     End If
     con.disconnect
     
End Sub
pucktarget2Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
HemanthaKumarConnect With a Mentor Commented:
We use NotesUIDocument to represent the currently composed document( or Form) and backend representation is NotesDocument.

You don't have to declare each field name. Basically you have to design a form with required fields and name it accordingly, then you code in a action/button or an event to execute certain tasks. In your case the odbc code will go into a button or action in a form.

You can use the same code with modifications that I suggested to pull values into the fields like this

' After all odbc object calls, when the resulset is available

    If result.IsResultSetAvailable Then
             Dim ws as New NotesUIWorkspace
             result.FirstRow ' In this case we assume that firstrow has all the values that needs to be put into this document
             set doc = ws.CurrentDocument.Document
             doc.BankNumber = result.GetValue("BANK")
             doc.Obligor= result.GetValue("OBLIGOR")
             doc.Obligation=result.GetValue("OBLIGATION")
             doc.CustName=result.GetValue("CUSTNAME")
             doc.CollType=result.GetValue("COLLTYPE")
             doc.Item=result.GetValue("ITEM")
             doc.CollDesc1=result.GetValue("FirstoFCOLLDESC1")
             doc.CollDesc2=result.GetValue("FirstoFCOLLDESC2")
             doc.EMailAdd=result.GetValue("txt_EMAILADD")
             doc.Save(true,false)
    End if

Hope you are still with me... Just do this and let me know what you see
0
 
HemanthaKumarCommented:
Ok, You din't mention what is wrong or what you want ?

~Hemanth
0
 
HemanthaKumarCommented:
So I assume that you want a resultset to go into a document

then use this code sample (assuming that you are running this code on a uidocument)


Dim ws as New NotesUIWOrkspace
Dim uidoc as NotesUIDOcument
dim note as NotesDocument
set uidoc = ws.CurrentDocument
set note = uidoc.Document

' Get the result set

' You set the values into the document like this
note.BankNumber = result.GetValue("BANK")
' So on ....

Since you said the agent said that it has run on some 9000 documents, I believe you are running the agent on all documents in db or view ... Is that what you intend to do ???

So be little clear about your requirements

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
pucktarget2Author Commented:
Sorry about the confusion.  My intention is to run the agent every morning.  I do not want it to run on any documents, either in db or view, but I can't seem to get that to turn off.  I just want this to run every morning eventually.  After being pulled in I want the info to go into a document or table so I can query on it and poplulate a form.

Hope this explains this better.  Can provide more if needed.

pucktarget
0
 
HemanthaKumarCommented:
To run only once, open the agent and change the parameter to "Run Once" in Which document should it act on combobox.

I don't know why you want to download the data in documents and then use it in some other form. Rather than that you can directly query the db and import the relvant data into the form. So that you get current data, which is a plus.

But still if you intend to do the bulk import into your notes db then add this in do loop section

Declare this variables on top of the script
Dim session as New NotesSession
dim db as NotesDatabase
dim doc as NotesDocument
set db = session.CurrentDatabase


and your do..loop will become

         Do
              result.NextRow
              set doc = db.CreateDocument
              doc.Form = "Download"
              doc.BankNumber = result.GetValue("BANK")
              doc.Obligor= result.GetValue("OBLIGOR")
              doc.Obligation=result.GetValue("OBLIGATION")
              doc.CustName=result.GetValue("CUSTNAME")
              doc.CollType=result.GetValue("COLLTYPE")
              doc.Item=result.GetValue("ITEM")
              doc.CollDesc1=result.GetValue("FirstoFCOLLDESC1")
              doc.CollDesc2=result.GetValue("FirstoFCOLLDESC2")
              doc.EMailAdd=result.GetValue("txt_EMAILADD")
              doc.Save(true,false)
         Loop Until result.IsEndofData
         

=========

The do loop will create a document called download and will have all the data from the db. THen use it in forms by building appropriate views

PS: Download is a pseudo form name, when you try to open, it will give an error.

0
 
pucktarget2Author Commented:
Directly querying the db into the form I want to populate would work for this purpose.  I am not set on bringing the whole db in.  I used the Lotus Notes design help to find and adjust the code above.  I wanted to at least have some code down before I went and posted a question.  Just trying to teach myself. But if there is a better method, I am definitely open to it.
0
 
HemanthaKumarCommented:
Most of us use the direct query, so that it saves time and instantaneous too. Secondly, there is no long connecting time and hold it for long hours unnecessarily.

If you are using some lookup list values in a listbox then making a instant connection is too much, so we try to download the data periodically into the notes db so that we can save the odbc traffic.

I really admire the nature of your self motivation.

So what are we exactly doing here ? To choose the path, and I prefer direct querying .
0
 
pucktarget2Author Commented:
Ok.  Direct Query it is.  Is that an easy change from the code I have here presently?  I am guessing I will have to declare the form (Dim Form as NotesForm) and set the form (Set Form="Automated_OARF"), and then copy the info from the dataset into the fields on the form.  Do I declare the fields I want to copy into on the form as Fields or as Strings?

Thanks for your patience.
0
 
pucktarget2Author Commented:
I appreciate all your help.  This is what I built combining your enhancements along with some of my original attempt.  Here's a stupid question.  How to I test it?  It is in the action section of the form itself.

Thanks!!

Sub Initialize
     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
          Set uidoc = ws.CurrentDocument
          Set note = uidoc.Document
          result.FirstRow
          Set doc = ws.CurrentDocument.Document
          doc.BankNumber = result.GetValue("BANK")
          doc.Obligor= result.GetValue("OBLIGOR")
          doc.Obligation=result.GetValue("OBLIGATION")
          doc.CustName=result.GetValue("CUSTNAME")
          doc.CollType=result.GetValue("COLLTYPE")
          doc.Item=result.GetValue("ITEM")
          doc.CollDesc1=result.GetValue("FirstoFCOLLDESC1")
          doc.CollDesc2=result.GetValue("FirstoFCOLLDESC2")
          doc.EMailAdd=result.GetValue("txt_EMAILADD")
          doc.Save(True)
     End If
     
End Sub
0
 
HemanthaKumarCommented:
First, replace note with doc in your script by using replace tool.

Testing, After running this script look for fields  like BankNumber and other doc.Fields that you set in the script through document properties interface ( file..Document Properties), and click second tab to see list of fields and find the one you created via this script

PS: doc.EMailAdd, means that you create or assign the field with a certain value
0
 
pucktarget2Author Commented:
Ok.  I got it to test and I got it to work.  I had to do a little code adjusting but it does scrape the first record into the form like I want.  Now I need to work on some evals and looping.  

Quick question:  After scraping the firs record, I want it to go to the next record and evaluate the Obligor, Obligation and Item number fields.  If all three are the same, I want it to go to the next record, if the obligor and obligation are the same but the item number is different, I want it to scrape the item, colltype and two description fields.  Once it finds a new obligor and obligation, I want it to send out the e-mail and prepare a new form, then start with the next record the process above.  I am not asking you to build the code of course, but I would like to know if it is possible.

Thanks!!  I will accept your answer and give you the points soon!!

Pucktarget
0
 
HemanthaKumarCommented:
Yes it is possible, you just loop through the resultset and get each column value into a temporary var and compare, then send mail and compose another document.

0
 
HemanthaKumarCommented:
Yes it is possible, you just loop through the resultset and get each column value into a temporary var and compare, then send mail and compose another document.

0
 
pucktarget2Author Commented:
Great!!

I will try to build some looping code for what I want it to do.  If I have any problems, I will post another question when the time comes.

Thanks!

Pucktarget
0
 
pucktarget2Author Commented:
Great patience and quick responses from Hemanth once again!!
0
 
HemanthaKumarCommented:
Thanks
0
All Courses

From novice to tech pro — start learning today.