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

Posted on by — No Comments ↓

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 :, )

Leave a Reply