Is This Possible ???

Hello All,
First I'm seeing if this is possible.  If so I will upgrade the points to well over 500 if it can be done.  I have a database form that generates emails to a mailin database when the form is "submitted".  This form requires some course of action that the doc outlines to be done, elsewhere but is documented in this form.  So.
1.)  the user fills out the form and "submits"
2.)  An email is generated and sent to a mailing database as a parent entry with a doclink to document.
3.)  a member of the mailin database opens the email from the database and reviews what actions are outlined in the doc.
4.)  that member then clicks an assign  button in the form tht changes the status of the doc and sends antother email to the mailin database.
Question:  Is there a way that all future emails to the mainin database can be child relations to the original parent email in the mailin database.??  So that if looking in the database you would see in a view an original email and then under that all associated emails like a parent child relationship.  And of course all emails would have that doclink back to the document in the other database ?!?!?!?!
hope this makes sense.
LVL 4
pratiganAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

cbcliCommented:
I would think that this can be done by making the original form a Document and the replies to come in and use the Response form type. That way all of them would be in a hierarchy to the original and then you could even use another criteria to have a third teir under the responses by using a 3 rd form of the type "Response to Response"
Just tag and screen the documents as they go in and out - as to which form type they will use.  
0
Sjef BosmanGroupware ConsultantCommented:
Yes, that's the theory, but in this case it's a little different, for documents are not created in the database but they are received. By the way, Response-to-Response is fake; internally there are only two possibilities: a document is a main document, or it is a response. The three types in a form serve only to determine the parent when the document is first saved.

Different suggestion:
- create an agent that runs on Before mail is received (runs immediately, always on one mail)
- the agent checks if the mail is a response to an earlier mail (how exactly is your task)
- if a parent document exists, attach the current mail to the parent
- no need to Save, the mail is yet to be saved after the agent has finished

How do you know a mail is in fact a response mail? Do you keep unique mail codes, and if so, how does the originator know those codes?
0
pratiganAuthor Commented:
Thank You for the feedback.  Hopefully this is easier to do than it sounds...lol

currently today in the mailin database, a techician will open an email and then reply to it with whatever response or notification he's doing.  Since he opened the original email in the mailin database and replied to it then the response automatically stores under the original in the mailin database view.  This is what I am trying to duplicate but the original email in the mainin database is the only email coming from my automated database today.  The technicians want to open the original email from the mailin database and click on the doclink that takes them to my database and then there is new logic added that updates status' and response codes etc through the use of a button clisk in the origianl doc in my database.  They want an automated email to go back to the mailin database as a child or Response to the original email.  Does this make sense ??

0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

pratiganAuthor Commented:
as some additional information, each document has a numbering process.  therefore everytime one is created it takes the next number in order, thereby giving each doc a unique identifier.  This doc as the doclink in the original email and each response to email will have this unique number.  could notes possibly use that field to check for all emails with a particular number and group them ?? and group them by original parent and all subsequent responses ??
0
Sjef BosmanGroupware ConsultantCommented:
I have to re-read this tomorrow, it doesn't make no sense to me right now... Not your fault probably. :-)
0
pratiganAuthor Commented:
this is still in progress.  Sjef was going to get back to me as well.  I'm trying to get a sense for the size of the work effort involved here.
0
Sjef BosmanGroupware ConsultantCommented:
Okay, let me get this straight:
- mail O is received in the mail-in db, with a key
- response mail is also received in the mail-in db, with a parent key
- you want to create a document hierarchy for the mails with the same (parent) key, i.e for the parent and al the responses
Is that it?

If so
- create on agent in Before mail is received
- check if the mail is a parent or a response document
- if it's a parent, leave the agent
- if it's a response, locate the parent document and attach this mail as a response to the parent (MakeResponse)
- do NOT save, it will happen automatically
0
pratiganAuthor Commented:
ok so this sounds very feasible to do. sounds like the agent exists on the mailin database side and there are ways to flag an email as a parent or response.
Ths beig said... I'm going to increase the points significantly and get more into details on going about this.  I will be looking for further assistance on piecing this together so I'll make it worthwhile.
Thank you for all your input.  It is very much appreciated.  Good to know there is a place to go for very valueable assistance.
0
pratiganAuthor Commented:
If you feel there is a better way, please let me know.
0
pratiganAuthor Commented:
I have increased the points for this question.  I can give another full explanation of what I'm trying to do if needed.  I'm going to be doing some further research over the next few days to get this off the ground.
Thank You !
0
pratiganAuthor Commented:
Hi Sjef,
This sounds very straight forward.
I'm going to be coding this agent starting tomorrow.  I will let you know how I'm doing.
How do you recommend I pass the parent / response keys to the mailin DB ?
0
pratiganAuthor Commented:
how do I check the email to determine if its a parent or a response. ??  I have an agent created but can't determine how to anaylze the incoming email for parent vs response. ??
0
Sjef BosmanGroupware ConsultantCommented:
You could try to add some info (fields) to the mail, so that you know where the content should be attached. With "know" I mean that you can do some lookups in views (DbLookup or NotesView.GetDocumentByKey), and then you use NotesDocument.MakeResponse to attach it somewhere.
0
pratiganAuthor Commented:
ok. so I'm going to assign a doc key for the original doc email and then the agent that runs on the mailin DB takes the incoming email and takes its key and does a lookup on a view in the mailin DB looking for the key in each doc in the view.  If none then this incoming email is considered an original (parent) if it find the key then I make the incoming email a response to the doc found.   it sounds like this is going to have to be done usnig lotus script.  lol.... not my strong point.  
0
pratiganAuthor Commented:
I've found some new mail agents script that I'm working with now.
Sub Initialize
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim coll As NotesDocumentCollection
      Dim searchString As String
      Dim searchDate As NotesDateTime
      Dim folder As NotesView
      Dim folderContents As NotesViewEntryCollection
     
      Set db=session.CurrentDatabase
      Set folder=db.GetView("Search Results")
     
      REM Clear search results folder first
      Set folderContents=folder.AllEntries
      Call foldercontents.RemoveAllFromFolder("Search Results")
     
      REM Lookup new results and put them in the folder
      Set searchDate=New NotesDateTime("01/01/1900")
      searchString=Inputbox ( "Enter search string", "Search mail")
     
      searchString={@Contains(From; "} + searchString + {")}
      Set coll=db.Search(searchString, searchDate, 0)
      Call coll.PutAllInFolder("Search Results")
      Messagebox ("Find results in 'Search Results' folder")
End Sub

going to change the search to the subject field which will contain the key I want to use.  Then I need to write the logic to search the mailin DB view looking for that key.
0
Sjef BosmanGroupware ConsultantCommented:
You lost me... Your first comment today I understand, but how it relates to the second comment I fail to see.

Why don't you write pseudo-code first, so we can see what you actually want?
0
pratiganAuthor Commented:
I was going to search the subject field on the new incoming emails because that subject contains the document number which I will use as the key.  I would then search the existing docs in a mailin view to match the key, if not then its a new original parent doc, if the first occurance is found then I would response the incoming email.
0
Sjef BosmanGroupware ConsultantCommented:
Here's some code of an agent that runs automatically when new mail arrives, on each mail separately. It's just an example that shows how a yet unsaved mail can be dealt with.

Maybe it helps you to develop your own agent. Once again: the agent runs on When new mail is received, is not scheduled or anything else. It runs inside the mail-in db, on any incoming mail.

Hope it helps!

Sub Initialize
	Dim ns As New NotesSession
	Dim ndc As NotesDocumentCollection
	Dim doc As NotesDocument
	Dim skip As Integer
	
	On Error Goto oops
	Set doc= ns.DocumentContext
	skip= doc.SentByAgent
	If debug Then Msgbox "HandleIncomingMail: skip=" & skip
	If doc.HasItem("SMTPOriginator") Then
		skip= Not doc.SMTPOriginator(0) Like "*@*acme*"  ' handle only acme mail
	End If
	If debug Then Msgbox "HandleIncomingMail: skip=" & skip
	If Not skip Then 
		Call HandleMail(doc)			
	End If
	Exit Sub
oops:
	Msgbox "ChronOddo,line " & Erl & ": " & Error$
	Exit Sub
End Sub

Sub HandleMail(doc As NotesDocument)
	Dim user As String
	Dim manager As String
	Dim roles As String
	Dim item As NotesItem
	
	user= doc.From(0)
	manager= doc.Principal(0)
	If manager="" Then manager= user
'			Msgbox "User: " & user
	roles= ""
	Forall r In aclroles
		If manager=Listtag(r) Or nab.IsAMemberOf(manager, Listtag(r)) Then
			If roles<>"" Then roles= roles + ";"
			roles= roles + Join(r, ";")
		End If
	End Forall
	If roles="" Then roles= "[Inconnu]"
	roles= user + ";" + manager + ";" + roles + ";[Manager]"
	If debug Then Msgbox "HandleMail: Roles= " & roles
	Set item= New NotesItem(doc, "Readers", Arrayunique(Split(roles, ";")), READERS)
	Call doc.ReplaceItemValue("Reference", GetRef(db,manager))
	Call RemoveAttachments(doc)
	Call doc.Save(True, False)
	Call SendReply(doc, manager, user)
End Sub

Open in new window

0
pratiganAuthor Commented:
wow...  ok...
0
pratiganAuthor Commented:
ok Here is first round draft.
I check the incoming email 2x first i check for the subject to begin with a DCRsub then if thats true then checks for the full number valued key against a categorized view in the mailin db that is categorized by the DCRkey (includes unique number).

I have mixed code with english to help understand the concept attempting.

Sub Initialize
      Dim s As New NotesSession
      Dim doc As NotesDocument
      Dim it  As NotesItem
      Dim DCRsub As String
Dim DCRkey As String
      DCRsub = "[DCRENV DCR ]"      ' this is the first test identifier
      DCRkey = “[DCRENV DCR nnnn]” ‘if first test is successful then test for this… nnnn is a 4 digit number unique to send doc
      Set doc = s.documentcontext
      ' REMOVE the print statements after testing !!!!!
      Print "Processing " & doc.subject(0) " from " & doc.From(0)
      Set it = doc.GetFirstItem("Subject")
      If it does not begin with DCRsub Then
            EXIT
     
Dblookup (server:db; mailin db keyview
If dblookup error, exit  (parent doc, no match  for response)
Else
Set incoming email as response      
Exit
End if
         
End Sub
0
pratiganAuthor Commented:
am I on the right page here ????
sorry but my script experience is limited,
0
Sjef BosmanGroupware ConsultantCommented:
Seems ok to me. Do some step-by-step development, add or modify a few lines at a time and test, then do some more things and test again. BTW, the output of the Print you'll see in the server's log database.
0
pratiganAuthor Commented:
@If(@Left(Subject;10) = "DCRENV DCR";

@Do(
Field DCRkey := @Left(Subject;15);
FIELD match := @DbLookup("":"ReCache";server1:database1;DCRview;Subject;Subject;DCRkey);
@IfError(match;@Return(""));
@IsResponseDoc);"")

I've been looking at this in a different way.  Since my script skills are terrible, I've decided to code this in formula code.
I created a view in the mailin DB called DCRview which is sorted acending by the subject field.
I've created a key from the incoming emailwhich will contain DCRENV DCR nnnn where nnnn is a unique number of the incoming email doclink.

This logic first checks the incoming email to see if it even begins with DCRENV DCR.  if not it stops.
If it does, then I create the key and do a dblookup on the mailin db for a match.
If no match I return nothing and the email is set as an original (parent)  if there is a match then I set the incoming email as @IsResponse to the matched lookup.

correct ????
0
Sjef BosmanGroupware ConsultantCommented:
So you think you're stronger than Domino? You "decided" that it'll be formula language? So sorry to wake you up out of a nice dream, but that ain't gonna happen here. Forget it, for you don't have the tools to do what you want. Don't get me wrong, it might be possible with a load of code and several additional agents or so. In any case, I'm not going to help you there, although I might learn something from it I'm convinced that it is note the way to go and I have lots of other things to do. Plus, don't you think it's about time you learn some LotusScript??

Dblookup (server:db; mailin db keyview
If dblookup error, exit  (parent doc, no match  for response)
Else
Set incoming email as response      
Exit
End if
         
That's about equivalent to:

Set view= db.GetView("something")
Set pdoc= view.GetDocumentByKey("key")
If Not doc Is Nothing Then
      Call doc.MakeResponse(pdoc) ' or the other way round... check the Help db
End If
0
pratiganAuthor Commented:
Hello Sjef,
I didn't mean to avoid doing it correctly or cut corners anywhere.  I'm merely trying to utilize the tools that I have that are strongest to achieve the outcome.  I understand your busy and I'm sorry your getting frustrated.  Thank You for all your help.
I will take your advise and go back to using script.
thx
0
pratiganAuthor Commented:
Sub Initialize
      Dim s As New NotesSession
      Dim doc As NotesDocument
      Dim it  As NotesItem
      Dim subit As String
      Dim DCRsub As String
      Dim DCRkey As String
      Set DCRsub = "[DCRENV DCR]"      ' this is the first test identifier
      Set doc = s.documentcontext
      Set it = doc.GetFirstItem("Subject")
      Set subit = Left$("it",10)
      
      If subit <> DCRsub Then Exit Sub           '(not valid type of email to check)
      
      Set DCRkey = Left$("it",15)                   '(if first test is successful then test for this. The subject with a 4 digit number after subit.)
      Set view= db.GetView("DCRview")
      Set pdoc= view.GetDocumentByKey("DCRkey")
            
      If Not doc Is Nothing Then
           Call doc.MakeResponse(pdoc)
      End If
            
End Sub

0
pratiganAuthor Commented:
Added database pieces:

Sub Initialize
      Dim s As New NotesSession
      Dim doc As NotesDocument
      Dim db As NotesDatabase
      Dim it  As NotesItem
      Dim subit As String
      Dim DCRsub As String
      Dim DCRkey As String
      Set DCRsub = "[DCRENV DCR]"      ' this is the first test identifier
      Set doc = s.documentcontext
      Set it = doc.GetFirstItem("Subject")
      Set subit = Left$("it",10)
      Set db = s.GetDatabase("AMER-AP10", "Discuss\\emednydb.nsf")
      
      If subit <> DCRsub Then Exit Sub           '(not valid type of email to check)
      
      Set DCRkey = Left$("it",15)                   '(if first test is successful then test for this. The subject with a 4 digit number after subit.)
      Set view= db.GetView("DCRview")
      Set pdoc= view.GetDocumentByKey("DCRkey")
            
      If Not doc Is Nothing Then
                            Call doc.MakeResponse(pdoc)
      End If
            
End Sub
0
pratiganAuthor Commented:
following the flow I see the database setup and now type mismatch on statement.
Call doc.MakeResponse(pdoc)
0
pratiganAuthor Commented:
ok... I've made some additional changes to doc and location of database Set.  still getting type mismatch...

Sub Initialize
      Dim s As New NotesSession
      Dim doc As NotesDocument
      Dim db As NotesDatabase
      Dim it  As NotesItem
      Dim subit As String
      Dim DCRsub As String
      Dim DCRkey As String
      Set DCRsub = "[DCRENV DCR]"      ' this is the first test identifier
      Set db = s.GetDatabase("AMER-AP10", "Discuss\\emednydb.nsf")
      Set doc = New NotesDocument( db )
      Set it = doc.GetFirstItem("Subject")
      Set subit = Left$("it",10)
      
      
      If subit <> DCRsub Then Exit Sub           '(not valid type of email to check)
      
      Set DCRkey = Left$("it",15)                   '(if first test is successful then test for this. The subject with a 4 digit number after subit.)
      Set view= db.GetView("DCRview")
      Set pdoc= view.GetDocumentByKey("DCRkey")
      
      If Not doc Is Nothing Then
            Call doc.MakeResponse(pdoc)
      End If
      
End Sub
0
Sjef BosmanGroupware ConsultantCommented:
Now you got me wrong, I didn't meant be seem frustrated or so.  I just wanted to say that Domino doesn't have the Formula equivalents you need to get this thing done. Your statement was firm, but it won't help you. ;-)

Some corrections in your code:
- Set can only be used on objects
- the values of a NotesItem object can be found in the Values() array

Sub Initialize
      Dim s As New NotesSession
      Dim doc As NotesDocument
      Dim db As NotesDatabase
      Dim it  As NotesItem
      Dim subit As String
      Dim DCRsub As String
      Dim DCRkey As String
     
      DCRsub = "[DCRENV DCR]"      ' this is the first test identifier
      Set db = s.GetDatabase("AMER-AP10", "Discuss\\emednydb.nsf")
      Set doc = New NotesDocument( db )
      Set it = doc.GetFirstItem("Subject")
      subit = Left$(it.Values(0),10)
     
     
      If subit <> DCRsub Then Exit Sub           '(not valid type of email to check)
     
      DCRkey = Left$(it.Values(0),15)                   '(if first test is successful then test for this. The subject with a 4 digit number after subit.)
      Set view= db.GetView("DCRview")
      Set pdoc= view.GetDocumentByKey(DCRkey, True)
     
      If Not doc Is Nothing Then
            Call doc.MakeResponse(pdoc)
      End If
     
End Sub
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
pratiganAuthor Commented:
Thank You for the updates and insight.  I tend to vere away from using script when possible, but I will definitely start taking a closer look at it and working more with it.

I see the values concept and of course the Set is straight forward.

I am still getting a type mismatch on pdoc.
Call doc.MakeResponse(pdoc)

Sub Initialize
      Dim s As New NotesSession
      Dim doc As NotesDocument
      Dim currentdoc As NotesDocument
      Dim db As NotesDatabase
      Dim it  As NotesItem
      Dim subit As String
      Dim DCRsub As String
      Dim DCRkey As String
      Set currentdoc = Source.Document
      DCRsub = "[DCRENV DCR]"      ' this is the first test identifier
      Set db = s.GetDatabase("AMER-AP10", "Discuss\\emednydb.nsf")
      Set doc = New NotesDocument( db )
      Set it = currentdoc.GetFirstItem("Subject")
      subit = Left$(it.Values(0),10)
      
      
      If subit <> DCRsub Then Exit Sub           '(not valid type of email to check)
      
      DCRkey = Left$(it.Values(0),15)                    '(if first test is successful then test for this. The subject with a 4 digit number after subit.)
      Set view= db.GetView("DCRview")
      Set pdoc= view.GetDocumentByKey("DCRkey")
      
      
      If Not doc Is Nothing Then
            Call doc.MakeResponse(pdoc)
      End If
      
End Sub

I've added a currentdoc to the script to designate the difference between the current incoming email doc and the parentdoc (pdoc)
but still gtting that mismatch error.
0
pratiganAuthor Commented:
ok I think I'm there.
I realized that I didn't Dim pdoc and I adjusted the doc currentdoc

Sub Initialize
      Dim s As New NotesSession
      Dim pdoc As NotesDocument
      Dim currentdoc As NotesDocument
      Dim db As NotesDatabase
      Dim it  As NotesItem
      Dim subit As String
      Dim DCRsub As String
      Dim DCRkey As String
      Set currentdoc = Source.Document
      DCRsub = "[DCRENV DCR]"      ' this is the first test identifier
      Set db = s.GetDatabase("AMER-AP10", "Discuss\\emednydb.nsf")
'      Set doc = New NotesDocument( db )
      Set it = currentdoc.GetFirstItem("Subject")
      subit = Left$(it.Values(0),10)
      
      
      If subit <> DCRsub Then Exit Sub           '(not valid type of email to check)
      
      DCRkey = Left$(it.Values(0),15)                    '(if first test is successful then test for this. The subject with a 4 digit number after subit.)
      Set view= db.GetView("DCRview")
      Set pdoc= view.GetDocumentByKey("DCRkey")
      
      
      If Not doc Is Nothing Then
            Call currentdoc.MakeResponse(pdoc)
      End If
      
End Sub
0
Sjef BosmanGroupware ConsultantCommented:
You pinched that line with source somewhere! It is wrong, it doesn't belong in an agent running before mail has arrived. I didn't notice last time, I did only a syntax-check.

This is the line you need:
      Set currentdoc = s.documentcontext

Come to think of it: where does all that code come from, and what is it doing there??

I still suppose that what you want is this:
- the agent runs inside a mail-in database
- the agent catches a mail before it is saved
- the agent finds a parent document inside the mail-in database
- if found, it attaches the mail to the intended parent document.

If that's all true, why the GetDatabase, and why the New Notesdocument??
0
pratiganAuthor Commented:
that is correct.. attach to the parent as a response doc if found.
I figured I had to distinguish the database, but now that I think about it, I'm already in that database.
I figured the New Notesdocument as this email was new.  I do now see the point.

How does this look.  It makes perfect sense now.

Sub Initialize
      Dim s As New NotesSession
      Dim pdoc As NotesDocument
      Dim currentdoc As NotesDocument
      Dim db As NotesDatabase
      Dim it  As NotesItem
      Dim subit As String
      Dim DCRsub As String
      Dim DCRkey As String
      Set currentdoc = s.documentcontext
      DCRsub = "[DCRENV DCR]"      ' this is the first test identifier
      
      Set it = currentdoc.GetFirstItem("Subject")
      subit = Left$(it.Values(0),10)
      
      If subit <> DCRsub Then Exit Sub           '(not valid type of email to check)
      
      DCRkey = Left$(it.Values(0),15)                    '(if first test is successful then test for this. The subject with a 4 digit number after subit.)
      Set view= db.GetView("DCRview")
      Set pdoc= view.GetDocumentByKey("DCRkey")
      
      If Not doc Is Nothing Then
            Call currentdoc.MakeResponse(pdoc)
      End If
      
End Sub
0
Sjef BosmanGroupware ConsultantCommented:
Almost correct, I think, except for this line:

      Set pdoc= view.GetDocumentByKey("DCRkey")

If you write "DCRkey", you specify a constant string, so you're always looking in the view for the same document with the key "DCRkey". Instead of the string constant, use the variable DCRkey, and also specify the second parameter (to force an exact match). The result:

      Set pdoc= view.GetDocumentByKey(DCRkey, True)
0
pratiganAuthor Commented:
Got It !!
Thank You Sjef.... I will be testing this week, I'll keep you posted on the testing results.
The uer is out of the office for the week so I won't be able to get signoff until after the Holiday.
I'll keep you posted !!
Thank You Again for the Assistance and the Education !!
0
Sjef BosmanGroupware ConsultantCommented:
:-) You're welcome!
0
pratiganAuthor Commented:
preliminary testing went well.
I won't know for sure until the changes are moved to the production server.
I'll keep you posted.

Thank You Very Much for your Help !!
paul
0
pratiganAuthor Commented:
Excellent Resource.  
0
Sjef BosmanGroupware ConsultantCommented:
You see, it wasn't that hard after all. Well done!
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.