Replace a string in the documents in a Lotus database

Ref: http://www.lucazanini.eu/2011/lotus/replace-a-string-in-the-documents-in-a-lotus-database/?lang=en

by LUCA ZANINI
I think it might be useful to replace each occurrence of a string in all the documents or in the selected documents in a Lotus database, especially when you want to replace a user in the fields author/reader.
The use of roles or groups should be recommended to set the access to the documents, but there may be cases where the user’s name has been used as in Lotus Workflow projects.

This code only works for fields of type text, number, name, author, reader and date.

The meaning of the constants in the code:

ONLYSELECTEDDOCUMENTS : True to process only selected documents, False to process all documents in the database
FROMSTRING : string to replace
TOSTRING : new string
WHOLE_WORD : True if you want to replace only if the field or a its element in the case of multivalue field is exactly equal to FROMSTRING, False if you want to replace even if the string FROMSTRING is a substring of field or a its element in the case of multivalue field
EXCLUDEDOLLARFIELDS : True to exclude fields whose names begin with “$” symbol like $Revisions or $UpdatedBy, False to include these fields
EXCLUDEFIELDS : list of fields not to be replaced in the format of “Field1;Field2;Field3?
INCLUDEONLYFIELDS : list of fields only to be replaced in the format of “Field1;Field2;Field3?
Below there is the code to insert in a LotusScript agent, tested on Lotus Notes 8.5.2

Option Public
Option Declare

Sub Initialize()

    Dim s As New NotesSession
    Dim db As NotesDatabase
    Dim dc As NotesDocumentCollection
    Dim doc As NotesDocument
    Dim value As Variant
    Dim formula As String
    Dim FROMSTRING As String
    Dim TOSTRING As String
    Dim i As Integer
    Dim newValue As Variant
    Dim replaceValue As Variant
    Dim modifiedDocument As Boolean
    Dim modifiedField As Boolean
    Dim itemType As Integer
    Dim replaceVariant As Variant
    Dim ONLYSELECTEDDOCUMENTS As Boolean
    Dim WHOLE_WORD As Boolean
    Dim EXCLUDEDOLLARFIELDS As Boolean
    Dim EXCLUDEFIELDS As string
    Dim excludeField As Variant
    Dim INCLUDEONLYFIELDS As String
    Dim includeOnlyField As Variant
    Dim foundField As Boolean
    Dim newDateTime As NotesDateTime
   
    ONLYSELECTEDDOCUMENTS = True
    WHOLE_WORD = False
    EXCLUDEDOLLARFIELDS = True
    EXCLUDEFIELDS = "" ' "Field1;Field2..."
    INCLUDEONLYFIELDS = "" ' "Field1;Field2..."
    FROMSTRING ="aaa"
    TOSTRING = "zzz"

    Set db = s.Currentdatabase
    If ONLYSELECTEDDOCUMENTS Then
        Set dc = db.Unprocesseddocuments
    Else
        Set dc = db.Alldocuments
    End If
   
    Set doc = dc.Getfirstdocument()
    Do While Not(doc Is nothing)
       
        modifiedDocument = False
       
        ForAll item In doc.Items
           
            If not(item.type=1280 Or item.type=768 Or item.type=1074 Or item.type=1075 Or item.type=1076 Or item.type=1024) Then
                GoTo nextItem
            End If
           
            If EXCLUDEDOLLARFIELDS Then
                If (Left(item.name, 1)="$") Then
                    GoTo nextItem
                End If
            End If
           
            If (EXCLUDEFIELDS<>"") Then
                excludeField = Split(EXCLUDEFIELDS, ";")
                ForAll v In  excludeField
                    If (Trim(v)=item.name) Then
                        GoTo nextItem
                    End If
                End ForAll            
            End If

            If (INCLUDEONLYFIELDS<>"") Then
                includeOnlyField = Split(INCLUDEONLYFIELDS, ";")
                foundField = false
                ForAll v In  includeOnlyField
                    If (Trim(v)=item.name) Then
                        foundField = True
                    Exit forall
                    End If
                End ForAll
                If Not(foundField) Then
                    Print item.name
                    GoTo nextItem
                End If        
            End If
           
            i = 0
            modifiedField = False
            value = item.values
            newValue = ""
            itemType = item.Type
            replaceVariant = value
           
            If (IsArray(value)) then
                ForAll v In value
                   
                    formula = ""
                   
                    If WHOLE_WORD then
                        If (CStr(v)=FROMSTRING) Then
                            formula = |@ReplaceSubstring("|+CStr(v)+|";"|+FROMSTRING+|";"|+TOSTRING+|")|
                        End If
                    Else
                        formula = |@ReplaceSubstring("|+CStr(v)+|";"|+FROMSTRING+|";"|+TOSTRING+|")|      
                    End If
                   
                    If (formula<>"") then
                        replaceValue = Evaluate(formula, doc)
                       
                        If (CStr(v)<>CStr(replaceValue(0))) Then
                            modifiedField = True
                        End If

                        If (itemType=1280) Then ' TEXT
                            replaceVariant(i) = CStr(replaceValue(0))
                        ElseIf (itemType=768) Then ' NUMBERS
                            replaceVariant(i) = CDbl(replaceValue(0))
                        ElseIf (itemType=1074) Then ' NAMES
                            replaceVariant(i) = replaceValue(0)
                        ElseIf (itemType=1075) Then ' READERS
                            replaceVariant(i) = replaceValue(0)
                        ElseIf (itemType=1076) Then ' AUTHORS
                            replaceVariant(i) = replaceValue(0)
                        ElseIf (itemType=1024) Then ' DATETIMES
                            replaceVariant(i) = replaceValue(0)
                        End If
                    End If

                    i = i + 1
                End ForAll
            End If
           
            If modifiedField Then
                If (itemType=1280) Then ' TEXT
                    newValue = replaceVariant
                    Call doc.Replaceitemvalue(item.name, newValue)
                ElseIf (itemType=768) Then ' NUMBERS
                    newValue = replaceVariant
                    Call doc.Replaceitemvalue(item.name, newValue)
                ElseIf (itemType=1074) Then ' NAMES
                    newValue = replaceVariant
                    Call doc.Replaceitemvalue(item.name, newValue)
                ElseIf (itemType=1075) Then ' READERS
                    newValue = replaceVariant
                    Call doc.Replaceitemvalue(item.name, newValue)
                ElseIf (itemType=1076) Then ' AUTHORS
                    newValue = replaceVariant
                    Call doc.Replaceitemvalue(item.name, newValue)
                ElseIf (itemType=1024) Then ' DATETIMES
                    Set newDateTime = New NotesDateTime(replaceVariant(0))
                    Call doc.Replaceitemvalue(item.name, newDateTime)
                End If
               
                modifiedDocument = True
            End If
           
nextItem:  
        End forall
       
        If modifiedDocument then
            Call doc.save(True, True, False)
        End If
       
        Set doc = dc.Getnextdocument(doc)
    Loop

End Sub