Sorting data by state using LotusScript

Posted on 2007-08-07
Last Modified: 2013-12-18
I am retrieving data from the address book to sort and export to .xls.
I need to separate the data by state. I got it done, but I had to keep changing my search criteria manually. Is there anyway to have the users select from a list of search criteria or enter the criteria on the fly? Here is an excerpt of the code:

I have to comment and uncomment
each line in order to do each search.

'searchFormula$ = |Form = "Person" & @contains(OfficeState;"DC")|
'searchFormula$ = |Form = "Person" & @contains(OfficeState;"FL")|
'searchFormula$ = |Form = "Person" & @contains(OfficeState;"MD")|
'searchFormula$ = |Form = "Person" & @contains(OfficeState;"NC")|
searchFormula$ = |Form = "Person" & @contains(OfficeState;"")|
Set collection = dbx.Search(searchFormula$,dateTime,0)
While Not ( docx Is Nothing )
With docx
Print #fileNum%, ALIASCOMM & .InternetAddress(0) & FEmail(docx) & S & FCorreo(docx)&CONFIRMCOMM & CR
End With
Set docx = collection.GetNextDocument( docx )
Question by:KTTKTT
    LVL 22

    Expert Comment

    Sure, just prompt the user for the state using NotesUiWorkspace.Prompt.  Here is an example:

          Dim ws As New NotesUiWorkspace()
          Dim dbx As NotesDatabase
          Dim collection As NotesDocumentCollection
          Dim dateTime As NotesDateTime
          Dim vResult As Variant
          Dim arrChoices(4) As String
          Dim strState As String
          Dim searchFormula As String
          arrChoices(0) = "DL"
          arrChoices(1) = "FL"
          arrChoices(2) = "MD"
          arrChoices(3) = "NC"
          arrChoices(4) = "-- All States --"
          strState = ws.Prompt(PROMPT_OKCANCELLIST, "State", "Select a state from the list:", "-- All States --", arrChoices)
          If (strState = "") Then Exit Sub
          Select Case strState
          Case ""
                Exit Sub
          Case "-- All States --"
                searchFormula$ = |(Form = "Person")|
          Case Else
                searchFormula$ = |(Form = "Person") & @Contains(OfficeState;"| & strState & |")|
          End Select
          Set collection = dbx.Search(searchFormula$,dateTime,0)
    LVL 19

    Expert Comment

    continuing from the above solution, you can dump those document collection into a private view or shared folder.
    later you can export to excel.
    Once exported, you can remove it from the folder.

    Hope the above helps you.
    LVL 31

    Expert Comment

    Do you need SORT or SELECTION.  For selection, Bill Hanses provided your answer. For sort, you need one of:
    * custom view
    * sort routine in LotusScript
    * external call to a sort system

    One sloppy way around this is to get a list of unique state names, then sort THOSE, then loop through the list using the formula you are already using.  The list could be hard-coded, or you could retrieve all documents in two passes, the first to retrieve the list of active states and sort them; the second to retrieve by group using your forumula.

    The custom view solution is easiest
    LVL 22

    Expert Comment

    I have a Bubble Sort function in LotusScript that will sort any array ascending or descending.  Let me know if you need it.
    LVL 31

    Expert Comment

    Bubble sort. I'm blowing tiny bubbles.

    Author Comment

    I do not need to do a sort. What I need to do is allow the user to select from different search criteria.The code that Bill provided is perfect, but I may not be searching based on state each time, I may be searching based on zip code, street address, or department.
    Currently receiving error :  "..Initialize: 66 . Unexpected: Then.  Expected: End of Statement Operator"
    LVL 22

    Accepted Solution

    Ok, one thing at a time.

    You said: "The code that Bill provided is perfect".
    But then you said "Currently receiving error ...".

    Now I'm really confused.  Does my example code work or not?

    Also, since you need to search based on several criteria (state, zip, address, etc...) you should gather the choices from the user via a temporary form not via Prompt.

    You can simply compose a form that has fields for each criteria, then have a search button written in LotusScript that does the search and reports the results.

    Take this one step at a time.  Create the search form (make sure it has a computed text field named "SearchOptions" value="0") and add a State combo-box to allow the user to select a state.

    Then add some code to your search button like this.  When you get this working, continue by adding the other fields to the search string:

          Dim ws As New NotesUIWorkspace()
          Dim sess As New NotesSession()
          Dim db As NotesDatabase
          Dim nab As New NotesDatabase("", "")
          Dim dc As NotesDocumentCollection
          Dim doc As NotesDocument
          Dim uidoc As NotesUIDocument
          Dim docSearch As NotesDocument
          Dim strQuery As String

          Set uidoc = ws.CurrentDocument
          Set docSearch = uidoc.Document
          Set db = sess.CurrentDatabase
          if (not nab.Open("SERVER", "names.nsf")) Then error 2000, "Unable to open NAB."
          strQuery = {([Form]="Person") AND ([State]="} & docSearch.GetItemValue("State")(0) & {")}
          set dc = nab.Search(strQuery, Nothing, 0)
          if (dc.Count = 0) then error 2000, "No documents found."
          Set doc = dc.GetFirstDocument()
          Do While (Not doc Is Nothing)
                ' Add data to your report here.
                Set doc = dc.GetNextDocument(doc)
    LVL 31

    Expert Comment

    LVL 22

    Expert Comment

    oops!  I take it that you know what I meant to type, qwaletee.

    For everyone else, there is a typo in my previous post...

          make sure it has a computed text field named "SearchOptions" value="0"

    ... should read...

          make sure it has a computed text field named "SaveOptions" value="0"

    Sorry for any confusion.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
    IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now