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 :Show-n-Tell+Thursday, SnTT)