Notes: How to re-order rows on a 'dynamic' table

If you have a 'dynamic' table where each column is composed by a multivalue field and you want to be able to change a row's position, then you can do it like this:

• Create a multivalue, computed for display field (hidden), named 'FldPos' with this formula:

@For(i:=1;i<=@Elements(fldProxiesName);i:=i +1;
sList:=sList : @Text(i)
);
@Trim(sList)

• Create a radio button field, named 'FldPosRB' and select "Use formula for choices" and then on the formula window type:

FldPos

• Then create two Buttons (or Action Hotspots), one called "Move Up" and another "Move Down".

The code for the first button is (for a 3 column table):

' moving up
Sub Click(Source As Button)
Dim ws As New notesuiworkspace
Dim uidoc As notesuidocument
Dim iCurPos As Integer

Dim vFinalList() As Variant
Dim vFinal1List() As Variant
Dim vFinal2List() As Variant

Dim vTemp As Variant

Dim sFieldSelection As String

Dim aFieldNames(2) As String

sFieldSelection = "FldPosRB"

aFieldNames(0)="FldMultivalue1"
aFieldNames(1)="FldMultivalue2"
aFieldNames(2)="FldMultivalue3"


Dim i As Integer

On Error Goto ErrorHandling

Set uidoc = ws.CurrentDocument


If Not isArrayInit(uidoc.document.GetItemValue(aFieldNames(0))) Then
Msgbox "The table is empty." , 64 , "Move up"
Exit Sub
End If

iCurPos =Cint(uidoc.document.getitemvalue(sFieldSelection)(0)) - 1

If iCurPos > 0 Then

For i=0 To Ubound (uidoc.Document.GetItemValue(aFieldNames(0)))
If i = iCurPos - 1 Then 'the position to move to
Redim Preserve vFinalList (i)
vFinalList(i) = uidoc.Document.getitemvalue(aFieldNames(0))(iCurPos)
Redim Preserve vFinal1List (i)
vFinal1List(i) = uidoc.Document.getitemvalue(aFieldNames(1))(iCurPos)
Redim Preserve vFinal2List (i)
vFinal2List(i) = uidoc.Document.getitemvalue(aFieldNames(2))(iCurPos)


Redim Preserve vFinalList (i + 1)
vFinalList(i + 1) = uidoc.Document.getitemvalue(aFieldNames(0))(i)
Redim Preserve vFinal1List (i + 1)
vFinal1List(i + 1) = uidoc.Document.getitemvalue(aFieldNames(1))(i)
Redim Preserve vFinal2List (i + 1)
vFinal2List(i + 1) = uidoc.Document.getitemvalue(aFieldNames(2))(i)


i = i + 1

Else
Redim Preserve vFinalList (i)
vFinalList(i) = uidoc.Document.getitemvalue(aFieldNames(0))(i)
Redim Preserve vFinal1List (i)
vFinal1List(i) = uidoc.Document.getitemvalue(aFieldNames(1))(i)
Redim Preserve vFinal2List (i)
vFinal2List(i) = uidoc.Document.getitemvalue(aFieldNames(2))(i)


End If
'vFinalList =
Next

Call uidoc.document.ReplaceItemValue(aFieldNames(0), vFinalList)
Call uidoc.document.ReplaceItemValue(aFieldNames(1), vFinal1List)
Call uidoc.document.ReplaceItemValue(aFieldNames(2), vFinal2List)

Call uidoc.Document.replaceitemvalue(sFieldSelection, iCurPos)
uidoc.Refresh
uidoc.refresh
End If

errorhandling:
If Err=9 Then 'subscript out of range, one column is empty
Resume Next
Elseif Err>0 Then
Msgbox "Error: " + Error
Goto EndThis
End If

EndThis:
End Sub

And to move down (logical differences in bold):

' moving down
Sub Click(Source As Button)
Dim ws As New notesuiworkspace
Dim uidoc As notesuidocument
Dim iCurPos As Integer

Dim vFinalList() As Variant
Dim vFinal1List() As Variant
Dim vFinal2List() As Variant
Dim sFieldSelection As String

Dim aFieldNames(2) As String

sFieldSelection = "FldPosRB"

aFieldNames(0)="FldMultivalue1"
aFieldNames(1)="FldMultivalue2"
aFieldNames(2)="FldMultivalue3"


Dim i As Integer

On Error Goto ErrorHandling

Set uidoc = ws.CurrentDocument

If Not isArrayInit(uidoc.document.GetItemValue(aFieldNames(0))) Then
Msgbox "The table is empty." , 64 , "Move down"
Exit Sub
End If

iCurPos =Cint(uidoc.document.getitemvalue(sFieldSelection)(0)) - 1


If iCurPos < i="0" style="font-weight: bold;">i = iCurPos Then
Redim Preserve vFinalList (i)
vFinalList(i) = uidoc.Document.getitemvalue(aFieldNames(0))(iCurPos + 1)
Redim Preserve vFinal1List (i)
vFinal1List(i) = uidoc.Document.getitemvalue(aFieldNames(1))(iCurPos + 1)
Redim Preserve vFinal2List (i)
vFinal2List(i) = uidoc.Document.getitemvalue(aFieldNames(2))(iCurPos + 1)



Redim Preserve vFinalList (i + 1)
vFinalList(i + 1) = uidoc.Document.getitemvalue(aFieldNames(0))(iCurPos)
Redim Preserve vFinal1List (i + 1)
vFinal1List(i + 1) = uidoc.Document.getitemvalue(aFieldNames(1))(iCurPos)
Redim Preserve vFinal2List (i + 1)
vFinal2List(i + 1) = uidoc.Document.getitemvalue(aFieldNames(2))(iCurPos)



i = i + 1
Else
Redim Preserve vFinalList (i)
vFinalList(i) = uidoc.Document.getitemvalue(aFieldNames(0))(i)
Redim Preserve vFinal1List (i)
vFinal1List(i) = uidoc.Document.getitemvalue(aFieldNames(1))(i)
Redim Preserve vFinal2List (i)
vFinal2List(i) = uidoc.Document.getitemvalue(aFieldNames(2))(i)

End If
Next


Call uidoc.document.ReplaceItemValue(aFieldNames(0), vFinalList)
Call uidoc.document.ReplaceItemValue(aFieldNames(1), vFinal1List)
Call uidoc.document.ReplaceItemValue(aFieldNames(2),vFinal2List)


Call uidoc.Document.replaceitemvalue(sFieldSelection, iCurPos + 2)
uidoc.Refresh
uidoc.refresh
End If

errorhandling:
If Err=9 Then 'subscript out of range, one column is empty
Resume Next
Elseif Err>0 Then
Msgbox "Error: " + Error
Goto EndThis
End If

EndThis:
End Sub

Notes: Document has not yet been saved error.

Scenario:

Embedded view on a form (or page) has an action button that invokes a LS agent to work on selected documents. This works fine in a regular view.

Problem:

This action button returns the error: "Document has not yet been saved".

Solution:

In embedded views, the LS needs to be in the action button.

Notes: View Entry's ColumnValues returns an array for a specific column

This will happen if the column's property "Show multiple values as selected entries" is selected.

In this case the ColumnValues property of the NotesViewEntry class, will return an Array and the common way of obtaining it's value will fail.

To access the entry's column value weather it's an array or not, use this:

Function getColValue (vCol As Variant) As String
If Isarray(vCol) Then
getColValue = Cstr(vCol(0))
Else
getColValue = Cstr(vCol)
End If
End Function

And then use it like this:

stringValue = getColValue(vwEntry.ColumnValues(1))

Notes: How to update rich text in a document that's open and redisplay it without saving

«The code sample below shows how to write LotusScript code that makes changes to a rich text field in a document that the user is editing, and displays those changes immediately on-screen, without saving the changes. This also works if you need to repeat other operations that only occur when a document is opened, e.g. evaluating section editor formulas and computed subform formulas.

Here's the code, which should work in Notes 5.0.2 and higher:

Dim wksp As New NotesUIWorkspace
Dim session As New NotesSession
Dim uidoc As NotesUIDocument, uidocNew As NotesUIDocument
Dim doc As NotesDocument
Dim rti As NotesRichTextItem
Dim strFieldname As String

Set uidoc = wksp.CurrentDocument
uidoc.Refresh True ' do this if the rich text field is editable, to get the current contents in case user has modified them.
Set doc = uidoc.Document ' get the back-end document for the document open on screen.
strFieldname = uidoc.CurrentField ' remember the current field if any
Set rti = doc.GetFirstItem("fieldname") ' insert your fieldname here, generally "Body"

' Make your rich text changes here, for instance:
Call rti.AddNewLine(1, True)
Call rti.AppendText(Now & ": log entry.")
If session.NotesBuildVersion >= 190 Then
rti.Update ' ND6 only
Else
Call doc.ComputeWithForm(True, False) ' caution, as this may erase some field values if you have @Db functions in formulas.
End If

doc.SaveOptions = "0" ' make it possible to close the document without a "do you want to save" prompt. If this is a mail-in doc you may need to set MailOptions="0" also to avoid being prompted.
Call uidoc.Close(True)
Set uidocNew = wksp.EditDocument(True, doc, , , , True)
Delete uidoc
uidocNew.Document.RemoveItem("SaveOptions")
If strFieldname <> "" Then uidocNew.GotoField(strFieldname) ' return focus to field that was current before.

Note: this will cause Queryclose, Queryopen, Postopen (and so on) form events to trigger. Also, uidoc.Refresh will execute computed field formulas and input validations, so you should write the validation formulas to not return @Failure unless @IsDocBeingSaved | @IsDocBeingSent is true.»

in Notes/Domino 6 and 7 Forum

Notes: How to access .Net classes from LotusScript

IBM Technote: How to access .Net classes from LotusScript

Problem
You have created an object using Microsoft .Net and would like to create and use this object within LotusScript. How can this be done?
 
Solution
To create a .Net DLL that is accessible from LotusScript perform the following steps:

1. Create a C# Class Library Project with the following code:

using System;

public class MyTest
{
public string AppendStr(string s)
{
return s + " from inside the DLL";
}
}

Note: A Visual Basic Class Library could also be created.

2. To access this DLL from anywhere on the machine you will also need to create this DLL as a shared assembly and publish the your DLL to the Global Assembly Cache (GAC). Otherwise, the DLL will be created as a private assembly and you will only be able to create the object if the DLL is in the same directory as the calling program.

      To create this DLL as a shared assembly you will need to generate a cryptographically strong name for the assembly. To do this start up the Visual Studio Command Line prompt, and enter the following command:

      sn -k c:\MyTest.snk

      This will create an Assembly Key File name at the location c:\MyTest.snk


3. Open the AssemblyInfo.cs file of the project include the following line:

      [assembly: AssemblyKeyFile(@"c:\\MyTest.snk")]

      Note: For Visual Basic you will need to include this tag instead: <assembly: AssemblyKeyFile(@"c:\\MyTest.snk")>


4. Build the project. This will build the project with the value of the Assembly Key File and allow it to be published as a Shared Assembly to the Global Assembly Cache.

5. Using the Visual Studio Command Line, change to the directory where the DLL was generated, then publish the DLL to the Global Assembly Cache using the following command:

      GacUtil /i MyTest.dll


6. To make the objects in this DLL accessible via the COM interface, enter the following command:

      regasm MyTest.dll


7. To access the object contained in this DLL using LotusScript use the following code:

      Dim obj As Variant
      set obj = CreateObject("MyTest")
      MsgBox obj.AppendStr("This is")

      The message box will display "This is from inside the DLL."


In short, once the shared assembly is published as a COM component, it can be instantiated in LotusScript using the CreateObject() function. With the exception of Step 7, all of the steps are all specific to the deployment process of Microsoft .Net components. For further information regarding any of these utilities you should refer to the Microsoft web site or Microsoft support.

Notes: Bubble Sorting a Document Collection

«Searching a database using db.ftsearch is pretty fast and efficient but ordering based on a field value in the documents is not possible. What is required is something akin to a SQL ORDER BY clause but alas we don't have one and have to resort to sorting the collection the old fashioned way... by implementing a bubble sort.

Usage

Dim vCollection As NotesDocumentCollection 

Set vCollection = vSearchDatabase.ftsearch(aQuery, 501, FT_SCORES, FT_FUZZY) 
Set vCollection = Global_SortCollection(vCollection, "Person_Surname")

The sort function requires two parameters, the first a NotesDocumentCollection with documents in it and the second a field name to use to sort the documents, in the example above the documentcollection "vCollection" will be sorted by "Person_Surname".

The function works by converting the document collection into an array of documents, doing a bubble sort on the array and then converting the array back into a new document collection.

The Code

Function Global_SortCollection(aCollection As NotesDocumentCollection, aField As String) As Variant 
 Dim vCollectionDB As NotesDatabase 
 Dim vDocTemp As NotesDocument 
 Dim vCollectionSorted As NotesDocumentCollection 
 Dim vDocArray() As String 
 Dim vLower As Integer
 Dim vUpper As Integer 
 Dim vBottomMax As Integer 
 Dim vLoop As Integer 
 Dim vLoopTopHalf As Integer 
 Dim vLoopBottomHalf As Integer 
 Dim vMidPoint As Integer 
 Dim vTarget As String 
 Set vCollectionDB = aCollection.Parent 
 Redim vDocArray(aCollection.count-1) As String 
'CONVERT DOCUMENT COLLECTION TO ARRAY 
 Let vLoop = 0 
 Set vDocTemp = aCollection.GetFirstDocument 
 Do While Not vDocTemp Is Nothing 
  vDocArray(vLoop) = vDocTemp.GetItemValue(aField)(0) + "~" + vDocTemp.UniversalID 
  Set vDocTemp = aCollection.GetNextDocument(vDocTemp) 
  vLoop = vLoop + 1 
 Loop
 'SHELL SORT THE ARRAY 
 vLower = Lbound( vDocArray( ) ) 
 vUpper = Ubound( vDocArray( ) ) 
 vMidPoint = 1 
'DETERMINE A STARTING MID POINT TO THE ARRRAY 
 Do
  vMidPoint = (3*vMidPoint) + 1 
 Loop Until vMidPoint > vUpper - vLower + 1
 'LOOP THROUGH THE ARRAY 
 Do 
  vMidPoint = vMidPoint \ 3 
  vBottomMax = vLower + vMidPoint - 1 
  For vLoopTopHalf = vBottomMax + 1 To vUpper 
   
   
   vTarget = vDocArray(vLoopTopHalf) 
   vLoopBottomHalf = vLoopTopHalf 
'COMPARE TOP HALF OF ARRAY WITH BOTTOM HALF 
   Do While vDocArray( vLoopBottomHalf - vMidPoint ) > vTarget
    vDocArray(vLoopBottomHalf) = vDocArray(vLoopBottomHalf - vMidPoint) 
    vLoopBottomHalf = vLoopBottomHalf - vMidPoint 
    If (vLoopBottomHalf <= vBottomMax) Then Exit Do 
   Loop 
   If (vLoopBottomHalf <> vLoopTopHalf) Then vDocArray(vLoopBottomHalf) = vTarget 
  Next 
 Loop Until vMidPoint = 1 
'CREATE A NEW EMPTY DOC COLLECTION 
 Set vCollectionSorted = vCollectionDB.Search("",Nothing,0) 
'CONVERT ARRAY TO DOC COLLECTION 
 For vLoop = 0 To Ubound(vDocArray) 
  Set vDocTemp = vCollectionDB.GetDocumentByUNID(Strrightback(vDocArray(vLoop), "~")) 
  Call vCollectionSorted.AddDocument(vDocTemp) 
 Next vLoop 
'RETURN THE DOCUMENT COLLECTION 
 Set Global_SortCollection = vCollectionSorted 
End Function 

Conclusion

A simple yet extremely useful function that belongs in everyone's global routines script library.»

(Source)

Notes - Lotus Script: Resume without error

Scenario:

A script ends and returns the "Resume without error" message, even though no error was thrown during it's execution.
Checking the Error$ function, it may return the message "Variant does not contain a container".


Possible solution:

Include in the script the "Option Declare" statement, correct all errors (undeclared variables) and save it.

Notes - LotusScript: function returning a notes document

Problem:

Even though the document is instatiated inside the function it is not returned to the calling code.

 

Explanation:

You're probably declaring and setting the value of the NotesDocument's parent database inside the function, which is outside the scope of the calling function. As a result, although you return the right document, the containing database is no longer open.

You can either pass the database *and* the document and pass them both back, or you can declare the database globally .
 

From: Notes Forum

Notes - Lotus Script: Replace Substring

Function ReplaceSubstring(sourcestr As String, fromstr As String, tostr As String) As String
   ' This function replaces characters in a string. Take all the occurrences of "fromstr"
   ' in the source string and replace them with "tostr"
   Dim tempstr As String
   Dim convstr As String
   Dim i As Long
   Dim length As Long
   tempstr = sourcestr
   If Len(fromstr) = 0 Then
      ReplaceSubstring = sourcestr
      Exit Function
   End If
   If Instr(tostr, fromstr) <> 0 Then ' If, for example, "\" is being replaced with "\\"
      ' Find a character (or set) that is not in the source string.
      ' Try the extended characters (over 128 ASCII)
      i = 128
      length = 1
      convstr = ""
      While convstr = ""
         If Instr(tempstr, String$(length, Chr$(i))) = 0 Then convstr = String$(length, Chr$(i))
         i = i + 1
         If i = 256 Then ' If all the extended characters were in there
            length = length + 1 ' Start over, but try 2 extended characters (or 3 or 4)
            i = 128
         End If
      Wend
      ' Go through tempstr twice - once replacing fromstr with the computed
      ' string, then replacing the computed string with tostr
      While Instr(tempstr, fromstr) <> 0
         tempstr = Left(tempstr, Instr(tempstr, fromstr)-1) & convstr _
         & Mid(tempstr, Instr(tempstr, fromstr)+Len(fromstr))
      Wend
      While Instr(tempstr, convstr) <> 0
         tempstr = Left(tempstr, Instr(tempstr, convstr)-1) & tostr _
         & Mid(tempstr, Instr(tempstr, convstr)+Len(convstr))
      Wend
   Else ' It's a normal replace substring call - fromstr is not part of tostr
      While Instr(tempstr, fromstr) <> 0
         tempstr = Left(tempstr, Instr(tempstr, fromstr)-1) & tostr _
         & Mid(tempstr, Instr(tempstr, fromstr)+Len(fromstr))
      Wend
   End If
   ReplaceSubstring = tempstr
End Function