« Standardized and Simple-to-Use Keyword Formulas | Main| User-Friendly Agent for Archiving Notes Email Attachments »

Validate a Field Value's Uniqueness Across All Documents

QuickImage Category

Code 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";
VIEW := "ViewbyTitle";
KEY := Title;
@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
Dim session As New NotesSession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Dim fieldvalue As Variant
dupeflag As Integer
db = Session.CurrentDatabase
= 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)
doc = dc.getfirstdocument
Do While Not doc Is Nothing
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
doc = dc.getnextdocument(doc)
CheckDuplicateValues = dupeflag
Exit Function
= False
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.



1 - Cool - should it exit the while loop after the first true dupeflag though?

2 - Thanks Jim, good point. I made the necessary changes to use a Do While instead of While loop and exit it early when the first duplicate is found. If you're not too averse to Goto statements, you could break out of a while loop using that approach.

3 - In lotusnotes ,i am validating a field using javascript and on save i am validating the same field in lotus script.so 2 popup messages r coming when i save a document without exiting from the field. how to avoid this?

Your Host

Kevin Pettitt View Kevin Pettitt's profile on LinkedIn

Tools I Use

Idea Jam

Subscribe to This Blog

 Full Posts  Comments

netvibes Add to Netvibes


Hosted by



This site is in no way affiliated, endorsed, sanctioned, supported, nor blessed by Lotus Software nor IBM Corporation, nor any of my past or future clients (although they are welcome to do so). The opinions, theories, facts, etc. presented here are my own and in no way represent any official pronouncement by me on behalf of any other entity.

© 2005-2019 Kevin Pettitt - all rights reserved as listed below.

Creative Commons License
Unless otherwise labeled by its originating author, the content found on this site is made available under the terms of an Attribution / NonCommercial / ShareAlike