LotusScript View ColumnValues dealing with embedded arrays

Question

When working with LotusScript to access the ColumnValues (a variant array of values accessed via a NotesDocument or NotesViewEntry) some of the array elements may contain embedded arrays within one or more elements. eg:

doc.ColumnValues(0) = "A"
doc.ColumnValues(1) = ["A", "B"]
doc.ColumnValues(2) = 4
...

Is there a way to assign all the array values to an array in one go? If you assign the ColumnValues array to a variant variable, then this only works provided no array data is embedded within the arrays elements, ie: ColumnValues(1) = "AB" not "[A,B]" eg:

dim v as variant
v = doc.ColumnValues

If an array is present, this assignment fails, but works if all elements are scalar. One workaround is to ensure that all ColumnValues are scalar (not arrays), but this cannot be guaranteed. The only other workaround I have is to iterate over all ColumnValues and check for an array:

For i = 1 to ubound(doc.ColumnValues)   'or other columcount var!
  v=doc.ColumnValues(i)
  if isarry(v) then
    a=join(v,";")
  else
    a=v
  end if
Next

The above works but is very slow for many columns, does anybody have an alternative approach?

andora · Accepted Answer · 2013-05-13T14:09:38.267

Ok after some testing, I found the following: If I use a forall loop...

dim cvals() as variant
redim cvals(ncolsreqd)
i = 0
ForAll v In doc.columnValues
    If IsArray(v) Then
        cvals(i) = Join(v, ";")
    Else
        cvals(i) = v
    End if
    i = i + 1
End ForAll
Print "v:= " & Join(cvals, ",")

Then the ColumnValues only appear to retrieved once and performance for many columnns is unaffected. (If a For loop is used, then the whole array is looked up each time and then 1 element selected from the array).

score 0 · Answer 2 · answered May 14 '13 at 16:33

Another idea: If the data you want to retrieve is almost always scalar, you can use the single assign to variant:

dim v as variant
v = doc.ColumnValues

Then, if this errors because of a type mismatch, (embedded array), then only reprocess the data in a forall loop as part of the error handling, then resume. Avoids the forall loop for the majority of cases and should be faster overall. (However, if large proportion of data is multi-valued, then the error/array handling route could be slower)!

LotusScript View ColumnValues dealing with embedded arrays

2 Answers2