SNTT : Test for Unique values in a Lotus Notes Form field

A new Year and more Show-n-Tell+Thursday Lotus Domino examples! The pressure is on.

Suppose you need to ensure that a value is unique for a field for a given form and the documents created by that form. Like a “CompanyName” field on a “Company” form. How would you go about that in the Lotus Notes client?

First, would I assume that this is a important field, therefore it’s required, so in the forms QuerySave you need to do field validated on the from (you could use the techniques I described in SnTT : LotusScript Required Field Validation without pain, but feel free to use you own). You could do validated before or after.

I would also test to see if the value had changed before testing for uniqueness. You don’t need to test it every time!

But the QuerySave is where to test for uniqueness, before the document is saves, and if that fails I pop up a nice user friendly error message and the stop the save to change the value, or close with out saving.

Sub Querysave(Source As Notesuidocument, Continue As Variant) If Not IsValueUniqueForFormViaView (Source.Document,_ "LookupCompanyByName",_ Source.Document.CompanyName) Then Messagebox "The Company Name must be unique.",_ MB_OK+MB_ICONSTOP,_ db.title + " Business Rule Error" Continue = False Exit Sub End If End Sub

Update : I’ve update the code sample here, since it was breaking things in IE. I’ve revised the code along lines suggested by Nathan’s very valid point in the comments.

A big assumption here is that use have a view, selecting just valid Company forms and indexed by “Company Name” values ,to pass to the function.

To do this I need the back-end document, and I need to know the Form (this is not on the document until after the form is saved, hence I pass it), and the single value field being testing.

I build a search string which limits the results to the Form, and uses the trim and upper case function to see if the value is already there. You can add AddtionalCondtions to exclude some documents on a static test like: (the pipe | is equal to a quote ” character, and makes it easier to make sure I closed all my quotes) |Status = “Current”| or |Status = “| + Source.Document.Status(0) + |”| .

Once I have a document collection, I look at the number of documents in the collection (count). Values of more than 1 or zero are easy, if the value is 1 then I ask “is this the same document I’m trying to save or not”, if not the the value is not unique.

Update : See Nathan’s very valid point in the comments. As a alternative I would modify the function by passing the viewname used to test uniqueness. I’ve Added a “IsValueUniqueForFormViaView” function to the attached code that works just that way. (Thanks Nathan! CITOKATE!)

Function IsValueUniqueForFormViaView (Doc As NotesDocument, ViewName As String, FieldValue As Variant ) 'Doc,the back-end Document we are testing. 'ViewName,a view sorted by the field name, ' containing the documents we want to test. 'FieldValue, the value we what to test for uniqueness Dim session As New NotesSession Dim db as Notesdatabase Dim view AS NotesView Set db = session.CurrentDatabase Set view = db.GetView( ViewName ) If view Is Nothing Then MsgBox "Error Problem with View " & ViewName IsValueUniqueForFormViaView = False Exit Function End If Set vc = view.GetAllEntriesByKey( FieldValue, True ) If vc.count > 1 Then Goto HandleFalse If vc.Count = 0 Then Goto HandleTrue If vc.Count= 1 Then If Doc.UniversalID = vc.GetFirstEntry.document.UniversalID Then Goto HandleTrue end if ' we just found the same document , so we should be okay End If HandleFalse: IsValueUniqueForFormViaView = False Exit Function HandleTrue: IsValueUniqueForFormViaView = True End Function

I’ve attached the above code to make copy and paste easy: IsValueUniqueForForm.txt.

The Function(s) should also be usable for a Domino server web form. (Although this is currently untested by me). I have tested this in R6.x, although it should work R5 and everything more recent.

So what to do if you have a bunch of fields which must be unique for the from? (like First Name and Last Name) ? That’s for next week ! (technorati.com tags :, )

2 Replies to “SNTT : Test for Unique values in a Lotus Notes Form field”

  1. Ouch. Using db.search in your validation step for every required unique value is going to seriously compromise scalability of your system. It’s one of the slowest methods you can use in Lotuscript.

    If you really need to ensure uniqueness of a field, let the indexer do the hard part for you by creating a view. Then use .getAllEntriesByKey.

  2. Pingback: False Positives » Blog Archive » SNTT : Testing for Unique Multi Values in a Lotus Notes Form field