Help with copying from external database

hcl-bot · November 28, 2007, 4:31pm

Hi,

I am very new to Lotus and Lotus scripting so thought I would ask on here for some help. I am basically trying to design an agent that will copy data from documents in one database into new documents in another database. I am sure there are parts of my coding I could be doing ‘better’, and I also specifically need help with one part of the code. Code shown below:

Sub Initialize

Dim s As New NotesSession

Dim s2 As New NotesSession

Dim workspace As NotesUiWorkspace

Dim db As NotesDatabase

Dim currentdb As NotesDatabase

Dim respDoc As NotesDocument

Dim doc As NotesDocument

Dim newdoc As NotesDocument

Dim item As NotesItem

’ Dim AskedDate As NotesDateTime

Dim view As NotesView

Dim FinalQNo(100) As String

Dim Sitting(100) As String

Dim Session(100) As String

Dim Party(100) As String

Dim House(100) As String

Dim AskedBy(100) As String

Dim AddressedTo(100) As String

Dim AskedDate(100) As String



Set db = s.GetDatabase("nDPCTest00", "parlimentstagingstaging")

Set view = db.GetView( "Questions" )

Set doc = view.GetFirstDocument	



x = 0

’ While Not(doc Is Nothing)

’ Forall subject In Doc.GetItemValue(“Sitting”)

’ message(x) = subject

’ End Forall

’

’ Forall subject2 In doc.GetItemValue(“FinalQNo”)

’ message2(x) = subject2

’ End Forall

’

’ Messagebox message(x) + " " + message2(x)

’ Set doc = view.GetNextDocument(doc)

’ x = x+1

’ Wend

While Not(doc Is Nothing)

	Forall whatever In doc.GetItemValueDateTimeArray("QAskedDate")

		AskedDate(x) = whatever

	End Forall

	

	FinalQNo(x) = doc.FinalQNo(0)

	Sitting(x) = doc.Sitting(0)

	Session(x) = doc.Session(0)

	Party(x) = doc.Party(0)

	House(x) = doc.House(0)

	AskedBy(x) = doc.SingleWeb(0)

	AddressedTo(x) = doc.MultipleWeb(0)

’ times = doc.GetItemValueDateTimeArray(“QAskedDate”)

’ AskedDate(x) = times

’ Set item = doc.GetFirsItem(“QAskedDate”)

’ Set AskedDate = item.DateTimeValue

	Messagebox FinalQNo(x) + " " + Sitting(x) + " " + Session(x) + " " + Party(x) + " " + House(x) + " " + AskedBy(x)_

	+ " " + AddressedTo(x) + " " + AskedDate(x)

	Set doc = view.GetNextDocument(doc)

	x = x+1

Wend	





Set Currentdb = s2.CurrentDatabase

’ Set newdoc = currentdb.CreateDocument

Set newdoc = New NotesDocument(CurrentDb)

newdoc.form = "Notes Question Form"

newdoc.SingleWeb = AskedBy(0)

newdoc.MultipleWeb = AddressedTo(0)

newdoc.QAskedDate = AskedDate(0)

newdoc.Session = Session(0)

newdoc.Sitting = Sitting(0)

newdoc.Party = Party(0)

newdoc.House = House(0)

newdoc.FinalQNo = FinalQNo(0)

Call newdoc.Save( True, True )

Messagebox newdoc.QAskedDate(0)

End Sub

I have been playing around quite a bit so please excuse the remmed out code.

The main problem I’m having at the moment is copying the date over. In the original document, the date on the form appears like this - 18/09/2007 - but when you go into the document properties, it appears like this - 18/09/2007 12:00:00 AM ZE10. I have been playing around with date formats etc but just can’t seem to get it to work.

Also, I have declared a number of ‘arrays’ at the top of the code with an array limit of 100 - this is probably not the best way to do it, but seemed to be the only way I could get the data to copy across properly.

Also, eventually eventually I would like to ‘loop’ through many documents, copying the data into new forms one by one, so that is the reason I was using arrays and will be the next step I look at.

Cheers and thanks in advance.

hcl-bot · November 28, 2007, 8:25pm

Subject: Help with copying from external database

Try this:

If you are trying to copy the entire document from one database to another:

Dim session As New NotesSession

Dim currentdb As NotesDatabase

Set currentdb = session.CurrentDatabase



Dim db2 As New NotesDatabase("Server/Name", "File\Path\database.nsf")



Dim FromView As NotesView

Set FromView = currentdb.GetView("ViewName")



Dim SourceDoc As NotesDocument

Set SourceDoc = FromView.GetFirstDocument()



Dim destdoc As NotesDocument



While Not (SourceDoc Is Nothing)

	Set destdoc = db2.CreateDocument()

	Call SourceDoc.CopyAllItems(destdoc, True)

	Call destdoc.Save(True, False, True)

	Set SourceDoc = FromView.GetNextDocument(SourceDoc)

Wend

IF you are trying to copy one specific items, add this line before the ‘while’

Dim nItem as NotesItem

and replace this line:

Call SourceDoc.CopyAllItems(destdoc, True)

with these:

Set nItem = SourceDoc.GetFirstItem(“ItemName”)

Call nItem.CopyItemToDocument(destdoc, nItem.Name)

Set nItem = SourceDoc.GetFirstItem(“Item2Name”)

Call nItem.CopyItemToDocument(destdoc, nItem.Name)

etc …

jeremy

http://www.zetaone.com/jeremy/hodgebloge.nsf

hcl-bot · November 28, 2007, 9:02pm

Subject: RE: Help with copying from external database

wow, thanks so much, that is much easier. I knew there would have been better ways to write the code, I just wasn’t sure how.

I have changed the code a little because the source and destination documents/databases were the wrong way around.

I have one other question - I only want to copy fields from those documents that have a status of “Staging” and then set the status of those documents to “Complete”, so that they won’t get copied again next time.

I have added in the If statement which works fine, but wasn’t sure how to then set the source documents that I have copied from to a status of “Complete”. Here is my code as it stands now:

Sub Initialize

Dim session As New NotesSession

Dim currentdb As NotesDatabase

Dim db2 As New NotesDatabase("nDPCTest00", "parlimentstagingstaging")

Dim FromView As NotesView

Dim SourceDoc As NotesDocument

Dim destdoc As NotesDocument

Dim nItem As NotesItem



Set currentdb = session.CurrentDatabase

Set FromView = db2.GetView("Questions")

Set SourceDoc = FromView.GetFirstDocument()



While Not (SourceDoc Is Nothing)

	If sourcedoc.Status(0) = "Staging" Then

		Set destdoc = currentdb.CreateDocument()

		destdoc.form = "QuestionForm"

		

		Set nItem = SourceDoc.GetFirstItem("Session")

		Call nItem.CopyItemToDocument(destdoc, nItem.Name)

		

		Set nItem = SourceDoc.GetFirstItem("Sitting")

		Call nItem.CopyItemToDocument(destdoc, nItem.Name)

		

		Set nItem = SourceDoc.GetFirstItem("Party")

		Call nItem.CopyItemToDocument(destdoc, nItem.Name)

		

		Set nItem = SourceDoc.GetFirstItem("House")

		Call nItem.CopyItemToDocument(destdoc, nItem.Name)

		

		Set nItem = SourceDoc.GetFirstItem("FinalQNo")

		Call nItem.CopyItemToDocument(destdoc, nItem.Name)

		

		Set nItem = SourceDoc.GetFirstItem("FinalQSubNo")

		Call nItem.CopyItemToDocument(destdoc, nItem.Name)

		

		Set nItem = SourceDoc.GetFirstItem("QPrefNo")

		Call nItem.CopyItemToDocument(destdoc, nItem.Name)

		

		Set nItem = SourceDoc.GetFirstItem("SingleWeb")

		Call nItem.CopyItemToDocument(destdoc, nItem.Name)

		

		Set nItem = SourceDoc.GetFirstItem("MultipleWeb")

		Call nItem.CopyItemToDocument(destdoc, nItem.Name)

		

		Set nItem = SourceDoc.GetFirstItem("QAskedDate")

		Call nItem.CopyItemToDocument(destdoc, nItem.Name)

		

		Call destdoc.Save(True, False, True)

		

	End If

	SourceDoc.Status(0) = "Complete"

	Set SourceDoc = FromView.GetNextDocument(SourceDoc)

Wend

End Sub

hcl-bot · November 28, 2007, 10:45pm

Subject: RE: Help with copying from external database

Change

SourceDoc.Status(0) = “Complete”

to

SourceDoc.Status = “Complete”

SourceDoc.Save(True, False, True)

and you’ll be all set …

If performance is an issue, I’d look at creating a new Source view that contains only documents that have Status = “Staging”, add the line

Dim nDoc as NotesDocument

before the while Not (…) and finish off the sub like this:

SourceDoc.Status = “Complete”

Set nDoc = FromView.GetNextDocument(SourceDoc)

Call SourceDoc.Save(True, False, True)

Set SourceDoc = nDoc

Wend

End Sub

jeremy

http://www.zetaone.com/jeremy/hodgebloge.nsf