[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Editing an agent that sends Newsletter

Experts,

I have the following code that will send a newsletter email, based on what is in the Interest Profile (taken from a tutorial book titled Notes 6 Projects).  This works great -- and it uses the fields from one document, but I want the agent to search on other fields in other documents and return one newsletter with all docs that fit the criteria in the fields in the Interest Profile.  So, I want to know where in the code I have to adjust to add more fields from other documents.  I don't see a reference to a form, so I think this is doable, so long as I add the new fields in the Interest Profle -- correct?

Any suggestions? (I want to add like 6 more fields from two other documents.)

Thanks in advance,
AV

Start
===================================================

Sub Initialize
      'Declaration
      Dim ns As New NotesSession
      Dim db As NotesDatabase
      Dim ProfileView As NotesView
      Dim ProfileDoc As NotesDocument
      Dim NotesDoc As NotesDocument
      Dim NewsLet As NotesNewsletter
      Dim DocCollection As NotesDocumentCollection
      
      Dim ByAuthor As Variant            
      Dim ByCategory As Variant
      Dim BySubject As Variant
      Dim ByClassification As Variant
      Dim ByLocation As Variant
      
      'Declaration of variable, to form a formatted string for search query      
      Dim strAuthor As String      
      Dim strCategories As String
      Dim strSubject As String
      Dim strClassification As String
      Dim strLocation As String      
      Dim IsCriteriaNull As Variant      
      Dim SearchQry As String
      
      'Initialization
      Set db = ns.CurrentDatabase
      Set ProfileView = db.GetView("(InterestProfile)")
      Set ProfileDoc = ProfileView.GetFirstDocument
      
      If ProfileDoc Is Nothing Then Exit Sub      'if no user profiles then quit the process
      
      Do While Not (ProfileDoc Is Nothing)
            If db.IsFTIndexed = True Then            'If Db is Full text Index do Ft search                  
                  If Trim(ProfileDoc.CreatedByDiscuss(0)) <> "" Then
                        ByAuthor = ProfileDoc.CreatedByDiscuss      
                        strAuthor = ""                  
                        Forall value In ByAuthor            'Iterate through the Values in the Authors field
                              If Trim(strAuthor) = "" Then
                                    strAuthor =  value
                              Else
                                    strAuthor = strAuthor & "," & value
                              End If
                        End Forall
                        SearchQry = "field CreatedByDiscuss contains " & strAuthor
                  End If
                  
                  If Trim(ProfileDoc.CategoryDiscuss(0)) <> "" Then
                        ByCategory = ProfileDoc.CategoryDiscuss
                        strCategories = ""
                        Forall value In ByCategory
                              If Trim(strCategories) = "" Then
                                    strCategories =  value
                              Else
                                    strCategories = strCategories & "," & value
                              End If
                        End Forall
                        qry = "field CategoryDiscuss contains (" & strCategories & ")"
                        If Trim(SearchQry) = "" Then
                              SearchQry = qry
                        Else
                              SearchQry = SearchQry & " Or " & qry
                        End If      
                  End If
                  
                  If Trim(ProfileDoc.SubjectDiscuss(0)) <> "" Then
                        BySubject = ProfileDoc.SubjectDiscuss
                        strSubject = ""
                        Forall value In BySubject
                              If Trim(strSubject) = "" Then
                                    strSubject =  value
                              Else
                                    strSubject = strSubject & "," & value
                              End If
                        End Forall
                        qry  = "field SubjectDiscuss contains (" & strSubject & ")"
                        If Trim(SearchQry) = "" Then
                              SearchQry = qry
                        Else
                              SearchQry = SearchQry & " Or " & qry
                        End If      
                  End If
                  
                  If Trim(ProfileDoc.ClassificationDiscuss(0)) <> "" Then
                        ByClassification = ProfileDoc.ClassificationDiscuss
                        strClassification = ""
                        Forall value In ByClassification
                              If Trim(strClassification) = "" Then
                                    strClassification =  value
                              Else
                                    strClassification = strClassification & "," & value
                              End If
                        End Forall
                        qry = "field ClassificationDiscuss contains (" & strClassification & ")"
                        If Trim(SearchQry) = "" Then
                              SearchQry = qry
                        Else
                              SearchQry = SearchQry & " Or " & qry
                        End If      
                  End If
                  
                  If Trim(ProfileDoc.LocationDiscuss(0)) <> "" Then
                        ByLocation = ProfileDoc.LocationDiscuss
                        strLocation = ""
                        Forall value In ByLocation
                              If Trim(strLocation) = "" Then
                                    strLocation =  value
                              Else
                                    strLocation = strLocation & "," & value
                              End If
                        End Forall
                        qry = "field LocationDiscuss contains (" & strLocation & ")"
                        If Trim(SearchQry) = "" Then
                              SearchQry = qry
                        Else
                              SearchQry = SearchQry & " Or " & qry
                        End If      
                  End If      
                  
                  If Trim(SearchQry) <> "" Then
                        SearchQry = SearchQry & " and (not(field Form contains InterestProfile))"
                        Print SearchQry
                        Set DocCollection = db.UnprocessedFTSearch(SearchQry, 0)
                  End If
            Else            'Formula Search code starts from here................
                  
                  IsCriteriaNull = True            'Intialize it to True assumes the Search Criteria is null
                  If Trim(ProfileDoc.CreatedByDiscuss(0)) <> "" Then
                        IsCriteriaNull = False      'Flag set to false, i.e Search Criteria is not Null, to allow the search as some criteria is available to carry on search
                        ByAuthor = ProfileDoc.CreatedByDiscuss
                        strAuthor = ""                  're-intializing
                        Forall value In ByAuthor
                              st = """" & value & """"      'Formating of the string such as it would be Enclosed by a Double quotes e.g "Sashi"
                              If Trim(strAuthor) = "" Then
                                    strAuthor =  st
                              Else
                                    strAuthor = strAuthor & ":" & st      'Formating the string to create a Stringlist delimited with Semi colon : e.g "Sashi":"Sanjeev":"Praveen"
                              End If
                        End Forall
                  End If
                  
                  If Trim(ProfileDoc.CategoryDiscuss(0)) <> "" Then
                        IsCriteriaNull = False
                        ByCategory = ProfileDoc.CategoryDiscuss
                        strCategories = ""
                        Forall value In ByCategory
                              st = """" & value & """"
                              If Trim(strCategories) = "" Then
                                    strCategories =  st
                              Else
                                    strCategories = strCategories & ":" & st
                              End If
                        End Forall
                  End If
=========================================
End
0
AliciaVee
Asked:
AliciaVee
  • 2
  • 2
  • 2
2 Solutions
 
marilyngCommented:
Hi AliciaV,

I'll preface with, I can be wrong...

When you're creating a query, you phrase it like a sentence, which is what your code above seems to be doing..
{Select documents where the form is "Contacts" and the author is "Mike" and the color is "Green"}

If the other two forms have nothing in common with the first form, that is, they cannot be linked by "author", "form" or color, then you have to do a separate query and create second and third collections.

So, without really knowing the construct of your database,

Collection1 = those documents returned from the first query,
collection2 = those document returned by the second query,
collection3 =those documents returned by the third query.

Format the newsletter header information.
Instantiate the richtextfield
-branch to a sub that will cycle through and add the document results from the collection 1 to the richtextfield
 if collection1.count >0 then
    set doc = collection1.getfirstdocument
     while not doc is nothing
           with doc
              rtitem.appendText(doc.somefield(0))
              'etc...
            end with
        set doc = collection1.getnextdocument(doc)
  end if

if collection2.count >0 then
    set doc = collection2.getfirstdocument
     while not doc is nothing
           with doc
              rtitem.appendText(doc.somefield(0))
              'etc...
            end with
        set doc = collection1.getnextdocument(doc)
  end if

and so on.

----------------------------------
Your code doesn't show what the eventual call for the search looks like or what it returns, or how it's building the search string.  But the search string being built is a good generic one that can be adapted to suite your means.

You probably need to split your code into a script library, and let a function return the collections, so all you have to do is pass the changing variables to the same code...

function coll(thisSession as NotesSession, thisDB as NotesDatabase, someKeys as variant) as NotesDocumentCollection
   'put all your collection gathering here...


Does this make sense?
0
 
Sjef BosmanGroupware ConsultantCommented:
Hi Alicia,

> I want the agent to search on other fields in other documents
Please explain. The fields in the code right now, they are existing fields in your application?
And don't make the classical mistake to go for a solution that doesn't necessarily solve your problem. Please analyse first, and then design and code. The Interest Profile could really be a very good thing, but we cannot see that from here. No offence...

Can you describe, in your own words, what you want the agent to look for? E.g. I want only forms with the name "Beverage" and for the rest the user can select zero or more fields from {Quantity, Colour, Sweet, Alcohol} with a specific value.

Cheers!
0
 
AliciaVeeAuthor Commented:
marilyng,

Yes, your comments do make sense, and I agree that this code should be split. I get what you are saying - let me look closer at it and see what I can figure out.

sjef,

The database has a discussion board, and the interest profile uses fields in the form that are used to post comments (these equal to 3 forms, main topic, response, and response to response).  This database also has other forms that will hold other data for reference (like a document library), so if a users creates an interest profile and chooses to be notified whenever a document of category "Education" or category "Mentoring" or category "Volunteering" is posted, I'd also like the newsletter to provide other documents that may have the same categories defined.  Is that possible?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Sjef BosmanGroupware ConsultantCommented:
Ah, I get it. But isn't that more or less the same as a Database Subscription that's already present in Notes?

Look in the Designer Help database, the Index, under Subscriptions: Enabling Subscriptions
0
 
marilyngCommented:
Well, of course.  Duh. forgot about those.
0
 
AliciaVeeAuthor Commented:
marilyng and sjef,

Sorry I haven't been back here to close this post out.  I did finish what I needed with the help of both of your input.  Funny, Help has a lot of information, but sometimes I don't know how to search for it.  It is very senstive to what you type and I get a lot of invalid searches.  Maybe I should use the Index more often!  Good idea.

Thanks again for all of your help.
AliciaV
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now