Tag Archive: LotusScript

Anyone Using the LotusScript Symphony Toolkit?

In this month’s Lotus Developer Tips Newsletter, I wrote about using the Lotus Symphony Toolkit extract data from Lotus Notes to Symphony (Spreadsheets in Part I) via LotusScript.

I’ve been working a lot lately with the toolkit and just started wondering who else (if anyone) is using this?

Why or why not?

Thanks for your input!

Multiple Values in a View – Now With More Special

Okay, so in my last (technical) blog post, I noted a problem I had with getting multiple values in a view via LotusScript. I learned and posted that the NotesViewNavigator was the solution.

The wise Nathan Freeman posted a comment, which I think has quite a handy littly tidbit in it, but I fear not enough people read my blog, let alone read the comments on my blog days after a posting, so I am blogging about his comment. With an example and screenshot.

Here is what Nathan said:

“Make a set of documents with several multi-value fields, all text.

Field 1: Red, Green, Blue
Field 2: Me, Myself, I
Field 3: This, That, The Other
Field 4: Apples, Churches, Very Small Rocks

Give them all the same values for Field 1, but randomize the values for fields 2-4

Now create a view that has a column for each of these fields, in order. Set them all to “Show multiple values as separate entries.”

On the first column, turn on categorization.

Now look at your view CLOSELY.

If you want to do something really special, try CreateViewNavigatorFromCategory(“Red”). Then walk the entries and retrieve the .columnValues.


So, I tried it out. Really special indeed.

Say you had an application with a salesperson form. In this example we have a whopping three documents, but stick with me. We have three fields, “date”, “item” and “quantity”. These three fields are multi-value fields. We’ll assume we have three documents because we only have three salespeople, one for each.

Doc 1:
11/15/09; 11/30/09; 12/15/09; 12/31/09
Apples; Oranges; Grapes; Peaches
10; 30; 40; 20

Doc 2:
11/15/09; 11/30/09; 12/15/09; 12/31/09
Oranges; Apples; Grapes; Peaches
40; 30; 20; 10

Doc 3:
11/15/09; 11/30/09; 12/15/09; 12/31/09
Peaches; Grapes; Apples; Oranges
30; 40; 20; 10

Now if we put these three docs in a view, without “Show multiple values as separate entries”, I would have three rows in my view, with all the data lumped together, separated by semi-colons.

Or I could follow Nathan’s tip and get this:


And then I could use CreateViewNavFromCategory(dateVariable) to get all my column values. Sort of the “GetAllEntriesByKey” version of the NotesViewNavigator, but better. Way better.

*Newbie tip – don’t sort the remaining columns. Notes will screw up the whole “show as multiple values” thing and each sorted column adds rows for each value for each field. Not good.

*Second newbie tip – don’t rely on the view preview in Designer for this type of view. It almost never looks the same in Designer as it does in the client. There is a reason for the “Preview in Notes” button, use it.

Problem With Multiple Values in a View

I had a view with several columns that had “Show multiple values as separate entries” selected. Obviously, these columns showed fields with … wait for it…multiple values. I chose to “Show multiple values ad separate entries” because I wanted the multiple values shown alone, each in its own row. Crazy talk, huh?

Okay, so the view is set up and displaying exactly as I want it to. For example, five documents, two values in the field, show as ten rows. Perfect.

Elsewhere in the application, I had an agent that I wanted to walk the view and get the values. I know, this is revolutionary stuff, eh?

So the agent went something like this…

dim view as Notesview
set view = db.GetView (“myMultiValueView”)

dim viewcol as NotesViewCollection
set viewcol = view.AllEntries

dim viewEntry as NotesViewEntry
set viewEntry = viewcol.GetFirstEntry()

While Not (viewEntry is Nothing)

myVal = viewEntry.ColumnValues(8)

set viewEntry = viewcol.GetNextEntry(viewEntry)


Great, right? Except that “myVal” kept returning just the first value of the multivalue field. If there were two values, and consequently two rows, myVal would return the first value twice. Really infuriating when I knew the code was right. This was pretty simple stuff and was NOT working. A quick check of the help files…everything checks out. My view is good. My code is correct. What’s the problem?

Google to the rescue and I found this SPR, which explained that what I was doing was not going to work. But also provided a workaround.

dim view as Notesview
set view = db.GetView (“myMultiValueView”)

dim nav as NotesViewNavigator
set nav = view.CreateViewNav

dim viewEntry as NotesViewEntry
set viewEntry = nav.GetFirst

While Not (viewEntry is Nothing)

myVal = viewEntry.ColumnValues(8)

set viewEntry = nav.GetNext(viewEntry)


Hooray! Success! It would have been nice if this was documented in the help file, but at the end of the day (almost literally), I found the way to make it work. And, as is frequently the case, I’m blogging this mostly for other people to find the answer if they have this same problem (including my future self).

Find “WhichFolders” via Lotusscript

Follow up to my prior post where Denny commented that he wants the @WhichFolders available in Lotusscript. Well, back in March 2008, Bob Balaban blogged about a routine to find what folder a document is in. He blogged a code snippet that was written in C#. An alternative or two, and the timing of the various methods were blogged by Steve McDonagh, so check that out.

Here is my little snippet of the same basic idea as Bob’s, but in Lotusscript. The v.IsFolder bit is a little slow. And obviously, the more folders you have the longer this will take. @WhichFolders is much faster, but doesn’t work outside of view columns!

Dim ses As New NotesSession Dim db As NotesDatabase Dim myViews As Variant Dim myFolders As Variant Set db = ses.CurrentDatabase Dim doccol As NotesDocumentCollection Dim doc As NotesDocument Set doccol = db.UnprocessedDocuments Set doc = doccol.GetFirstDocument() myViews = db.Views Dim viewnav As NotesViewNavigator While Not (doc Is Nothing) ForAll v In myViews If v.IsFolder Then v.AutoUpdate = False Set viewnav = v.CreateViewNavFrom(doc) If viewnav.Count > 0 Then MessageBox v.name End If End If End ForAll Set doc = doccol.Getnextdocument(doc) Wend

ls2html was brought to you by nsftools.com

Fun with Lists!

Lotusscript lists, that is. Frequently underutilized by developers (including me), but a very useful tool nonetheless. I had a need recently to loop through some documents, and create a unique list of names from those documents, and then count how many unique names I had. I needed to do it in Lotusscript. I would also need to do something with those names later in my code. Oh, and the number of names would depend on the document collection and how many were actually unique. Addtionally, I had more than one bit of data that I wanted to capture. For this example, we’ll say name and phone number. So, what to do?

Well, I *could* use an array, but not knowing how many names would be added to the array would mean having to ReDim the array, but that’s no fun. So enter Lists! Creating a list is super easy, and you don’t have to know how many items will be in the list. I added a little counter to count the number of items in my list as I added them. Yes, I could have added them, and then looped through afterward and counted them that way, but I actually had some other stuff to do, so did it when I added.

Dim clientList List As String ‘dim the list itself Dim n As Long ‘dim n as long for the counter n = 0 ‘start the count at 0 While Not entry Is Nothing ‘obviously prior code would have dimmed a view entry collection and 'provided a way to loop through it, this part of the code just insures we are acting on an actual entry ‘This next bit checks to see if the item is already in the list and if it is NOT, then increases the counter by 1, 'and adds the entry with column value 5 as the listtag and column value 6 as the value If Not (Iselement(clientList(entry.columnvalues(5)))) Then n = n + 1 clientList (entry.ColumnValues(5)) =entry.columnvalues(6) End If Print Cstr(n) ‘this prints the count of items added to the list ‘This just shows something we can do with the list, such as printing the listtag '(aka column value 5) for all of the entries in the list. Forall clients In clientList Print ListTag(clients) End Forall

ls2html was brought to you by nsftools.com

Pretty easy, huh? Easy to create and easy to use the data. So Lists! Use ‘em!