Validate a Field Value's Uniqueness Across All Documents
Category Show-n-Tell ThursdayCode Updated 8/23/2006 per Jim Knight's comment...
In any database, there are often instances where you want to ensure that a given field value is unique across a family of documents. In the case of automatically generated IDs and such, you can ensure uniqueness very easily using either some variation of @Unique or a sequential counter of some sort. Where it gets interesting is for user-entered fields like "Title", where these approaches do not apply.
When this happens, the typical approach is to create a view of documents sorted by the field in question, then use something like this in the field's input validation:
CLASS := "";
NOCACHE := "NoCache";
SERVERDB := "";
VIEW := "ViewbyTitle";
KEY := Title;
FIELDNAME := 1;
Lookup := @DbLookup(CLASS : NOCACHE; SERVERDB; VIEW; KEY;FIELDNAME);
@If(@IsError(LookUp) | Lookup = ""; @Success; @Failure("Title is not unique"))
This is all fine and great, but unless you already have a sorted view available for a specific field, you'll have to create one under this approach. In instances where you have several fields you need to check for uniqueness, this gets to be a drag, so I wrote a little Lotusscript function that might come in handy as long as the number of documents in question is moderate, such as with configuration documents. Here's the code, which should be pretty self-explanatory:
.
Function CheckDuplicateValues(currentdoc As NotesDocument, fieldname As String, formname As String) As Integer
On Error Goto ErrorHandler
Dim session As New NotesSession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Dim fieldvalue As Variant
Dim dupeflag As Integer
Set db = Session.CurrentDatabase
fieldvalue = currentdoc.GetItemValue(fieldname)
dupeflag = False
' We need to trap for quotation characters in the field value so the following selection formula doesn't break. Other special characters may need handling depending on circumstance
fieldvalue(0) = ReplaceSubstring(fieldvalue(0), |"|, |\"|)
searchformula$ = |Form = "| & formname & |" & | & fieldname & | = "| & fieldvalue(0) & |"|
Set dc = db.Search(searchformula$, Nothing, 0)
Set doc = dc.getfirstdocument
Do While Not doc Is Nothing
If doc.NoteID <> currentdoc.NoteID Then 'Skip the current document since we're already in it
dupeflag = True 'i.e. found a *different* document with the same project name
Exit Do 'No need to check further
End If
Set doc = dc.getnextdocument(doc)
Loop
CheckDuplicateValues = dupeflag
Exit Function
ErrorHandler:
CheckDuplicateValues = False
Resume
End Function
This should work fine in either the Notes client or on the web. By the way, a possible adaptation of this code would be to allow the user to open the other document which contains the duplicate value and "fix" the problem that way.
Enjoy!
- 
Full Posts
Comments
Comments
Posted by Jim Knight At 08:47:59 AM On 08/23/2006 | - Website - |
Posted by Kevin Pettitt At 01:50:25 PM On 08/23/2006 | - Website - |
Posted by dhanabal At 02:49:39 AM On 10/09/2006 | - Website - |