I want to query two views with an agent (Java or Lotusscript) in the one database.
View1 has 2 columns empid, email (from Lotus Notes)
View2 has 2 columns empid, email (retrieved from Oracle)
I want to be able to overwrite the email field in column 2(View2) with the correct one from view1.
I want to hold this information(correct empid and email) in a new view and new form.
Can someone tell me the best way to go about this. Any code would be appreciated?
Thankyou.
Subject: Compare two views and create new one with new info.
I am a bit confused about why you would want to create yet another new document in this database. Based on your comments, it appears that you already have two documents for each empid, namely, one containing a Notes email address and one containing an Oracle email address.
Do you have two different forms you are using? Do you have a flag of some sort that indicates which document contains which information?
Let’s say you traverse View1 and create a List of all empid values (as the list tag) and set the List item value to be the email address. Then, you traverse View2 to determine whether that empid already exists in the View1 List. If the empid from the View2 document does exist in the View1 List, then you really have nothing to do since a document, with the Notes email address already exists (but here, you could create yet another new document if you wish).
Suppose you come to a document from View2 with an empid that does not exist in the View1 List – now, you have nothing to do because there is not Notes email address to be used.
Again, I am confused by what you really need to accomplish here. Do you just need to get rid of the documents from View2 if they already exist in View1?
Please clarify what you really need and we can probably help you.
Coni Loving
Subject: RE: Compare two views and create new one with new info.
I want to create another new document\View as I going to use it as a basis for a report I’ll run nightly.I do have two different forms ‘NotesPerson’ and ‘IFSperson’.
The purpose is to overwrite the IFSPerson email field with the NotesPerson email fielid based on the unique flaq\key ‘empid’.
FYI… I plan to take this new information and update our ERP(Oracle) system accordingly with a Java Agent.
Code would really be great. Thank you
I
Subject: RE: Compare two views and create new one with new info.
So what exactly do you want to end up with? There are 3 possibilities:
For each document
NotesPerson doc empid = IFSPerson doc empid
NotesPerson doc empid does not exist in IFSPerson docs
Are you a programmer? I am asking because while I do not mind helping someone with their code, I don’t usually just write code for someone who has not even given it a try.
If I have some time tomorrow, I will give you some rather high-level code to get you started (if that is what you need).
Subject: RE: Compare two views and create new one with new info.
CODE below Coni…Thanks.
For each document
NotesPerson doc empid = IFSPerson doc empid
NotesPerson doc empid does not exist in IFSPerson docs
My code so far (still in the making!)… I built a notesDocumentcollection based on the employee id. I think I want to use this to do a look up on the other view, and where the email address differs create a new document…
I havn’t been programming solidly for while!
Sub Initialize
Dim s As New notessession
Dim db As notesdatabase
Set db = s.currentdatabase
Dim defcollect As NotesDocumentCollection
Dim view As notesview
Dim defview As notesview
Dim doc As notesdocument
Dim defdoc As notesdocument
Dim ifsdoc As NotesDocument
Dim agentLog As New NotesLog("Agent log")
Call agentLog.OpenAgentLog
Set view = db.getview("IFS_Notes_Person")
Set defview = db.getview("IFSPerson")
Set doc = view.getfirstdocument
While Not (doc Is Nothing)
Set defcollect = defview.GetalldocumentsByKey(doc.employeeid(0)) '===> from the first lookup
Set defdoc = defcollect.getfirstdocument
While Not (defdoc Is Nothing)
agentLog.LogAction("found: "+defdoc.qNum(0))
'Don't bother overwriting existing document(IFSPerson) - create new document and update the fields accordingly
Set newDoc = New NotesDocument( db )
Call newDoc.ReplaceItemValue("Form", "IFSemail")
newDoc.ComputeWithForm False, False
Call newDoc.ReplaceItemValue("IFSemployeeid", employeeid)
Call newDoc.ReplaceItemValue("IFSemail", email)
Set defdoc = defcollect.getnextdocument(defdoc)
Wend
Set doc = view.getnextdocument(doc)
Wend
End Sub
Subject: RE: Compare two views and create new one with new info.
New code with updates…
I built a notescollection instead of the list(tag, value) that you suggested… This code works, but I probably only want to update those email addresses that differ now. What do you think?
Sub Initialize
Dim s As New notessession
Dim db As notesdatabase
Set db = s.currentdatabase
Dim defcollect As NotesDocumentCollection
Dim view As notesview
Dim defview As notesview
Dim doc As notesdocument
Dim defdoc As notesdocument
Dim ifsdoc As NotesDocument
Dim agentLog As New NotesLog("Agent log")
Call agentLog.OpenAgentLog
Set view = db.getview("IFSNotesPerson")
Set defview = db.getview("IFSPerson")
Set doc = view.getfirstdocument
While Not (doc Is Nothing)
Set defcollect = defview.GetalldocumentsByKey(doc.EmployeeID(0)) '===> from the first lookup
Set defdoc = defcollect.getfirstdocument
While Not (defdoc Is Nothing)
agentLog.LogAction("found: "+defdoc.qNum(0))
'Don't bother overwriting existing document(IFSPerson) - create new document and update the fields accordingly
Set newDoc = New NotesDocument( db )
Call newDoc.ReplaceItemValue("Form", "IFSemail")
newDoc.ComputeWithForm False, False
Call newDoc.ReplaceItemValue("IFSemployeeid", doc.EmployeeID)
Call newDoc.ReplaceItemValue("IFSemail", doc.InternetAddress)
Call newDoc.ReplaceItemValue("Fname", doc.FirstName)
Call newDoc.ReplaceItemValue("Sname", doc.LastName)
newDoc.Save True, True
Set defdoc = defcollect.getnextdocument(defdoc)
Wend
Set doc = view.getnextdocument(doc)
Wend
End Sub