?
Solved

Send Newsletter or use LS Agent

Posted on 2007-10-08
21
Medium Priority
?
307 Views
Last Modified: 2013-12-18
Experts,

I have used Newsletter agents before, with good success, based on the value of a specific field (open/close) and all documents assigned to one person.  The person would get an email with all doc links to documents that needed their attention.

Not sure how I can do something somewhat similar, but I need to send an email to users when they have "not" submitted a document (report).  So, there is a view with all documents, that are also categorized based on region.  Is there a way to use UserRoles to be able to identify that their name is not in a specific week?  So, let's say East has 3 people, and West has 5, the view would look like this:

Week 1
 East
   Person 1
   Person 2
   Person 3
 West
   Person 4
   Person 5
   Person 6
   Person 7
   Person 8

And, lets say for a given week, or weeks, Person 2 did not submit a document and he belongs East and Person 6 (from West) is also missing documents from 3 weekly periods.  How can I send an auto alert, on a weekly basis that might identify the weeks where users did not have a document posted?  Is this even possible?
0
Comment
Question by:AliciaVee
  • 8
  • 6
  • 5
  • +1
20 Comments
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 20037379
It is possible, but you would need a table of user names to check against.  You cannot use @UserRoles since that function only returns information about the current user (which is the server in this case).

You can get a list of all users from the Domino Directory, but somtimes there are users in the Directory that you don't want to send email to.  The best way to handle this, in my opinion, is to create a separate Organization Directory database that lists all users, but has additional fields that define thier role(s) in your applications.  For example, you can add a checkbox to the user profile form that says something like "Recieves report newsletters".

Once you get all of that setup, the rest is easy.
0
 

Author Comment

by:AliciaVee
ID: 20037400
Bill -- thanks for your reply.  Okay, so would a Profile document work?  If I have a profile document that has each user listed in each region -- or does it even have to be a profile document?  If I create a document with the following fields: East, West, North, South, and have all users in each field based on their region, then display this in a view -- would that help?  What would the next steps be?
0
 
LVL 22

Accepted Solution

by:
Bill-Hanson earned 1000 total points
ID: 20041407
Then, you need to design an agent that reads each list of names and process them separately.

For example, first get the users in the West region...

      Dim userNames as Variant
      userNames = docProfile.GetItemValue("WestUsers")

... then process that list ...

      Forall userName In userNames
            Call SendNewsletter(userName)
      End Forall

... Create a function called SendNewsletter that looks up any documents for the user and sends the newsletter.  If no documents match, just exit the function.  You will probably want to create a special view just for this purpose that displays these documents categorized by username.  Then, you can just use NotesView.Getalldocumentsbykey to get the documents for the newsletter.  Then repeat this for each region field.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 20041434
I just remembered that you want to send notifications when the user does NOT have a document.  You can still use the same idea from my last post, just send the notification when NotesView.Getalldocumentsbykey returns zero documents instead of creating an actual newsletter.
0
 

Author Comment

by:AliciaVee
ID: 20041899
Bill -- ah...I think I've got it.  Okay -- thanks.  Yes, when 0 docs exists, then send an alert.  So, I don't use the Newsletter agent because there will be no docs.  I just send an alert.  Will I be able to identify which documents are missing?  So, if there are 4 weeks in a month, and Person 2 from East region only submitted 2 documents in 2 weeks of the 4, will I be able to pull those dates and say something like, "You have not submitted activity reports for the following weeks: 10/1/2007, 10/8/2007 (I have the view categorized by weeks, with the reports (documents) sorted by user name -- all use a Monday date, so they group by week)

I'm going to work on both solutions you provided -- at least to a certain point where I can.  Today is a good day (so far) and I will get back to you in a few hours.
0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 20042178
I think you have the right idea.  Good luck.
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 20043012
Actually, I often use a "self maintained" user list for these purposes.  Have a view that shows documents for the last x days (say, 8 weeks in your case, which means if a user got away with murder for 8 weeks, we don't even ask them anymore).  View simply categorizes on name, and we pick up all unique names from the view.

Then, for each name, see what weeks are present and what weeks are expected, and simply remove the "present" fro the "expected."

Newsletter does not work for this, but the LotusScript (or even formula!) is not that difficult.

I'm not proposing anything all that different from Bill, but it cuts down onmaintenance and allows you to acocunt for a time period for review as well.

0
 

Author Comment

by:AliciaVee
ID: 20043588
qwaletee -- I'm laughing.  If they got away with murder, in this case, they still need to get prosecuted -- ha ha ha.

Bill -- okay -- I'm not sure how to start this.  I have a view.  I have test documents in specific weeks -- view looks like this:

Categorized on Date (using @Adjust(RptDate;0;0;1-@Weekday(RptDate);0;0;0) to show a Monday date.

Date           Submitted By
10/8/2007
                   Person 1
                   Person 2
                   Person 3
10/1/2007
                  Person 1
                  Person 3
9/24/2007
                  Person 1
                  Person 2

In the example above, Person 1 would not get an alert -- they submitted all required reports.  Person 2 would get an alert that says they are missing 10/1/2007 report and Person 3 would get an alert that says they are missign 10/102007 and 9/24/2007 report.

There are less than 20 folks that would get these alerts -- on a weekly basis.  I do have a document created that lists all users and which region they belong -- separate fields, so I could create a view that lists all 20 users (for this specfici purpose) who would be required to submit reports.  Can someone help explain (provide LS) that will do this?
0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 20044096
There are many ways to implement this, but you'll probably want to create a new view for this purpose and categorize it by user name.  The selection criteria can limit the view to only show the documents for the reporting period that you are concerned with, but it is not necessary.

For each name in your user's list, you will need to get a list of documents, then check those documents for the dates that are missing.

Here's some pseudo-code to get you going...
      
Dim sess as New NotesSession()
dim db as NotesDatabase
Dim view as NotesView
Dim dc as NotesDocumentCollection
Dim doc as NotesDocument
dim arrRequiredDates as variant
dim arrReportedDates as variant
dim arrMissingDates as variant

Set db = sess.CurrentDatabase
Set view = db.GetView("ReportsByUser.Lookup")
.
.
.
[you'll need to get the report dates into an array -- here's a manual method]
arrRequiredDates = Evaluate({"10/1/2007":"10/10/2007":"9/24/2007"})
.
.
.
[now we need to get the report dates for each user]
[for each name in your list]
userName = [a name from your list]
Set dc = view.GetAllDocumentsByKey(userName, True)
Set doc = dc.GetFirstDocument()
do Until doc is nothing
      If Isarray(arrReportedDates) Then Redim Preserve arrReportedDates(Ubound(arrReportedDates)+1) Else Redim arrReportedDates(0)
      arrReportedDates(Ubound(arrReportedDates)) = doc.GetItemValue("ReportDate")(0)
      set doc = dc.GetNextDocument(doc)
Loop
.
.
.
[now you have an array of required dates and an array of reported dates for this user.  All that's left is to remove the reported dates from the required dates and send the email.
0
 
LVL 31

Assisted Solution

by:qwaletee
qwaletee earned 1000 total points
ID: 20044123
I would actually reverse the sort order -- first name then date -- because you are sending a notice pper person, not per date.  Therefore, you want to sort on person first.

Example of code that you can adapt:

'set up once for the entire run across all users
lastSunday = today + 1 - weekday(today)
Dim sundays list as boolean
For i = 0 to 7
  checkSunday = lastSunday - (7*i)
  sundays(cstr(checkSunday)) = True
Next
Dim notice as new notesDocument(thisDatabase)
notice.form = "Memo"
notice.Subject = "You have delinequent weekly reports"


'run the following for each user -- this will need to be in a loop of some sort
Dim anyProblems as boolean
Forall everySunday in sundays
  everySunday = False 'mark this one as incomplete for this user until we find the matching report
End Forall
set reports = view.getAllDocumentsByKey( nameOfUser , True )
anyProblems = false
notice.SendTo = nameOfUser
notice.body = ""
Set report = reports.getFirstDocument
Do until report is nothing
  if report.columnValues(0) <> nameOfUser Then
    delete report
  else
    if iselement(sundays(cstr(report.columnValues(1)))) Then
        sundays(cstr(report.columnValues(1))) = True 'mark this one as having been complete
    end if
    Set report = reports.getNextDocument(report)
  end if
Loop
Forall everySunday in sundays
  if not everySunday Then
    notice.body = notice.body(0) & "Missing report for " & listTag(everySunday) & chr$(13)
    anyproblems = true
  end if
report
End Forall
If anyProblems Then
 notice.Send False
End If
0
 

Author Comment

by:AliciaVee
ID: 20049292
qwaletee,

I started with your code first, since I thought I could "adapt" it -- am not able to create code from scratch with psuedo code.  I do have errors.  First, I searched on getAllDocumentsByKey in Notes Help and used their initial declarations from an example as a start -- not sure if this is correct.  I also tried, where possible, to input the values specific to my needs (view, field name) etc.  I've added comments to what I changed.  Errors appearing first is in "report" -- and the message is "Not a Sub of Function Name -- I guess because it wasn't declaried? I don't see where lastSunday is declared either.  Help!!  I see red everywhere.

=====================================
Sub Initialize
      
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim dc As NotesDocumentCollection
      Set db = session.CurrentDatabase
      ' vMembers is my view, first colum sorted by field where username is held using the following: @Name([CN];MemberList)
      'This view is not categorized, and uses "show multiple values as separate entries" for this column
      Set view = db.GetView( "vMembers" )
      'MemberList is the Names field, multivalue that holds the value of all users who need to receive alerts
      Set dc = view.GetAllDocumentsByKey("MemberList", False)
      
      'set up once for the entire run across all users
      lastSunday = Today + 1 - Weekday(Today)
      For i = 0 To 7
            checkSunday = lastSunday - (7*i)
            sundays(Cstr(checkSunday)) = True
      Next
      Dim notice As New notesDocument(LPW.nsf) 'LPW.nsf is the name of the current database
      notice.form = "Memo"
      notice.Subject = "You have delinequent weekly reports"
      
'run the following for each user -- this will need to be in a loop of some sort
      Forall everySunday In sundays
            everySunday = False 'mark this one as incomplete for this user until we find the matching report
      End Forall
      Set reports = view.getAllDocumentsByKey( nameOfUser , True )' not sure what goes here??
      anyProblems = False
      notice.SendTo = nameOfUser
      notice.body = ""
      Set report = reports.getFirstDocument
      Do Until report Is Nothing
            If report.columnValues(0) <> nameOfUser Then
                  Delete report
            Else
                  If Iselement(sundays(Cstr(report.columnValues(1)))) Then
                        sundays(Cstr(report.columnValues(1))) = True 'mark this one as having been complete
                  End If
                  Set report = reports.getNextDocument(report)
            End If
      Loop
      Forall everySunday In sundays
            If Not everySunday Then
                  notice.body = notice.body(0) & "Missing report for " & Listtag(everySunday) & Chr$(13)
                  anyproblems = True
            End If
            report
      End Forall
      If anyProblems Then
            notice.Send False
      End If
End Sub
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 20052606
I didn't declare variables in the sample.  lastSunday is a variant (date), reports is notesDocumentCollection, report is notesDocument

nameOfUser really has to be set by you and run in a loop.  For testing purposes, you can declare it as a string and set it manually to the name of a specific user to see if it works.
0
 

Author Comment

by:AliciaVee
ID: 20101179
qwaltee -- I will give it another try.  I can't even run the code because it is giving me all kinds of errors (a lot of the lines are red and won't go away)  For example, the line where "report" is initially mentioned is giving me the following error: "Not a sub or function"  how do I fix that?
0
 

Author Comment

by:AliciaVee
ID: 20101249
q -

I tried to clean up the code a bit (extra spaces removed, etc) and I'm now getting errors in this section:

 'set up once for the entire run across all users
      lastSunday = Today + 1 - Weekday(Today)
      For i = 0 To 7
            checkSunday = lastSunday - (7*i)
            sundays(Cstr(checkSunday)) = True
      Next
      Dim notice As New notesDocument(LPW.nsf) 'LPW.nsf is the name of the current database

Except for the word "Next" the complete section of code above is red.

What to do?
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 20105107
I only get red on
            sundays(Cstr(checkSunday)) = True
...and that's because it needs the definition for sundays, which I gave earlier:
      Dim sundays list as boolean
Once I added that back in, I had no error saving that snippet.

You also have an mistake:
Dim notice As New notesDocument(LPW.nsf)
You can't pass LPW.nsf to the notesDocument constructor.  You have to pass a notesDatabase object, for example:

Dim thisDB as notesDatabase
Set thisDB = sess.currentDatabase
Dim notice as New notesDocument(thisDB)
0
 

Author Comment

by:AliciaVee
ID: 20109041
qwaletee,

I don't know what I'm doing wrong -- still getting the same "report" error -- not a sub or function.  I added in the Dim sundays list as boolean -- don't know how that dropped off from your original code?  And, I changed the thisDb object -- but still getting errors so I can't save the script or run it.

====================
Sub Initialize
      
      Dim session As New NotesSession
      Dim thisDb As NotesDatabase
      Dim view As NotesView
      Dim dc As NotesDocumentCollection
      Set thisDb = session.CurrentDatabase
      Dim notice As New notesDocument(thisDB)
      
       ' vMembers is my view, first colum sorted by field where username is held using the following: @Name([CN];MemberList)
      'This view is not categorized, and uses "show multiple values as separate entries" for this column
      Set view = thisDb.GetView( "vMembers" )
      
        'MemberList is the Names field, multivalue that holds the value of all users who need to receive alerts
      Set dc = view.GetAllDocumentsByKey("MemberList", False) 'MemberList is the field where my users names are held -- multivalue -- first column of the view, sorted, not categorized.
      
      'set up once for the entire run across all users
      
      lastSunday = Today + 1 - Weekday(Today)
      
      
      Dim sundays List As Boolean
      For i = 0 To 7
            checkSunday = lastSunday - (7*i)
            sundays(Cstr(checkSunday)) = True
      Next
      
      notice.form = "Memo"
      notice.Subject = "You have delinequent weekly reports"
      
      'run the following for each user -- this will need to be in a loop of some sort
      Forall everySunday In sundays
            everySunday = False 'mark this one as incomplete for this user until we find the matching report
      End Forall
      
      Set reports = view.GetAllDocumentsByKey("MemberList",True )
      anyProblems = False
      
      
      notice.SendTo = "MemberList"
      notice.body = ""
      Set report = reports.GetFirstDocument
      
      
      Do Until report Is Nothing
            If report.columnValues(0) <> "MemberList" Then
                  Delete report
            Else
                  If Iselement(sundays(Cstr(report.columnValues(1)))) Then
                        sundays(Cstr(report.columnValues(1))) = True 'mark this one as having been complete
                  End If
                  Set report = reports.getNextDocument(report)
            End If
      Loop
      Forall everySunday In sundays
            If Not everySunday Then
                  notice.body = notice.body(0) & "Missing report for " & Listtag(everySunday) & Chr$(13)
                  anyproblems = True
            End If
            
            report
            
      End Forall
      If anyProblems Then
            notice.Send False
      End If
      
End Sub
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 20131767
Dim reports as notesDocumentCollection
0
 

Author Comment

by:AliciaVee
ID: 20133496
I changed the dc variable to reports.  I kept getting variables not declared errors, so I "set" other variables where they were not declared in the Dim statements.  Not sure if this is correct, but didn't know what else to do.  Did you say you ran or saved this code and it saved okay?  It still will not save for me so I cannot test or run it.

I am getting red errors on the following lines of code:

Set lastSunday = Today + 1 - Weekday(Today)

For i = 0 To 7
Set checkSunday = lastSunday - (7*i)

Set anyProblems = False

Set report = reports.GetFirstDocument

====================================================

Dim session As New NotesSession
      Dim thisDb As NotesDatabase
      Dim view As NotesView
      Dim reports As NotesDocumentCollection
      Set thisDb = session.CurrentDatabase
      Dim notice As New notesDocument(thisDB)
      
       ' vMembers is my view, first colum sorted by field where username is held using the following: @Name([CN];MemberList)
      'This view is not categorized, and uses "show multiple values as separate entries" for this column
      Set view = thisDb.GetView( "vMembers" )
      
      'MemberList is the Names field, multivalue that holds the value of all users who need to receive alerts
      'MemberList is the field where my users names are held -- multivalue -- first column of the view, sorted, not categorized.      
      Set reports = view.GetAllDocumentsByKey("MemberList", False)
      
      
      'set up once for the entire run across all users
      
      Set lastSunday = Today + 1 - Weekday(Today)
      
      
      Dim sundays List As Boolean
      For i = 0 To 7
            Set checkSunday = lastSunday - (7*i)
            sundays(Cstr(checkSunday)) = True
      Next
      
      notice.form = "Memo"
      notice.Subject = "You have delinequent weekly reports"
      
      'run the following for each user -- this will need to be in a loop of some sort
      Forall everySunday In sundays
            everySunday = False 'mark this one as incomplete for this user until we find the matching report
      End Forall
      
      Set reports = view.GetAllDocumentsByKey("MemberList",True )
      Set anyProblems = False
      
      
      notice.SendTo = "MemberList"
      notice.body = ""
      
      Set report = reports.GetFirstDocument
      
      Do Until report Is Nothing
            If report.columnValues(0) <> "MemberList" Then
                  
                  Delete report
            Else
                  If Iselement(sundays(Cstr(report.columnValues(1)))) Then
                        sundays(Cstr(report.columnValues(1))) = True 'mark this one as having been complete
                  End If
                  Set report = reports.getNextDocument(report)
            End If
      Loop
      Forall everySunday In sundays
            If Not everySunday Then
                  notice.body = notice.body(0) & "Missing report for " & Listtag(everySunday) & Chr$(13)
                  anyproblems = True
            End If
            
            reports
            
            
      End Forall
      If anyProblems Then
            notice.Send False
      End If
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 20133951
This compiles, I removed the line that only said "rreports" and changed it to a 3-line block, "If AnyProblems...", and I took the SET off anyProblems = ...., and I put in a boolean definition for anyProblems.  That's all I changed, and it compiles.  I don't know if it works, because I don't know your actual environment.  I do see that you hard-coded the SendTo as "MemberList," so I hope you have a recipient by that name

      Dim session As New NotesSession
      Dim thisDb As NotesDatabase
      Dim view As NotesView
      Dim reports As NotesDocumentCollection
      Set thisDb = session.CurrentDatabase
      Dim notice As New notesDocument(thisDB)
      
       ' vMembers is my view, first colum sorted by field where username is held using the following: @Name([CN];MemberList)
      'This view is not categorized, and uses "show multiple values as separate entries" for this column
      Set view = thisDb.GetView( "vMembers" )
      
      'MemberList is the Names field, multivalue that holds the value of all users who need to receive alerts
      'MemberList is the field where my users names are held -- multivalue -- first column of the view, sorted, not categorized.      
      Set reports = view.GetAllDocumentsByKey("MemberList", False)
      
      
      'set up once for the entire run across all users
      
      Set lastSunday = Today + 1 - Weekday(Today)
      
      
      Dim sundays List As Boolean, anyProblems As Boolean
      For i = 0 To 7
            Set checkSunday = lastSunday - (7*i)
            sundays(Cstr(checkSunday)) = True
      Next
      
      notice.form = "Memo"
      notice.Subject = "You have delinequent weekly reports"
      
      'run the following for each user -- this will need to be in a loop of some sort
      Forall everySunday In sundays
            everySunday = False 'mark this one as incomplete for this user until we find the matching report
      End Forall
      
      Set reports = view.GetAllDocumentsByKey("MemberList",True )
      anyProblems = False
      
      
      notice.SendTo = "MemberList"
      notice.body = ""
      
      Set report = reports.GetFirstDocument
      
      Do Until report Is Nothing
            If report.columnValues(0) <> "MemberList" Then
                  
                  Delete report
            Else
                  If Iselement(sundays(Cstr(report.columnValues(1)))) Then
                        sundays(Cstr(report.columnValues(1))) = True 'mark this one as having been complete
                  End If
                  Set report = reports.getNextDocument(report)
            End If
      Loop
      Forall everySunday In sundays
            If Not everySunday Then
                  notice.body = notice.body(0) & "Missing report for " & Listtag(everySunday) & Chr$(13)
                  anyproblems = True
            End If
            
            If anyProblems Then
                  notice.Send False
            End If
            
            
      End Forall
      If anyProblems Then
            notice.Send False
      End If
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20433915
Forced accept.

Computer101
EE Admin
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

839 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