VBA ColumnValues / categories dilemma

HCL Domino Domino Forum
1

I have a bunch of documents categorized in a view by Year: 2001, 2002, 2003, 2004, 2005… 5 categories at this point.

I am attempting to get the names of each of the categories (years). I was doing something like (pseudo):

While Not ( doc Is Nothing )

(check for unique value etc)

unique? Then … blahblahblah = doc.ColumnValues(0

)

Set doc = view.GetNextDocument( doc )

Wend

But it seems to be a very intensive operation. . . which makes sense considering it is sifting through every document looking for unique year column values.

Is there a way in LotusScript to just get the 5 category / column values I want without ripping through the entire db?

2

Subject: VBA ColumnValues / categories dilemma

In a categorized view, you can use Evaluate with @DbColumn. In a categorised column, it returns only the category values used as a “uniqued” array in a Variant. From VB/VBA, use Session.Evaluate. The only problem with doing it through VB/VBA is that you don’t get the alternative string delimiters, so you need to futz a bit getting the quotes in the macro string right.

3

Subject: VBA ColumnValues / categories dilemma

Use an Evaluate statement with a @DbColumn formula. Example:

Dim Years as variant

Years = Evaluate ( { @DbColumn ( “” ; “myserver” : “mydb” ; “myview” ; 1 ) } )

Years will be an array of years.

4

Subject: VBA ColumnValues / categories dilemma

Assuming that the first column is the categorized column to which you refer the Lotusscript would be approximately as follows:

dim view as notesview

dim nav as notesviewnavigator

dim entry as notesviewentry

set view = db.getview(“myview”)

Set nav = view.createviewnav

set entry = nav.getfirst ’ this will return a category entry or Nothing

do while not (entry is nothing)

’

’ do something with entry.columnvalues(0)

’

set entry = nav.getNextCategory(entry)

loop

A little more script than just calling Evaluate, but no nested quotes to deal with.