Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

LotusScript Newsletter Agent to check if employees submitted timecard on due date

Posted on 2011-10-14
12
Medium Priority
?
918 Views
Last Modified: 2013-12-18
Hi,
I've made an attempt to write lotusscript agent to read a list of employees from a view, get employee name then
(2) find employee name in timecard view
(3) if employee created card, then check if it is due and hasn't been completed, then write this information to newsletter
(4) if employee name not found in timecard view, then write this information to newsletter

Any help would be appreciated with modifying this homegrown script or giving me another solution.  Thanks in advance.
Dim session As New NotesSession 
	Dim db1 As NotesDatabase
	Set db1= session.CurrentDatabase 
	Dim db1View As NotesView  
	Set db1View = db1.GetView ("(EmployeeView)")
	Dim doc1 As NotesDocument
	Set doc1 = db1View.getfirstdocument
	
	
	Dim db2View As NotesView  
	Set db2View = db1.GetView ("(TimeCardView)")
	Dim doc2 As NotesDocument
	Set doc2 = db2View.getfirstdocument
	

	Dim dateToday As New NotesDateTime( "" )
	dateToday.LSLocalTime = Now   ' date only
	Dim international As NotesInternational
	Set international = session.International
	
	
	Dim doc As NotesDocument, newdoc As NotesDocument
	Dim collection As NotesDocumentCollection
	Dim item As NotesItem
	Dim newsletter As NotesNewsLetter
	Dim rtitem As Variant
	Set newsletter = New NotesNewsLetter( collection )
	newsletter.DoSubject = True
	newsletter.SubjectItemName = "Subject"
	
	' Read Staff View key to get list of all staff members, then check it against the timecard forms to see if each staff member created a timecard.
	' Create newsletter and (1) list staff members with no card, and (2) list staff members with a card for today but not completed.
	
	Do Until doc1 Is Nothing
		Do Until doc2 Is Nothing
			If doc2.StaffMember(0) >= doc1.TAName(0) Then Exit Do ' no more docs to process
			Set doc2= db2View.GetNextDocument(doc1)
		Loop
		If doc2 Is Nothing Then
			Call rtitem.AddNewLine(1)
			Call rtitem.AppendText("No Time Card Entry for " + doc1.TAName )
			
		
		
		 ElseIf doc2.GetItemValue ("StaffMember")(0) = doc1.GetItemValue("TAName")(0) Then  ' check it international today's date  is same as due date :
			' ActivityDateDue is in format D/mm/year  - Need to compare it with today's date converted to international format
		End If
		' If doc2.GetItemValue ("Status")(0) = "Draft"   And  doc2.GetItemValue ("ActivityDateDue")(0)  =  TodayDate(0)   Then  
		If doc2.GetItemValue ("Status")(0) = "Draft"   And  doc2.GetItemValue ("ActivityDateDue")(0)  =  international.IsDateDMY    Then  
			Call rtitem.AddNewLine(1)
			Call rtitem.AppendText("Time Card Not Completed for " + doc1.TAName )
		End If
		
		
		Set doc1= db1View.GetNextDocument(doc1)  '  getting error in debug: The Document is not in the view
	Loop
	
	Set doc = newsletter.FormatMsgWithDoclinks( db1 )
	doc.Form = "Memo"
	doc.Subject = "Today's Timeard Report"
	Set rtitem = doc.GetFirstItem( "Body" )

	Call rtitem.AddNewLine(1)
	Call doc.Send( False, "name/company" )

Open in new window

0
Comment
Question by:KTTKTT
  • 8
  • 4
12 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 36967304
The logic isn't entirely correct, and the collection should be filled differently. The idea of a NotesNewletter is also different, you generate the whole mail yourself, why do you need it anyway (i.e. the Newsletter)?

Some remarks to think about:
- you first need to have a collection of documents BEFORE you generate the newsletter
- can the 2nd loop be converted into one based on a GetAllDocumentsByKey, the key being the user's name?
- when the 2nd loop terminates with a value/object in doc2, you don't know if you found the same user (a strange loop it is...)
- IMHO you have to collect the No Time Card info in some string, so you can add it at the end to the newsletter

Suggestion: create a collection, create a newsletter, send it to yourself and see what it produces.
0
 

Author Comment

by:KTTKTT
ID: 37019698
It was more important to get the View list comparison done first.  Ok I think I have the results I need using this script to filter out who didn't produce a card for today.  I created employee view with list of all employees.
Then created a 2nd view that list employees who created a record for today's date.   My agent should compare both list and find out which employees did not create a card for today.  Now I need to get this info. in a memo.

Open in new window


Open in new window

Dim session As New NotesSession 
	Dim db As NotesDatabase
	
	Dim View1 As NotesView  
	Dim View2 As NotesView
	Dim vc As NotesViewEntryCollection

	
	
	Dim coll As NotesDocumentCollection
	Dim doc As NotesDocument
	Dim Cardlist List As String 'List2
	Dim Stafflist List As String 'List1
	
	Dim item As NotesItem
	
	
	Set db = session.CurrentDatabase 
	
	
	Dim x As Integer
	Dim y As integer

	x = 0
	y = 0

	

	
	Set View1 = db.GetView ("EmployeeList")
	
	
	Set coll=db.search(|View1 = EmployeeList|, Nothing, 0)
	Set doc = coll.getfirstdocument
	While Not doc Is Nothing
		Emplist(doc.EmpName(0) )=doc.EmpName(0)
		Set doc = coll.getnextdocument(doc)
		x = x + 1
	Wend


	
	Set View2 = db.GetView ("CardCompleted")
		Set coll=db.search(|View2 = CardCompleted|, Nothing, 0)
	Set doc = coll.getfirstdocument
	While Not doc Is Nothing
		CardComplete(doc.CardMember(0) )=doc.CardMember(0)
		Set doc = coll.getnextdocument(doc)
		y = y + 1
	Wend


	ForAll Emp In Emplist
		If Not IsElement ( Cardlist ( ListTag(Emp) ) ) Then
			Print ListTag(Emp)+" is missing a card"
		End If
	End ForAll

Open in new window

0
 

Accepted Solution

by:
KTTKTT earned 0 total points
ID: 37140837
my final working agent as follows.  If someone has a better solution please post, but it's working for me and I should give myself an A for effort and deserve the 500 points  :)
Initial problem:  Needed a way in Lotusscript to read a list of employees from a view, and see if they created a record in the database for a given day, then send an email or newsletter.  Just used regular memo for this.

Note:  with this code you need to have the due date already set in the form.  The first code attempt above did not evaluate the 2nd view that had @today for the due date - it was actually searching all documents in the database instead, so I ended up using a formula to search for specific docs used as the cardlist to compare against view1 employee stafflist.  Hope this is clear.

Sub Initialize   

	Dim session As New NotesSession 
	Dim db As NotesDatabase
	
	Dim View1 As NotesView  
	'Dim View2 As NotesView
	Dim vc As NotesViewEntryCollection
	
	Dim coll As NotesDocumentCollection
	Dim doc As NotesDocument
	Dim Cardlist List As String 'List2
	Dim Stafflist List As String 'List1
	
	Dim collection As NotesDocumentCollection
	Dim searchFormula as string
	 searchFormula$ = {Form = "TForm" & DateDue = @Today  & Status = "Complete"}
	
	Set db = session.CurrentDatabase 
	
	
	Dim x As Integer
	Dim y As integer

	x = 0
	y = 0
	
	
	

	Set View1 = db.GetView ("EmployeeList")
		
	Set coll=db.search(|View1 = EmployeeList|, Nothing, 0)
	Set doc = coll.getfirstdocument
	While Not doc Is Nothing
		Stafflist(doc.Name(0) )=doc.Name(0)
		Set doc = coll.getnextdocument(doc)
		x = x + 1
	Wend
	
 
Set collection = db.Search(searchFormula$, Nothing,0)
  Set doc = collection.GetFirstDocument()

	While Not(doc Is Nothing)

Cardlist(doc.Name(0) )=doc.Name(0)
		Set doc = collection.getnextdocument(doc)
		y = y + 1
		
	Wend

	Dim item As NotesItem
	Dim rtitem As NotesRichTextItem
	Dim rtitememailbody As Variant

	Dim  maildoc As NotesDocument
	Dim richText As NotesRichTextItem
	
	Dim richStyle As NotesRichTextStyle
	Set richStyle = session.CreateRichTextStyle
	Set maildoc = New NotesDocument(db)
	Set maildoc=db.createdocument
	
	maildoc.Form = "Memo"
	
	maildoc.Subject = "Report - Employees with no Record for today"
	maildoc.SendTo = "username/xyz"
	'	maildoc.CopyTo = ""
	maildoc.BlindCopyTo = ""
	
	Set richText = New NotesRichTextItem(maildoc,"Body")  
	Call richText.AppendStyle(richStyle)
	
	Call richText.AppendText( "The following employees do not have a Completed record for today:" )
	Call richText.AddNewLine(2)
	
		
		  
		'Find out what documents are missing in view by comparing all values in employee key with values from search criteria 
		ForAll v In Stafflist    
			If Not IsElement ( Cardlist ( ListTag(v) ) ) Then
				Print ListTag(v)+" is missing a record for today "
				
				
				Call richText.AppendStyle(richStyle)
				Call richText.AddNewLine(1)
				' Call richText.AppendText(ListTag(v) + "   is missing a record " )
				Call richText.AppendText(ListTag(v)  )
				
	
	
			End If
			
		End ForAll

		

		Call maildoc.Save ( True, True )
		
		
		Call maildoc.Send( False, "userxyz" )
		Print "Done"
End Sub

Open in new window

0
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!

 

Author Closing Comment

by:KTTKTT
ID: 37166507
Accepting my own comment as the solution because after trial and error, eventually came up with a working solution myself.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 37144063
Okay, you thought long and carefully about your code, and it shows. Some remarks that might help you, because I get the idea that you don't understand your own code for 100%, and some tips for shorter code:
- the variables x and y are calculated but never used
- the, 2nd list isn't required, you could loop through the documents and check it using the first list
- the first Search has a strange search formula: you check the field View1 in the document if it's equal to the field EmployeeList; what exactly did you have in mind?
- AFAIK you only have to append a style once
- even if all people filled in their document and there are no missing records, you send an (empty) mail out

0
 

Author Comment

by:KTTKTT
ID: 37145312
Hi Bossman,

I kept the x and y variables for debugging.
I certainly may not understand this 100%.
Please post a modified working example that's more efficient.  thanks
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 37145718
I'll try, but I can't test it right now...

And what is the first Search doing exactly, the one with View1=EmployeeList ?
Sub Initialize	 

	Dim session As New NotesSession 
	Dim db As NotesDatabase
	
	Dim vc As NotesViewEntryCollection
	
	Dim coll As NotesDocumentCollection
	Dim doc As NotesDocument
	Dim Cardlist List As String 'List2
	Dim Stafflist List As String 'List1
	
	Dim collection As NotesDocumentCollection
	Dim searchFormula as string
	Dim person As String
	Dim anyperson As Integer

	Set db = session.CurrentDatabase 

	' build list of completed forms for today
	searchFormula$ = {Form = "TForm" & DateDue = @Today	& Status = "Complete"} 
	Set collection = db.Search(searchFormula$, Nothing,0)
	Set doc = collection.GetFirstDocument()
	Do Until doc Is Nothing
		Cardlist(doc.Name(0) )=doc.Name(0)
		Set doc = collection.getnextdocument(doc)
	Loop

	' build a mail
	Dim	maildoc As NotesDocument
	Dim richText As NotesRichTextItem
	Dim richStyle As NotesRichTextStyle
	Set richStyle = session.CreateRichTextStyle
	Set maildoc = New NotesDocument(db)
	maildoc.Form = "Memo"
	maildoc.Subject = "Report - Employees with no Record for today"
	maildoc.SendTo = "username/xyz"
	'	maildoc.CopyTo = ""
	' maildoc.BlindCopyTo = ""
 	Call richText.AppendText( "The following employees do not have a Completed record for today:" )
	Call richText.AddNewLine(2)
	
	Set richText = New NotesRichTextItem(maildoc,"Body")	
	Call richText.AppendStyle(richStyle)

	' walk through employees	
	Set collection= db.search(|View1 = EmployeeList|, Nothing, 0)
	Set doc = collection.getfirstdocument
	Do Until doc Is Nothing
		person= doc.Name(0)
		'Find out what documents are missing in view by comparing all values in employee key with values from search criteria 
		If Not IsElement(Cardlist(person)) Then
			anyperson= True
			Print person+" is missing a record for today "
			Call richText.AddNewLine(1)
			' Call richText.AppendText(ListTag(v) + "	 is missing a record " )
			Call richText.AppendText(person)
		End If
		Set doc = coll.getnextdocument(doc)
	Loop
	If anyperson Then
		Call maildoc.Save ( True, True )
		Call maildoc.Send( False, "userxyz" )
	End If
	Print "Done"
End Sub

Open in new window

0
 

Author Comment

by:KTTKTT
ID: 37188076
I'm testing. thanks
0
 

Author Comment

by:KTTKTT
ID: 37208062
Bossman,
This works great - Thanks!  I made a few small corrections after testing - added this line:
1.
Call richText.AppendStyle(richStyle) ' was getting object variable not set until added this line
'before this line
Call richText.AppendText( "The following employees do not have a Completed record for today:" )
2.  Changed coll to collection on this line:
      Changed:
      Set doc = coll.getnextdocument(doc) ''''''''''''''''''''''''''''''''object var not set
      To:
        Set doc = collection.getnextdocument(doc)
----------------------
I do have several unresolved items.   1) I noticed that the report does not list the employee or person's name that shows up in the EmployeeList view who did not create a Record for today at all.  It does find employees who created a card but didn't complete it on time agent runs which is correct.
I thought the "If Not IsElement(Cardlist(person))" would handle this.  If person in the cardlist or EmployeeList View is not in the Search list, then it should capture those names as well.
2) I want to also be sure that an email is generated even if everyone created a record for a given day.  Something like:
ELSE
 Call richText.AppendText("All Time cards were submitted on time." )

Sub Initialize	 

	Dim session As New NotesSession 
	Dim db As NotesDatabase
	
	Dim vc As NotesViewEntryCollection
	
	Dim coll As NotesDocumentCollection
	Dim doc As NotesDocument
	Dim Cardlist List As String 'List2
	Dim Stafflist List As String 'List1
	
	Dim collection As NotesDocumentCollection
	Dim searchFormula as string
	Dim person As String
	Dim anyperson As Integer

	Set db = session.CurrentDatabase 

	' build list of completed forms for today
	searchFormula$ = {Form = "TForm" & DateDue = @Today	& Status = "Complete"} 
	Set collection = db.Search(searchFormula$, Nothing,0)
	Set doc = collection.GetFirstDocument()
	Do Until doc Is Nothing
		Cardlist(doc.Name(0) )=doc.Name(0)
		Set doc = collection.getnextdocument(doc)
	Loop

	' build a mail
	Dim	maildoc As NotesDocument
	Dim richText As NotesRichTextItem
	Dim richStyle As NotesRichTextStyle
	Set richStyle = session.CreateRichTextStyle
	Set maildoc = New NotesDocument(db)
	maildoc.Form = "Memo"
	maildoc.Subject = "Report - Employees with no Record for today"
	maildoc.SendTo = "username/xyz"
	'	maildoc.CopyTo = ""
	' maildoc.BlindCopyTo = ""
Call richText.AppendStyle(richStyle) 
 	Call richText.AppendText( "The following employees do not have a Completed record for today:" )
	Call richText.AddNewLine(2)
	
	Set richText = New NotesRichTextItem(maildoc,"Body")	
	Call richText.AppendStyle(richStyle)

	' walk through employees	
	Set collection= db.search(|View1 = EmployeeList|, Nothing, 0)
	Set doc = collection.getfirstdocument
	Do Until doc Is Nothing
		person= doc.Name(0)
		'Find out what documents are missing in view by comparing all values in employee key with values from search criteria 
		If Not IsElement(Cardlist(person)) Then
			anyperson= True
			Print person+" is missing a record for today "
			Call richText.AddNewLine(1)
			' Call richText.AppendText(ListTag(v) + "	 is missing a record " )
			Call richText.AppendText(person)
		End If
		Set doc = collection.getnextdocument(doc)
	Loop
	If anyperson Then
		Call maildoc.Save ( True, True )
		Call maildoc.Send( False, "userxyz" )
	End If
	Print "Done"
End Sub

Open in new window

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 37210593
Do you expect a reply from me, in this already closed question? ;-)
0
 

Author Comment

by:KTTKTT
ID: 37239316
Hi,
Yes, since the "Close" I thought was rejected and input was added by you.  How do we re-open then, so that you can get the points.  Thanks.
0
 

Author Comment

by:KTTKTT
ID: 37250370
REceived the following error when opened email generated from the report:
"Document has too many paragraphs - it must be split into several documents".
The report is only about 15 lines long.

How do we re-open this question for point assignment, please, thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

564 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