Script - forall and loop

hcl-bot · November 6, 2007, 3:21pm

Can someone show me what I’m doing wrong. I’m having problems with the array part. I want to take each element of the array and look at a view, collect the docs, then email the report. I want to loop through the array until finished. Tmp1-6 are declared in Declarations by the way.

Any suggestions?

Sub Initialize

Dim session As New NotesSession

Dim db As NotesDatabase

Set db = session.CurrentDatabase



Dim rtitem As NotesRichTextItem

Dim ctr As Integer

ctr = 0



Dim DocOne As NotesDocument

Dim NewDoc As NotesDocument



Dim ViewOne As NotesView	

Set ViewOne = db.Getview("Division")

Dim collectionone As NotesDocumentCollection





Dim Division(1 To 4) As String

Division(1) = "North"

Division(2) = "South"

Division(3) = "East"

Division(4) = "West"



Forall V In Division

	

	Set collectionone = viewone.GetAllDocumentsByKey(V,True) 

	

	Set DocOne = collectionone.GetFirstDocument

	Do Until DocOne Is Nothing

		

		Set NewDoc = DB.CreateDocument 

		NewDoc.Form = "FormA"

		NewDoc.sendto = "Scott Price"

		NewDoc.Subject = "New Customers" 	

		NewDoc.Title = "List of new customers."	

		NewDoc.Division = V

		Set rtitem = New NotesRichTextItem(NewDoc, "Link")

		

		tmp1(ctr) =  Format(DocOne.docdate(0),"mm/dd/yyyy")

		tmp2(ctr) =  Left(DocOne.author(0),20)

		tmp3(ctr) =  Left(Strconv(DocOne.companyname(0),3),30)

		tmp4(ctr) =  Left(DocOne.partnumber(0),20)

		tmp5(ctr) =  Format(DocOne.rdate(0),"mm/dd/yyyy")

		tmp6(ctr) =  Format(DocOne.ddate(0),"mm/dd/yyyy")

		Call rtitem.AppendDocLink(DocOne,"Link")

		Call rtitem.AddNewLine(1)

		

		ctr = ctr + 1

		Set DocOne = collectionone.GetNextDocument(DocOne)

		

	Loop

	

End Forall



NewDoc.DocCreated = Fulltrim(tmp1)

NewDoc.DocAuthor = Fulltrim(tmp2)

NewDoc.CompName = Fulltrim(tmp3)

NewDoc.PartNo = Fulltrim(tmp4)

NewDoc.RDate = Fulltrim(tmp5)

NewDoc.DClosed = Fulltrim(tmp6)



    'Clear arrays and send the emails

Call NewDoc.Send(True, NewDoc.SendTo)

Erase tmp1

Erase tmp2

Erase tmp3

Erase tmp4

Erase tmp5

Erase tmp6



Call rtitem.Update

End Sub

hcl-bot · November 6, 2007, 3:35pm

Subject: script - forall and loop

Hi Bill,

A couple things:

You set NewDoc equal to a new document everytime through the loop but you only send it once at the end, after the forall loop is done?
The tmp1-6 variables, what index are they declared with? It seems to me you would have to use the Redim statement everytime through the loop to “grow” these array variables.

hcl-bot · November 6, 2007, 3:48pm

Subject: RE: script - forall and loop

I’m not really sure where to put the create new doc section and not really sure about the array. 1-6 are like the following…Dim tmp1(5000) As String

I might use Redim Preserve but I need to figure out the above…driving me crazy.

hcl-bot · November 6, 2007, 3:52pm

Subject: Try this…

Sub Initialize

Dim session As New NotesSession

Dim db As NotesDatabase

Set db = session.CurrentDatabase

Dim rtitem As NotesRichTextItem

Dim DocOne As NotesDocument

Dim NewDoc As NotesDocument

Dim ViewOne As NotesView

Set ViewOne = db.Getview(“Division”)

Dim collectionone As NotesDocumentCollection

Dim Division(1 To 4) As String

Division(1) = “North”

Division(2) = “South”

Division(3) = “East”

Division(4) = “West”

Forall V In Division

Set collectionone = viewone.GetAllDocumentsByKey(V,True)

Set DocOne = collectionone.GetFirstDocument

If Not DocOne Is Nothing Then

Set NewDoc = DB.CreateDocument

NewDoc.Form = “FormA”

NewDoc.sendto = “Scott Price”

NewDoc.Subject = “New Customers”

NewDoc.Title = “List of new customers.”

NewDoc.Division = V

Set rtitem = New NotesRichTextItem(NewDoc, “Link”)

Do Until DocOne Is Nothing

NewDoc.DocCreated = Format(DocOne.docdate(0),“mm/dd/yyyy”)

NewDoc.DocAuthor = Left(DocOne.author(0),20)

NewDoc.CompName = Left(Strconv(DocOne.companyname(0),3),30)

NewDoc.PartNo = Left(DocOne.partnumber(0),20)

NewDoc.RDate = Format(DocOne.rdate(0),“mm/dd/yyyy”)

NewDoc.DClosed = Format(DocOne.ddate(0),“mm/dd/yyyy”)

Call rtitem.AppendDocLink(DocOne,“Link”)

Call rtitem.AddNewLine(1)

Set DocOne = collectionone.GetNextDocument(DocOne)

Loop

Call NewDoc.Send(True, NewDoc.SendTo)

End If

End Forall

End Sub

hcl-bot · November 6, 2007, 4:38pm

Subject: RE: Try this…

Your solution helped me with the script. Thanks.

hcl-bot · November 6, 2007, 4:41pm

Subject: RE: Try this…

Good - I actually wasn’t sure if there was a reason why this part of the code:

NewDoc.DocCreated = Format(DocOne.docdate(0),“mm/dd/yyyy”)

NewDoc.DocAuthor = Left(DocOne.author(0),20)

NewDoc.CompName = Left(Strconv(DocOne.companyname(0),3),30)

NewDoc.PartNo = Left(DocOne.partnumber(0),20)

NewDoc.RDate = Format(DocOne.rdate(0),“mm/dd/yyyy”)

NewDoc.DClosed = Format(DocOne.ddate(0),“mm/dd/yyyy”)

should have been inside the Do While loop, because each document in the collection will overwrite the values in NewDoc that the previous DocOne had set. Anyway, glad you worked things out.

hcl-bot · November 13, 2007, 9:52pm

Subject: RE: Try this…

Looks like I’m still having problems with this…the document links are only working for the first document in the collection. I’m not sure the datetime section is working correctly either. I want to collect docs 5 days old from today’s date.

Any suggestions on what the problem is? Thank you!!!

Sub Initialize

Dim session As New NotesSession

Dim db As NotesDatabase

Set db = session.CurrentDatabase

Dim rtitem As NotesRichTextItem

Dim ctr As Integer

ctr = 0

Dim DocOne As NotesDocument

Dim NewDoc As NotesDocument

Dim ViewOne As NotesView

Set ViewOne = db.Getview(“view1”)

Dim collectionone As NotesDocumentCollection

Dim Todaydate As NotesDateTime

Dim DocCreatedDate As NotesDateTime

Dim Docitem As NotesItem

Dim Division(1 To 4) As String

Division(1) = “North”

Division(2) = “South”

Division(3) = “East”

Division(4) = “West”

Forall V In Division

Set collectionone = viewone.GetAllDocumentsByKey(V,True)

Set DocOne = collectionone.GetFirstDocument

If Not DocOne Is Nothing Then

Do While Not DocOne Is Nothing

Set Todaydate = New NotesDateTime (Today)

Set Docitem = DocOne.GetFirstItem(“DocDate”)

Set DocCreatedDate = Docitem.DateTimeValue

If Todaydate.TimeDifference(DocCreatedDate) < 6 Then

Set NewDoc = DB.CreateDocument

NewDoc.Form = “Form1”

NewDoc.sendto = DocOne.dispComplaint5(0)

NewDoc.Subject = “Subject goes here…”

NewDoc.Title = “Title goes here…”

NewDoc.Plant = V

NewDoc.Principal = “Person name”

NewDoc.Replyto = “Person name”

Set rtitem = New NotesRichTextItem(NewDoc, “Link”)

tmp1(ctr) = Format(DocOne.docdate(0),“mm/dd/yyyy”)

tmp2(ctr) = Left(DocOne.complaintby(0),20)

tmp3(ctr) = Left(Strconv(DocOne.rmacompanyname(0),3),30)

tmp4(ctr) = Left(DocOne.partnumber(0),20)

tmp5(ctr) = Format(DocOne.qrdate(0),“mm/dd/yyyy”)

tmp6(ctr) = Format(DocOne.ccloseddate(0),“mm/dd/yyyy”)

Call rtitem.AppendDocLink(DocOne,“Link”)

Call rtitem.AddNewLine(1)

ctr = ctr + 1

Set DocOne = collectionone.GetNextDocument(DocOne)

End If

Loop

NewDoc.DocCreated = Fulltrim(tmp1)

NewDoc.DocAuthor = Fulltrim(tmp2)

NewDoc.CompName = Fulltrim(tmp3)

NewDoc.PartNo = Fulltrim(tmp4)

NewDoc.RevDate = Fulltrim(tmp5)

NewDoc.Closed = Fulltrim(tmp6)

'Clear arrays and send the emails

Call NewDoc.Send(True, NewDoc.SendTo)

Erase tmp1

Erase tmp2

Erase tmp3

Erase tmp4

Erase tmp5

Erase tmp6

Call rtitem.Update

End If

End Forall

End Sub

hcl-bot · November 14, 2007, 12:37am

Subject: Try this (again…)

Sub InitializeDim session As New NotesSession

Dim db As NotesDatabase

Set db = session.CurrentDatabase

Dim rtitem As NotesRichTextItem

Dim ctr As Integer

ctr = 0

Dim DocOne As NotesDocument

Dim NewDoc As NotesDocument

Dim ViewOne As NotesView

Set ViewOne = db.Getview(“view1”)

Dim collectionone As NotesDocumentCollection

Dim datToday as Variant

datToday = Today()

%REM

Dim Todaydate As NotesDateTime

Dim DocCreatedDate As NotesDateTime

Dim Docitem As NotesItem

%END REM

Dim Division(1 To 4) As String

Division(1) = “North”

Division(2) = “South”

Division(3) = “East”

Division(4) = “West”

Forall V In Division

Set collectionone = viewone.GetAllDocumentsByKey(V,True)

Set DocOne = collectionone.GetFirstDocument

Set NewDoc = Nothing

Set rtitem = Nothing

ctr = 0 'Do this each time you change Divisions, otherwise you start with ctr = last value in previous Division

If Not DocOne Is Nothing Then

Do While Not DocOne Is Nothing

%REM

Set Todaydate = New NotesDateTime (Today) 'Why get today’s date each time you go through the loop - do you expect the day to change as you are processing?

Set Docitem = DocOne.GetFirstItem(“DocDate”)

Set DocCreatedDate = Docitem.DateTimeValue

If Todaydate.TimeDifference(DocCreatedDate) < 6 Then 'Time difference does not return difference in days

%END REM

If datToday > DocOne.GetItemValue(“DocDate”)(0) + 5 Then 'Use variants instead of NotesDateTime - much simpler

If NewDoc Is Nothing Then

Set NewDoc = DB.CreateDocument

NewDoc.Form = “Form1”

NewDoc.sendto = DocOne.dispComplaint5(0)

NewDoc.Subject = “Subject goes here…”

NewDoc.Title = “Title goes here…”

NewDoc.Plant = V

NewDoc.Principal = “Person name”

NewDoc.Replyto = “Person name”

Set rtitem = New NotesRichTextItem(NewDoc, “Link”)

End If

'###################################################

'What are you trying to do here? Where is tmp1…tmp6 declared? Maybe you are getting an error here

tmp1(ctr) = Format(DocOne.docdate(0),“mm/dd/yyyy”) 'It pains me to see dates converted to text, but whatever…

tmp2(ctr) = Left(DocOne.complaintby(0),20)

tmp3(ctr) = Left(Strconv(DocOne.rmacompanyname(0),3),30)

tmp4(ctr) = Left(DocOne.partnumber(0),20)

tmp5(ctr) = Format(DocOne.qrdate(0),“mm/dd/yyyy”)

tmp6(ctr) = Format(DocOne.ccloseddate(0),“mm/dd/yyyy”) 'It pains me to see dates converted to text, but whatever…

'###################################################

Call rtitem.AppendDocLink(DocOne,“Link”)

Call rtitem.AddNewLine(1)

ctr = ctr + 1 '############### What exactly are you counting? The number of documents in the Division that are more than 5 days old?

Set DocOne = collectionone.GetNextDocument(DocOne)

End If

Loop

NewDoc.DocCreated = Fulltrim(tmp1)

NewDoc.DocAuthor = Fulltrim(tmp2)

NewDoc.CompName = Fulltrim(tmp3)

NewDoc.PartNo = Fulltrim(tmp4)

NewDoc.RevDate = Fulltrim(tmp5)

NewDoc.Closed = Fulltrim(tmp6)

'Clear arrays and send the emails

Call NewDoc.Send(True, NewDoc.SendTo)

Erase tmp1

Erase tmp2

Erase tmp3

Erase tmp4

Erase tmp5

Erase tmp6

Call rtitem.Update

End If

End Forall

End Sub

hcl-bot · November 14, 2007, 8:30am

Subject: RE: Try this (again…)

Cesar,

Thanks for the reply. I haven’t tried your code yet. I wanted to reply to your questions.

I declared the tmp variables in (Declarations) right above initialize.

(I actually have 6 variable in my: Dim Division(1 To 6) As String)

Dim tmp1(5000) As String

Dim tmp2(5000) As String

Dim tmp3(5000) As String

Dim tmp4(5000) As String

Dim tmp5(5000) As String

Dim tmp6(5000) As String

When the values are placed on the new document to email, I have to trim some of the values since some fields are really long. If I don’t, the text wraps and messes up the the document. The doc links won’t match up to the line. I just wanted the dates to appear correct too. That’s the only way I knew how to do it. I need that information on the newdoc though like that.

What are you trying to do here? Where is tmp1…tmp6 declared? Maybe you are getting an error here

tmp1(ctr) = Format(DocOne.docdate(0),“mm/dd/yyyy”) 'It pains me to see dates converted to text, but whatever…

tmp2(ctr) = Left(DocOne.complaintby(0),20)

tmp3(ctr) = Left(Strconv(DocOne.rmacompanyname(0),3),30)

tmp4(ctr) = Left(DocOne.partnumber(0),20)

tmp5(ctr) = Format(DocOne.qrdate(0),“mm/dd/yyyy”)

tmp6(ctr) = Format(DocOne.ccloseddate(0),“mm/dd/yyyy”

Counting, I guess I don’t need that line. I just want to make sure all the docs are selected and it loops and displays the data one line (fields on doc) after another…maybe I don’t understand that part really.

Thanks…I might still need help!!

hcl-bot · November 14, 2007, 8:35am

Subject: RE: Try this (again…)

No, that’s OK - now that I see how you have declared your tmp variables, it makes sense. You are first declaring a larger array than you think you need and them trimming off the unused elements at the end of your looping.

And you do need the counter because that tells you how much document data you have add to your 5000-max arrays. However, you were forgetting to reset the counter to 0 even though you were erasing the array.

Good luck

hcl-bot · November 14, 2007, 9:58am

Subject: RE: Try this (again…)

Thanks Cesar…I’m learning a lot here…

Hopefully I can get this to work… thanks again!

hcl-bot · November 14, 2007, 11:19am

Subject: once again…Try this (again…)

Well, I’m still having issues with this…sorry.

If datToday > DocOne.GetItemValue(“DocDate”)(0) + 5 Then

If the field DocDate + 5 is < Today’s date, then get all the documents…since they are at least 5 days old.

The code is picking all the documents. It should pick only a few.

Another weird thing is when I change the script to + 20, the script just keeps running and I have to control break it. Once I do that, one email is sent. It runs for about 2 minutes then I control break it.

Any suggestions? Thank once again!!!

hcl-bot · November 14, 2007, 11:32am

Subject: RE: once again…Try this (again…)

When you say

“The code is picking all the documents. It should pick only a few.”

Do you mean that it is picking up every DocOne?

If so, the loop is setup to go through all of the documents so in one way, that should not surprise you. However, if the mail contains links to all the documenets, it means that the comparison of the dates is failing. I honestly don’t know what values your documents contain and I fear that as in your previous code, you like to store dates as text, this may be the source of the problem.

Are you certain you have the expected values in the field DocDate of the DocOne documents that you are processing and that they truly are stored as dates? If I were you, I would focus on the code where the comparison is done - use the debugger!

hcl-bot · November 14, 2007, 11:51am

Subject: RE: once again…Try this (again…)

Hey, thanks!!! Let me explain…

Do you mean that it is picking up every DocOne? Yes, every document in the view is being picked up even when I’m trying to select based of the dates. I only want to pick the docs if they match the date criteria then send the email.

Yes, I believe the comparison of the dates is failing.

Yes, the fields are date fields.

docdate =

date/time

computed when composed (@Created )

Notes style

Allow multiple values NOT checked

display only month, day and year

I love the debugger…it helps me a lot…and still having I’m having issues… :O(

Thanks again…you are a great help…

hcl-bot · November 14, 2007, 11:55am

Subject: RE: once again…Try this (again…)

The fields may be DEFINED in the form as date fields but what do the documents actually contain?

Look at the document properties by clicking on a document in the view and on the 2nd tab, examine the field named DocDate (they are listed alphabetically). The field should contain something like:

Field Name: DocDate

Data Type: Time/Date

Data Length: 8 bytes

Seq Num: 2

Dup Item ID: 0

Field Flags: SUMMARY

hcl-bot · November 14, 2007, 12:08pm

Subject: RE: once again…Try this (again…)

Here is what the field docdate show on the second tab:

Field Name: DocDate

Data Type: Time/Date List or Range

Data Length: 12 bytes

Seq Num: 1

Dup Item ID: 0

Field Flags: SUMMARY

10/23/2007 10:27:36 AM EDT

On the newdoc.form… all the fields are text and the link is rich text field. Not sure this part matters though.

hcl-bot · November 14, 2007, 12:10pm

Subject: RE: once again…Try this (again…)

OK - the fact that in your case, the field is a

Time/Date List or Range

and not a

Time/Date

may be the reason why the comparison fails. Can you post the actual contents of the field (scroll a little further down).

hcl-bot · November 14, 2007, 12:44pm

Subject: RE: once again…Try this (again…)

If I click on DocDate, in the white box to the right, this is what show…it scrolls for a while down but this is all that shows…Is that what you mean?

Field Name: DocDate

Data Type: Time/Date List or Range

Data Length: 12 bytes

Seq Num: 1

Dup Item ID: 0

Field Flags: SUMMARY

10/23/2007 10:27:36 AM EDT

I’m not sure I even know the difference of the Time/Date List or Range…

hcl-bot · November 14, 2007, 12:53pm

Subject: RE: once again…Try this (again…)

OK, the document that you pulled this data from, would be selected by the agent running today (Nov. 14) because its DocDate value is

10/23/2007 10:27:36 AM EDT

and that makes the difference between today and DocDate > 5 days. Is this what you expect for this document? What about other documents?

hcl-bot · November 14, 2007, 1:12pm

Subject: RE: once again…Try this (again…)

Here are some that shouldn’t be selected. I want docs that are 5 days old or less than 5 days old, not > 5 days. Maybe I didn’t explain enough? Thanks!!!

Field Name: DocDate

Data Type: Time/Date List or Range

Data Length: 12 bytes

Seq Num: 1

Dup Item ID: 0

Field Flags: SUMMARY

08/08/2007 03:43:46 PM EDT

Field Name: DocDate

Data Type: Time/Date List or Range

Data Length: 12 bytes

Seq Num: 1

Dup Item ID: 0

Field Flags: SUMMARY

09/24/2007 08:09:01 PM EDT

Field Name: DocDate

Data Type: Time/Date List or Range

Data Length: 12 bytes

Seq Num: 1

Dup Item ID: 0

Field Flags: SUMMARY

08/10/2007 08:48:32 AM EDT

Field Name: DocDate

Data Type: Time/Date List or Range

Data Length: 12 bytes

Seq Num: 1

Dup Item ID: 0

Field Flags: SUMMARY

08/24/2007 01:12:00 PM EDT

Field Name: DocDate

Data Type: Time/Date List or Range

Data Length: 12 bytes

Seq Num: 1

Dup Item ID: 0

Field Flags: SUMMARY

08/15/2007 09:12:14 AM EDT

Field Name: DocDate

Data Type: Time/Date List or Range

Data Length: 12 bytes

Seq Num: 1

Dup Item ID: 0

Field Flags: SUMMARY

09/12/2007 11:31:06 AM EDT