Solved

Pulling in info from a outside dataset

Posted on 2002-06-05
16
237 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:pucktarget2
  • 9
  • 7
16 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7056484
Ok, You din't mention what is wrong or what you want ?

~Hemanth
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7056508
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
 

Author Comment

by:pucktarget2
ID: 7056637
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
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7056871
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
 

Author Comment

by:pucktarget2
ID: 7056976
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
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7057203
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
 

Author Comment

by:pucktarget2
ID: 7057256
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
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 100 total points
ID: 7058280
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:pucktarget2
ID: 7059096
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
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7059953
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
 

Author Comment

by:pucktarget2
ID: 7060018
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
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7062353
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
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7062356
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
 

Author Comment

by:pucktarget2
ID: 7062401
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
 

Author Comment

by:pucktarget2
ID: 7062404
Great patience and quick responses from Hemanth once again!!
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 7063638
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

20 Experts available now in Live!

Get 1:1 Help Now