A scanEZ user recently contacted us with this excellent question:

“Is scanEZ able to search and replace a specific string within all documents of a Lotus Notes database? We are soon to undergo a User OU migration project and this functionality would really help us to manage our applications throughout the project. We need to find references of a given username and replace it with the new OU structure. For example find “CN=John Doe/OU=IT/O=ACME”and replace to “CN=John Doe/OU=UK/O=MEGACORP” ?”

We were happy to tell him that, yes, it is possible. And here’s how it’s done…

Part One: Getting the Document Selection

First off, we need to use scanEZ’s Search by Formula function (in the Search menu).

The Search by Formula option in scanEZ

Then we must input the following formula. It will look for any documents with fields containing a given name (e.g. “Alfred Test1″) and place the resulting documents in a ‘My Selection‘ virtual folder. With this formula, we’re basically gathering a list of fields for each document and looking inside them to see if our “Alfred Test1″ string is present. We added an additional condition to skip any case where the string is present in the $UpdatedBy item. We’re doing this because this is a special, un-editable item. (Note: This is just a ‘quick and dirty’ formula that’s quite adequate for the task at hand—we could have tweaked it for better efficiency by making the search move on to another document after the first field value match).

This is where we enter our formula to find instances of ‘Alfred Test1′

Part Two: Investigating the Scope of the Job at Hand

We now have a selection of documents which all contain at least one field with the string that we hope to change. But before go ahead an process anything, it’s nice to get an idea of what is going to be changed. Here is where scanEZ’s ability to simulate applying a formula comes in very handy. So we created the formula below to show us precisely which fields, in which documents, need updating.

We can use this same formula either in the ‘Change Displayed Titles‘ (using formula) in the Selection Tree or in the ‘Add Custom Column‘ feature in the Values dialog (access by clicking the Values button in the Diff panel). The former lays things out right in front of our eyes and the latter puts the information in a grid that we can sort or export to a spreadsheet. In either case we must be sure that the MySelection virtual folder we created in step one is highlighted.

Here are the results of our formula when used with the ‘Change Displayed Title’ feature; the document titles represent the fields that require changes

After selecting our MySelection created in step one, we’ll see the Diff Panel. From here we can click the Values button to see the Values dialog. We’ll want to click the ‘Add Custom Column’ button here and enter our formula

After entering the Custom column, you’ll see a string ‘???’ characters until you click ‘evaluate.’

After evaluating the column, you’ll see the fields that require changing in each document. You can group by the custom column to get a better idea of the full extent of the changes required. You can also copy the grid to a spreadsheet.

Part Three: The Search and Replace Operation

Now we have our select and we know the full scope of the changes to be made. It’s time to make the changes.

To do so we start by selecting the MySelection virtual folder containing the search results created in part one. This takes you to the Diff Panel. From here you’ll need to click the Diff button to perform a compare/contrast operation on the fields of the documents retrieved.

This screen shows how we perform a ‘Diff’ operation on a MySelection virtual folder in scanEZ

After this Diff operation is completed, you should see a list of all the items that appear in at least one of the documents.

Now here’s where it gets interesting… We want to replace all instances of  “Alfred Test1″ with “John Doe” but we have no way of knowing which fields require modification. The workaround is finding an item that we’re sure will be in every document. This item will serve as a sort of ‘anchor.’ We’ll use the ‘form’ item as this anchor. With the ‘Form’ item selected, we’ll now click the Modify button, and add the following formula:

We need to select the ‘Form’ item and click ‘Modify’…

…and in this dialog we enter the formula which doesn’t actually change the form item, but rather finds and replaces all instances of the string that needs updating.

The nice thing here is that the “Form” item will not be modified but all the instances of “Alfred Test1″ will be replaced with “John Doe”. The screenshot below shows the Item List for an example document modified by the formula we entered:

This screenshot shows the Item list of one of the documents modified in this tip.Note the following:
a) the ‘Form’ item was untouched
b) the instances of ‘Alfred Test1′ were all replaced with “John Doe”…
c) …with the one exception being the special, un-editable ‘$UpdatedBy’ item

Keep me posted on more like this!

We’ll keep you up to date on new technical articles, tips and tricks, and upcoming events.

We don’t spam, and you can unsubscribe at any time.

Share This