Replace a string in the documents in a Lotus database

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