We help IT Professionals succeed at work.

Send Newsletter or use LS Agent

AliciaVee
AliciaVee asked
on
325 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?
Comment
Watch Question

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.

Author

Commented:
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?
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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.

Author

Commented:
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.
I think you have the right idea.  Good luck.

Commented:
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.

Author

Commented:
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?
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.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Commented:
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.

Author

Commented:
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?

Author

Commented:
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?

Commented:
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)

Author

Commented:
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

Commented:
Dim reports as notesDocumentCollection

Author

Commented:
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

Commented:
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
Forced accept.

Computer101
EE Admin
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.