Find and Replace for power-users
Posted by Theo Heselmans on July 25th, 2007
In every application I develop for customers, I include a power-user tool to allow the users (whom have that privilege), to do a global find & replace on data.
This saves me from creating silly agents every time they need to 'clean-up' their data.
This is how the dialog looks like:
The user has to know the fieldname he/she needs to update, and can provide a list of values to change from/to.
Now let's take a look at the code behind it.
The first part is the formula part on this dialogbox. It consists of 2 hidden fields:
SearchString, computed text field with the following formula:
"Form = \"" + DocumentType + "\" & " + FieldName + "*= "
+ @Implode("\"" + ValueFrom+ "\"";":")
(did you notice the *= in the above formula ?)+ @Implode("\"" + ValueFrom+ "\"";":")
ExecuteString, again a computed text field: FromList:=@Implode("\"" + ValueFrom+ "\"";":");
ToList:=@Implode("\"" + ValueTo+ "\"";":");
"@unique(@trim(@replace(" + FieldName + ";"+FromList+";"+ToList+")))"
ToList:=@Implode("\"" + ValueTo+ "\"";":");
"@unique(@trim(@replace(" + FieldName + ";"+FromList+";"+ToList+")))"
Then there is the LotusScript Agent part (this is an excerpt, with the 'gut' of the code):
'get info from dialog
sExecute = dlg.ExecuteString(0)
sFieldName = dlg.FieldName(0)
'get all documents of that form that need to be modified through dbsearch
Set dc = db.Search(dlg.SearchString(0), Nothing, 0)
Set doc = dc.GetFirstDocument
While Not (doc Is Nothing)
eval = Evaluate (sExecute, doc)
Call doc.ReplaceItemValue(sFieldName, eval)
Call doc.Save(False,False)
Set doc = dc.GetNextDocument(doc)
Wend
sExecute = dlg.ExecuteString(0)
sFieldName = dlg.FieldName(0)
'get all documents of that form that need to be modified through dbsearch
Set dc = db.Search(dlg.SearchString(0), Nothing, 0)
Set doc = dc.GetFirstDocument
While Not (doc Is Nothing)
eval = Evaluate (sExecute, doc)
Call doc.ReplaceItemValue(sFieldName, eval)
Call doc.Save(False,False)
Set doc = dc.GetNextDocument(doc)
Wend
Using the 'Evaluate' function is not a real drawback, and, as I and others have done some testing/timings on it, is not really 'slow'.
Updated: I created a small database with 2 design elements (the subform, and the agent): Blog_FindAndReplace.zip
Category: Domino/Notes | 
Technorati: Show-n-Tell Thursday, SnTT
Comments (5)
Just the sort of great idea I like to incorporate into SuperNTF, thanks!
I would probably try to "help" users by presenting some kind of field selection list, perhaps driven by a sample doc as opposed to the source form. This would show fields on computed subforms and avoid showing cfd fields.
Kevin, glad you're alive and kicking (and even able to type) !
Good idea to show the list of fields (based on a document) on the fly. I'll add it to my next project.
That is great. Excellent thinking!
:-) stw
Nice! Changing values is such a common task, I should alos have this in every database. Could you send me a sample database with the two elements?
@Florian
I've attached a db with both elements. Enjoy.