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).
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).
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.
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.
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:
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: