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
