« 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";
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!

Comments

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

KevinPettitt.jpg
Kevin Pettitt View Kevin Pettitt's profile on LinkedIn

Tools I Use

Idea Jam

Subscribe to This Blog

 Full Posts  Comments

MyYahoo
netvibes Add to Netvibes

Contact

Hosted by

OpenNTF

Disclaimer

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-2017 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