[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Sorting data by state using LotusScript

Posted on 2007-08-07
Medium Priority
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

ID: 19650145
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

ID: 19652590
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

ID: 19673783
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
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.

LVL 22

Expert Comment

ID: 19673795
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

ID: 19673914
Bubble sort. I'm blowing tiny bubbles.

Author Comment

ID: 19684476
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

Bill-Hanson earned 2000 total points
ID: 19685032
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

ID: 19687700
LVL 22

Expert Comment

ID: 19691224
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
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…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

873 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