Emailing buttons, doclinks, forms etc. - Need the most effective method!

Hi,

I have a database with approval workflow.
This database sits on multiple servers across the world.

When a new document is created an email is created with @mailsend and a doclink to the document in the db.

Here is the problem...

The doclink points to the server the document was created on until that document exists on the approver's server.
This means that when the user clicks the doclink it trys to connect back to a server across the other side of the world.

This leads me to think I need a solution where the approval takes place in the email itself...

Now if I put a couple of buttons in there then there is going to have to be a mail sent back to the database right?
This is easily done. I make the db a mailin db, give it a memo form etc.

What I would then need to do is update existing docs with the approval status from the returned mail.

Does this sound like a feasible/good idea?
How would I go about achieving this?

Thanks.
LVL 21
shuboarderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sjef BosmanGroupware ConsultantCommented:
Seems like you could create a form with the necessary buttons in it and send the document with the form (embedded) to the user, using Send(True). The only disadvantage is that you'll increase the amount of data a lot if a large form is to be sent. Maybe you could still send the link and send a much smaller form to the user.

Give it a try in a test-database.
0
shuboarderAuthor Commented:
Hi Sjef,

thanks for the response.
I would still need to send a mail back to the database though right?
0
Sjef BosmanGroupware ConsultantCommented:
Yes, absolutely, but the nature and content of that mail can be defined by yourself, in the form's logic. Everything of the form is set, including all LotusScript code. But watch out, as some fields in documents start to behave differently. Search your help db for "$TITLE"
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

shuboarderAuthor Commented:
Hi Sjef,

I've set this up now so that a smaller form is mailed with 2 buttons for approve and reject and also got the response being mailed back to the database. I am capturing the UNID of the document in the subject field and have added 2 views. One view shows Approved requests and one view shows rejected requests.

The structure of the subject is as follows: Status + " - " + UNID
e.g. Approved - 123456789ABCDEFGHIJKLMNOPQRSTUVW

Now for the part I really need help with....

I need an agent to pick up these mails and by using the UNID, update the status on the documents in the database.

Agent will run every 30mins and pick up received mail:

e.g.
Approved - 123456789ABCDEFGHIJKLMNOPQRSTUVW
It will find this document and update status field to = "Approved"

Sounds easy, but it's beyond me!

Any help / code snippets much appreciated!
0
Sjef BosmanGroupware ConsultantCommented:
First impression: neat! Second: make sure that NOBODY can send a fake mail. How? Haven't the foggiest, try to send the status and the UNID in some crypted way, the next release of your software.

For the agent, you need one in the database receiving the mail. The agent should be triggered on When new mail has arrived. An example of code you can find here:
http:/Q_20480274.html

In the loop, you need something like this:

Dim words As Variant

words= Split(doc.Subject, " - ")
If Ubound(words)=1 Then ' subject was okay
      Dim status As String
      Dim unid As String
      status= words(0)
      unid= words(1)
      Dim odoc As NotesDocument
      Set odoc= db.GetDocumentByUnid(unid)
      If Not odoc Is Nothing Then
            odoc.Status= status
            Call odoc.Save(True, False)
      End If
End If

You can fill in the blanks... Code is, as usual, not tested, not guaranteed, and not too difficult. I hope... :-))
0
shuboarderAuthor Commented:
Thanks Sjef...

making progress (I think) here is what it's looking like so far...


Sub Initialize
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim View As NotesView
	Dim doc As NotesDocument
	
	Set db=session.CurrentDatabase
	Set view = db.GetView("amail")
	Set doc=view.GetFirstDocument
	
	While Not doc Is Nothing
		
		Dim words As Variant
		
		words= Split(doc.Subject, " - ")
		If Ubound(words)=1 Then ' subject was okay
			Dim status As String
			Dim unid As String
			status= words(0)
			unid= words(1)
			Dim odoc As NotesDocument
			Set odoc= db.GetDocumentByUnid(unid)
			If Not odoc Is Nothing Then
				odoc.Status= status
				Call odoc.Save(True, False)
			End If
		End If
		
		Set doc=view.GetNextDocument(doc)
	Wend
End Sub

Open in new window

0
shuboarderAuthor Commented:
I presume your part of the code is doing the following:

splitting the subject into 2 where " - " is found
some validation on the words variable
declaring status as a string
declaring unid as a string
setting status variable = left of " - "
setting unid = right of " - "
declaring odoc as a NotesDocument
Setting odoc to match UNID of document to unid variable
If odoc is found then status = left of " - "
save odoc

Get next document...

I may be misunderstanding here...

Debug LotusScript is currently pointing to line 15, so I am obviously missing something before your part!
0
Sjef BosmanGroupware ConsultantCommented:
You have Notes R6 I hope? The function Split is new in R6 I think. What was the actual message?

Ah, no, b*ll*cks, my mistake:

        words= Split(doc.Subject(0), " - ")

So sorry... :-$
0
shuboarderAuthor Commented:
thanks... progress :)

Now getting the following error message:

"Invalid Universal ID"

Run debugger. Points to Line 22

The field I have on my form is called "unique_id"
it is a text field with the following formula:
@Text(@DocumentUniqueID)

I wonder if this is what is causing the problem?
0
shuboarderAuthor Commented:
Scrap that, I'm confusing myself now.
0
Sjef BosmanGroupware ConsultantCommented:
Apparently the unid isn't correct in the Subject. You could place a
      MessageBox unid
right in front of line 21.

By the way, the document with the UNID, can it be found in the same database the agent runs in? Merely checking...
0
shuboarderAuthor Commented:
Ok,

I deleted all the test data and put a new record in and it works well now.
Finally before closing, is there any way of stepping over a document if there is a problem with the unid?

At the moment if an error occurs it will prevent the agent running on subsequent docs...
0
Sjef BosmanGroupware ConsultantCommented:
Of course there is, using error-trapping code:


			On Error Goto oh_dear
                        Set odoc= db.GetDocumentByUnid(unid)
			On Error Goto 0
                        If Not odoc Is Nothing Then
                                odoc.Status= status
                                Call odoc.Save(True, False)
                        End If
                End If
nextdoc:                
                Set doc=view.GetNextDocument(doc)
        Wend
	Exit Sub
oh_dear:
	MessageBox Error$ & " on line " & Erl
	Resume nextdoc
End Sub

Open in new window

0
shuboarderAuthor Commented:
Ok... last point now I promise.
I've spent quite a bit of time on this now.

The issue I'm having is that the agent only works some of the time. I keep getting an error on line 23 I think
Would you mind checking the order of events here?

Basically if a unid is not found then the agent should step over and get the next document. It only seems to work if I run it manually twice. (not working as scheduled).

I'm guessing there is something not quite right - second pair of eyes much appreciated!
Sub Initialize
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim View As NotesView
	Dim doc As NotesDocument
	
	Set db=session.CurrentDatabase
	Set view = db.GetView("AMI")
	Set doc=view.GetFirstDocument
	
	While Not doc Is Nothing
		
		Dim words As Variant
		
		words= Split(doc.Subject(0), " - ")
		If Ubound(words)=1 Then ' subject was okay
			Dim status As String
			Dim unid As String
			status= words(0)
			unid= words(1)
			Dim odoc As NotesDocument
			
			On Error Goto oh_dear
			
			Set odoc= db.GetDocumentByUnid(unid)
			
			On Error Goto 0
			
			If Not odoc Is Nothing Then
				odoc.request_status= status
'send email back to requestor				
				Dim maildoc As New NotesDocument (db)
				Call odoc.CopyAllItems(maildoc,True)
				maildoc.Form = "MailRequest"
				maildoc.principal = "Request Database"
				maildoc.from = "Request Database"
				maildoc.subject = "Your Request Has Been " & status
				Call maildoc.send(True, "me@me.com")
				
				Call odoc.Save(True, False)
			End If
		End If
		
nextdoc:  
		Set doc=view.GetNextDocument(doc)
	Wend
	Exit Sub
	
oh_dear:
	Messagebox Error$ & " on line " & Erl
	Resume nextdoc
	
End Sub

Open in new window

0
Sjef BosmanGroupware ConsultantCommented:
Potential problems:
- line 15: if doc doesn't contain a field called Subject, what then?
- line 25: you could verify that unid is a more or less valid unique id: it needs to be a hex-string of 32 characters (could be done with Len(unid)=32 And unid Like "[0-9A-Fa-f]*"
- line 40: the Save could fail, due to lack of access rights

I don't see a lot of potential pitfalls in the rest of the code, but I may need some extra eyes as well...
0
shuboarderAuthor Commented:
Thanks for the suggestions...

The agent is not running correctly on a schedule.
If I run manually I get the following error message:

Notes error: Entry not found in index (All Mail In) <-- This is the view AMI the agent runs through

on pressing OK the agent appears to complete successfully.

Any ideas?
0
shuboarderAuthor Commented:
I think you may be right about line 25. I don't think the current error trapping is working.
0
shuboarderAuthor Commented:
I've noticed when running manually, it will process only 2 documents before showing the error.
It's not only odd, but really annoying!
0
shuboarderAuthor Commented:
Ok.... it's now running on schedule but in exactly the same way. It will process 1 or 2 documents only each time it runs. For some reason it appears to be terminating early.

Second/Third pairs of eyes welcome!
0
Sjef BosmanGroupware ConsultantCommented:
Sorry I left you on your own... Even freelancers do have to work sometimes...

Could you exactly state the nature of the agent, what is it triggered by, etc.

Could you also paste the (majority of) the code, for them third pair of eyes that are watching?
0
shuboarderAuthor Commented:
The agent is a scheduled agent set to run every 20 minutes.

The whole current code can be seen in the attached code snippet.

Thanks for your help!
Sub Initialize
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim View As NotesView
	Dim doc As NotesDocument
	
	Set db=session.CurrentDatabase
	Set view = db.GetView("AMI")
	Set doc=view.GetFirstDocument
	
	While Not doc Is Nothing
		
		Dim words As Variant
		
		words= Split(doc.Subject(0), " - ")
		If Ubound(words)=1 Then ' subject was okay
			Dim status As String
			Dim unid As String
			status= words(0)
			unid= words(1)
			
			Dim odoc As NotesDocument
			
			On Error Goto oh_dear
			
			Set odoc= db.GetDocumentByUnid(unid)
			
			On Error Goto nextdoc
			'On Error Goto 0
			
			If Not odoc Is Nothing Then
				odoc.request_status= status
'send email back to requestor				
				Dim maildoc As New NotesDocument (db)
				Call odoc.CopyAllItems(maildoc,True)
				maildoc.Form = "MailRequest"
				maildoc.principal = "Request Database"
				maildoc.from = "Request Database"
				maildoc.subject = "Your Request Has Been " & status
					
				Call maildoc.send(True, "me@me.com")
				
				Call odoc.Save(True, False)
				doc.processed="Yes"
				Call doc.Save(True,False)
				Goto nextdoc
			End If
		End If
		
nextdoc:  
		Set doc=view.GetNextDocument(doc)
	Wend
	Exit Sub
	
oh_dear:
	Messagebox Error$ & " on line " & Erl
	Resume nextdoc
	
End Sub

Open in new window

0
Sjef BosmanGroupware ConsultantCommented:
Maybe it's the "standard" error every Notes developer has to make. I suppose your view selects documents with a condition like Processed!="Yes"

Assuming that's true, what happens? Your code opens the view, sees the first document, treats it, sets the Processed-field and moves on to the next one. You think, because that's not what happens. It's more like this: your code opens the view, sees the first document, treats it, sets the Processed-field which effectively moves the document out of the view (!) and moves on to the next one., but there isn't any, or the code crashes (which is more likely).

Two possible ways to address this problem:
- line 8a: view.AutoUpdate= False
or
- line 5a: Dim ndoc As NotesDocument
- line 11a: Set ndoc=view.GetNextDocument(doc)
- line 51: Set doc= ndoc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shuboarderAuthor Commented:
Thanks ever so much! Works perfectly now!
0
Sjef BosmanGroupware ConsultantCommented:
Aaaah, problem solved! I like that! :-))
0
shuboarderAuthor Commented:
That problem is, but I have another problem related to the same agent!

New Question:
Q_23232439.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.