asked on 7/24/2008

Newsletter style data gathering

Have an ordering database I have been working on, had a couple issues already resolved here. Was tasked yesterday with a new challenge. The DB has 2 main elements, an Office record which has the basic office information, and a User record which has what each user has ordered desktop, laptop etc. The user records are accessed by the office via an embeeded view which has all their records.

My issue here is that they want me to create a button that will be used by our financial area that they have reviewed the information and it is completed, this will then send off a tracer to a group who will update records with the serial numbers for the hardware etc. I was thinking something like a newsletter might work. But I am not sure how I can be on the office record and have it capture all the user records for just that office. So when on office ABC it will pull all the records for users in office ABC. Can I capture certain fields needed on a piece of email, can just be plain text, don't need anything fancy.

I know this one is a lot, but I am hoping someone might have some sample code here to give me a head start.

Lotus IBM

Last Comment

mbonaci

8/21/2008

mbonaci

7/24/2008

You say you have to put on the newsletter all the documents of users in office ABC.
Then do something like this:
(I wrote "from my head", haven't tested it, so post if you get stuck...)

Sub Click(Source As Button)
  Dim s As New NotesSession
  Dim w As New NotesUIWorkspace
  Dim db As NotesDatabase
  Dim v As NotesView
  Dim c As NotesDocumentCollection
  Dim nl As NotesNewsletter
  Dim doc As NotesDocument
  Dim key As String
 
  Set db = s.CurrentDatabase
  Set doc = w.CurrentDocument.Document
  key = doc.OfficeName(0)    'get the name of office (view category) from the current document
  Set v = db.GetView( "ViewName" ) 'maybe you'll have to create the view that is categorized by office
  Set c = v.GetAllDocumentsByKey( key, True )
  Set nl = New NotesNewsletter( c )
 
  For j = 1 To c.Count
    Set newDoc = nl.FormatMsgWithDoclinks( db )
    Call newDoc.Save( True, True )
    Call newDoc.Send( True, "here specify recipient" )
  Next
 
End Sub

Open in new window

jforget1

7/24/2008

ASKER

Worked the code as described, had to declare a couple variables, let me know if I got them right. When I run it I get a "No form associated with Document" error

jforget1

7/24/2008

ASKER

Forgot to paste in the code

Sub Click(Source As Button)
	Dim s As New NotesSession
	Dim w As New NotesUIWorkspace
	Dim db As NotesDatabase
	Dim v As NotesView
	Dim c As NotesDocumentCollection
	Dim nl As NotesNewsletter
	Dim doc As NotesDocument
	Dim newDoc As NotesDocument
	Dim key As String
	Dim j As Integer
	
	Set db = s.CurrentDatabase
	Set doc = w.CurrentDocument.Document
	key = doc.office_number(0)    'get the name of office (view category) from the current document
	Set v = db.GetView( "newsletter" ) 'maybe you'll have to create the view that is categorized by office
	Set c = v.GetAllDocumentsByKey( key, True )
	Set nl = New NotesNewsletter( c )
	
	For j = 1 To c.Count
		Set newDoc = nl.FormatMsgWithDoclinks( db )
		Call newDoc.Save( True, True )
		Call newDoc.Send( True, "Field Technology" )
	Next
	
End Sub

Open in new window

jforget1

7/24/2008

ASKER

Tried this, but am getting Object variable not set even though newDoc is declared above.

For j = 1 To c.Count
            newDoc.Form = "Memo"
            newDoc.Subject = "Newsletter"
            Set newDoc = nl.FormatMsgWithDoclinks( db )
            Call newDoc.Save( True, True )
            Call newDoc.Send( True, "Field Technology" )
      Next

mbonaci

7/24/2008

Do you want to create collection.count number of newsletters, or you want one newsletter with all the documents in the collection?

You can access (set its' fields) newDoc after you have created it.
The creation occurs by calling:

Set doc = newsletter.FormatMsgWithDoclinks( db )

That method creates the document just like CreateDocument method of NotesDatabase.
That's why you get that error Obj var not set.

newDoc.Form and newDoc.Subject place bellow line Set newDoc = ...

jforget1

7/24/2008

ASKER

I want to have one newsletter that has all the appropriate records. Essentially a table of data.

mbonaci

7/24/2008

Then you don't need the For loop.
The newsletter will have all documents from the collection you send as argument to method FormatMsgWithDoclinks.

Locate the NotesNewsletter class in Designer Help.
Use SubjectItemName to include the info from each doc, like this:

nl.DoSubject = True
nl.SubjectItemName = "DocFieldNameWhoseValueYouWantIncludedInNewsletter"
Set newDoc = nl.FormatMsgWithDoclinks( db )

Have to go...

jforget1

7/24/2008

ASKER

OK I got the newsletter working and that is excellent, just trying to do one more thing here. How can I set this up so I get data from more than just one field value. The code below dis not work, but I think it shows what I am trying to do here. I am not sure in this case as it has quotes and not doc.FieldName.

nl.SubjectItemName = "current_asset" & " " & "office_number"

jforget1

7/24/2008

ASKER

Tried this but cannot get it to return the values. I kow I am just having a brain fart here.

subject = doc.current_asset(0) & " " & doc.office_number(0)
nl.DoSubject = True
nl.SubjectItemName = subject

mbonaci

7/25/2008

You can't do it like that.
But you can trick that by using dedicated field on your form that will compute itself like this:

doc.NewsLetterSubject = doc.current_asset(0) & " " & doc.office_number(0)
nl.DoSubject = True
nl.SubjectItemName = "NewsLetterSubject" 'it has to be in double quotes

That's it!

jforget1

7/25/2008

ASKER

So are you saying I would put the code you noted above in a field? Or would I use basic code to concatenate the values I want into one hidden field to get the subject line?

jforget1

7/28/2008

ASKER

I have tried creating a field subject_line that have the combined values I want to show on the newsletter. When I try the button I get a type mismatch. I have tried it as a Variant and a String, same result. Am I declaring the wrong element here?

Sub Click(Source As Button)
	Dim s As New NotesSession
	Dim w As New NotesUIWorkspace
	Dim db As NotesDatabase
	Dim v As NotesView
	Dim c As NotesDocumentCollection
	Dim nl As NotesNewsletter
	Dim doc As NotesDocument
	Dim newDoc As NotesDocument
	Dim key As String
	Dim j As Integer
	Dim subject_line As String
	Set db = s.CurrentDatabase
	Set doc = w.CurrentDocument.Document
	key = doc.office_number(0)    'get the name of office (view category) from the current document
	Set v = db.GetView( "newsletter" ) 'maybe you'll have to create the view that is categorized by office
	Set c = v.GetAllDocumentsByKey( key, True )
	Set nl = New NotesNewsletter( c )
	nl.DoSubject = True
	nl.SubjectItemName = doc.subject_line
	Set newDoc = nl.formatMsgWithDoclinks( db )
	newDoc.Form = "Memo"
	newDoc.Subject = "Subject Line"
	Call newDoc.Save( True, True )
	Call newDoc.Send( True, "Field Technology" )
	
	
End Sub

Open in new window

jforget1

8/13/2008

ASKER

Any thought on concantanating the values so they can be used as a newsletter, I cannot get values to return at this point.

jforget1

8/19/2008

ASKER

Ran this thru debugger and am getting a Type Mismatch at the line below. I have dimmed it as a Variant and a String, same result. It shows text on the value of the field on the record.

nl.SubjectItemName = doc.subject_line

Value in subject_line field is John Dole | 4401592 | SL | D

mbonaci

8/20/2008

You are sending the array to the property that receives the string:
nl.SubjectItemName (newsletter property) = doc.subject_line (array with one element).

Do it like this:
nl.SubjectItemName = doc.subject_line(0)

Keep in mind that you'll have to refresh the old docs, because adding the computed field on form will not by itself update the values in old documents, only the new ones will have computed value in the new field.
To do that create an action in the view that lists all documents of that kind and place this as the action's formula:

@Command([ToolsRefreshSelectedDocs])

Then open the view and select the docs you want refreshed. Click the action and you'll have the new field filled.

Hope this helps,
mb¤

jforget1

8/20/2008

ASKER

OK that fixed the mismatch issue, I always forgot when and where I need the (0), but I am still only getting the doclink in the letter that is sent, the from the subject line field is not carrying over to the newsletter. I have looked at each record and they all have the values for subject_line populated as expected.

ASKER CERTIFIED SOLUTION

mbonaci

8/20/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

jforget1

8/20/2008

ASKER

Wow that last change did it, did not think I was going to get to the bottom of this one. Really appreciate all the help here.

mbonaci

8/21/2008

The word "problem" doesn't exist when two of us are working on it :)

Thanks for the points,
Mb¤