SNTT : Testing for Unique Multi Values in a Lotus Notes Form field

Last week I posted on Testing for Unique values in a Lotus Notes Form field, then Nathan’s Comment made me reprise the code. (Learn and share or “CITOKATE”)

I promised to show how to test for Unique multiple values (or compound values), Like making sure the names (First Name + Last Name) are unique for each Company (as represented by Company Id or Name which It test for last week), on a person form.

Originally I was using an array of field names to build my search string to do me db.search, but that feels very obsolete now.

Happily the new “isValueUniqueForFormViaView” function can also be used if you build the view (Lookup People Unique to Company) sorted by a first field which is CompanyId+FirstName+LastName, with a select on the valid people documents (SELECT FORM=”PEOPLE” & STATUS = “ACTIVE”).

and call it like this :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Sub Querysave(Source As Notesuidocument, Continue As Variant)

Dim ViewName as string
Dim key As String
key = Source.Document.GetItemValue(“CompanyId” )(0)+_
Source.Document.GetItemValue(“FirstName” )(0) +_
Source.Document.GetItemValue(“LastName” )(0)
ViewName = “Lookup People Unique to Company”
If Not IsValueUniqueForFormViaView (Source.Document,_
ViewName,_
Key) Then
Messagebox “The First and Last Name must be unique.”,_
MB_OK+MB_ICONSTOP,_
db.title + ” Business Rule Error”
Continue = False
Exit Sub
End If

end Sub

and the Function is the same, although fixed up a bit :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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
Dim vc As NotesViewEntryCollection

Set db = session.CurrentDatabase
Set view = db.GetView( ViewName )

If view Is Nothing Then
Msgbox "Error with View " &_
ViewName,16 ,_
db.Title + " Checking for Uniqueness"
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
' same document , so we should be okay
Goto HandleTrue
End If

End If

HandleFalse:
IsValueUniqueForFormViaView = False
Exit Function
HandleTrue:
IsValueUniqueForFormViaView = True

End Function

(technorati.com tags :Show-n-Tell+Thursday, SnTT)