THE CHALLENGEWhat’s the most efficient way to locate and replace user names across 100,000 documents?
THE BACKGROUND
Approximately 600 user names are changing.
For example “Jane Doe/Germany/SuperCo” is changing to “Jane Marie Doe/Philipines/SuperCo”.
In order to maintain document access, all AUTHORS, READERS and NAMES fields need to be updated.
Some fields store a single-value while other fields store multiple-values.
Some fields store values in Abbreviated format while others store in Canonical format.
There are approximately 30 fields per document that have the potential to be updated.
(Side Note: I did not create the database, I just inherited it.)
CURRENT DESIGN
The current approach is to build a collection of documents, loop through the documents and search for all user names stored in any non-richtext field. If a match is found replace it and save the document. This approach touches each document only ONE time. Below is working code, but it takes a long time run when testing with a single name replacement. It will take much longer once the code is expanded to search for 600+ users. I do plan to run this code in an agent “On Server” which will help speed up the processing.
Does anyone have any suggestions on a more efficient approach?
WORKING CODE…
Option Public
Option Declare
Sub FixNames
Dim s As New NotesSession
Dim db As NotesDatabase
Dim collection As NotesDocumentCollection
Dim doc As NotesDocument
Dim cntr As Integer
Dim debug As Integer
Dim NewName As String
Dim OrigName As String
Dim OrigPerson As NotesName
Dim NewPerson As NotesName
Dim ErrorMsg As String
Dim SearchCriteria As String
Dim i As Integer
On Error GoTo Oops
'-----------------------------------------------------------------------------
’ Initialize variables
'-----------------------------------------------------------------------------
Cntr = 0
debug = 0
'SearchCriteria = “/Germany”
SearchCriteria = “Jane Doe/Germany/SuperCo”
'Placeholder: eventually add code here to import the old and new names from spreadsheet
'Hardcoding for testing purposes
OrigName = “Jane Doe/Germany/SuperCo”
NewName = “Jane Marie Doe/Philipines/SuperCo”
Set db = New NotesDatabase( “TestServer1”, “TestDB.nsf”)
Set OrigPerson = New NotesName( OrigName )
Set NewPerson = New NotesName( NewName )
If debug Then Print "Searching for " & OrigPerson.Common
'-----------------------------------------------------------------------------
’ Build a collection of documents that meet the search criteria
'-----------------------------------------------------------------------------
Set collection = db.FTSearch( SearchCriteria ,0)
'-----------------------------------------------------------------------------
’ Loop through the collection of documents for the current database
'-----------------------------------------------------------------------------
Set doc = collection.GetFirstDocument()
While Not(doc Is Nothing)
cntr = cntr + 1
Print "Processing document " & cntr & " of " & collection.Count
'-------------------------------------------------------------------------
' Loop through all fields for the current document
'-------------------------------------------------------------------------
ForAll item In doc.Items
' Placeholder: Eventually add code to loop through all user names to be changed
'---------------------------------------------------------------------
' Does the current field contain the original user name string?
' If yes, update the field. Otherwise skip it.
'---------------------------------------------------------------------
If InStr ( item.text, OrigName ) > 0 Then
If debug Then Print "Curr value " + item.name + " = " + item.text
If item.type = 1 Then
'Skip RichText fields
Print "Skipping RichText field: " + item.name
else
'-------------------------------------------------------------
' Determine total values in the field.
' Field contains a single value if Ubound equals 0
' Field contains a multi values if Ubound is greater than 0
'-------------------------------------------------------------
If UBound(item.Values) = 0 Then
'---------------------------------------------------------
' Single value field. Check field format and replace value.
'---------------------------------------------------------
If (item.text = OrigPerson.Abbreviated) Then
' Found single value name in abbreviated format
Call ReplaceFieldValue ( doc, item.name, NewPerson.Abbreviated )
If debug Then Print "New value " + item.name + " = " + item.text
Else
' Found single value name in a alternate format. Set to canonical
Call ReplaceFieldValue ( doc, item.name, NewPerson.Canonical )
If debug Then Print "New value " + item.name + " = " + item.text
End If
Else
'---------------------------------------------------------
' Multi-value field. Locate element and replace value.
'---------------------------------------------------------
If debug Then Print "Found Multi Value field."
i=0
ReDim ReturnArray(0) As Variant
ForAll v In item.Values
ReDim Preserve ReturnArray (i) As Variant
If InStr ( v , OrigPerson.common ) > 0 Then
' Found match. Check the name format and replace it
If v = OrigPerson.Abbreviated Then
ReturnArray(i) = NewPerson.Abbreviated
Else
ReturnArray(i) = NewPerson.Canonical
End If
Else
' Value does not contain the search term, skip it.
ReturnArray(i) = v
End If
i = i + 1
End ForAll
Call ReplaceFieldValue ( doc, item.name, ReturnArray )
End If ' End of Multi-value check
End If ' End of RichText check
End If ' End of found string in field check
' Eventually... Put end of loop through user names here
doc.Save True, True
End ForAll ' End of loop through fields in document
Set doc = collection.GetNextDocument(doc)
Wend
Print "Update complete. Total documents found: " & cntr
'------------------------------------------
’ Exit subroutine statement
'------------------------------------------
Finished:
Exit Sub
Oops:
'------------------------------------------
’ Build and display the error message
'------------------------------------------
ErrorMsg = “Error #” & Err & Chr$(10) &_
|“| & Error$ & |”| & Chr$(10) & Chr$(10) & "Line " &_
Erl & | in object: “| & LSI_Info(2) & |”|
MsgBox ErrorMsg, 16, “Unexpected Error”
Resume Finished
End Sub
Sub ReplaceFieldValue ( doc As NotesDocument, FieldName As String, NewValue As Variant )
Dim item As NotesItem
Set item = doc.ReplaceItemValue( FieldName, NewValue )
End Sub
