Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Pulling in info from a outside dataset

Posted on 2002-06-05
16
Medium Priority
?
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 400 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
 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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