DBCoulmn vs Document Collection

HCL Domino Domino Forum
1

On document creation, I need to check the contents of a field on field exit against a view to validate information. If the information is in the view, a pop up message is initiated. All was working fine until the view hit about 2700 records. I assumed it had to do with the 64k limit on @dbColumn, so I recoded to use a NotesDocumentCollection instead. In both instances, it works fine until 2700 entries in the view, and then it just doesn’t seem to work. No errors, it just moves on past the field with no validation. I’ve debugged it, and don’t see any errors.

Original Code:

Sub Exiting(Source As Field)

' checks GrpName and GrpNmDom fields against Sailpoint view to direct csr to Sailpoint if required - mb 5/2014

Dim workspace As New NotesUIWorkspace

Dim session As New NotesSession

Dim dc As notesDocumentCollection

Dim uidoc As NotesUIDocument

Dim doc As NotesDocument

Dim item As NotesItem

Dim db As NotesDatabase

Dim MatchView As NotesView ' view to pick GroupName and Domain from

Dim EGrpCk As Variant, EDomCk As Variant   'Variables to hold results of  GrpCk and DomCk 

Dim cmd As String, DPlusGp As String

Dim GrpName As String, GrpNmDom As String, GrpNm As String, GrpDom As String, SailPointCk As String 'fields in the form

Dim BoxType As String, BoxMsg As String	

Set db = session.CurrentDatabase	

Set uidoc = workspace.CurrentDocument

Set doc = uidoc.Document





Set MatchView = db.getview("Sailpoint")

Set doc = MatchView.GetFirstDocument

GrpNm = uidoc.FieldGetText("GrpName")

GrpDom= uidoc.FieldGetText("GrpNmDom")

DPlusGp = GrpDom + " " +GrpNm



uidoc.Refresh

EGrpCk = Evaluate( {@Trim(@unique(@DbColumn("":""; "";"Sailpoint"; 3)))},doc)

uidoc.Refresh



BoxMsg = |Management of this group has been moved to Sailpoint. 

                                 This request will not be saved in this database.

                                  For help with removal of IDs, Groups inside other groups or Generic ID provisioning, see SailPoint Help.  |



Forall r In EGrpCK

	If r = DPlusGp  Then

		Messagebox BoxMsg, MB_ICONSTOP,  "SAILPOINT ALERT"	

		Call uidoc.FieldSetText("SailPointCk","Yes")

		uidoc.Refresh

		Call uidoc.FieldSetText("GrpName", "")

		Call uidoc.GotoField("GrpName")

					

	End If

End Forall

End Sub

Modified code using Document Collection

Sub Exiting(Source As Field)

' checks GrpName and GrpNmDom fields against Sailpoint view to direct csr to Sailpoint if required - mb 5/2014

Dim workspace As New NotesUIWorkspace

Dim session As New NotesSession

Dim dc As notesDocumentCollection

Dim uidoc As NotesUIDocument

Dim doc As NotesDocument

Dim item As NotesItem

Dim db As NotesDatabase

Dim MatchView As NotesView ' view to pick GroupName and Domain from

Dim EGrpCk As Variant, EDomCk As Variant   'Variables to hold results of  GrpCk and DomCk 

Dim cmd As String, DPlusGp As String

Dim GrpName As String, GrpNmDom As String, GrpNm As String, GrpDom As String, SailPointCk As String 'fields in the form

Dim BoxType As String, BoxMsg As String	

Set db = session.CurrentDatabase	

Set uidoc = workspace.CurrentDocument

Set doc = uidoc.Document





Set MatchView = db.getview("Sailpoint")

Set doc = MatchView.GetFirstDocument

GrpNm = uidoc.FieldGetText("GrpName")

GrpDom= uidoc.FieldGetText("GrpNmDom")

DPlusGp = GrpDom + " " +GrpNm





uidoc.Refresh

Set dc = matchview.GetAllDocumentsByKey(GrpNm,True)

uidoc.Refresh



BoxMsg = |Management of this group has been moved to Sailpoint. 

                                 This request will not be saved in this database.

                                  For help with removal of IDs, Groups inside other groups or Generic ID provisioning, see SailPoint Help.  |





Do Until GrpNm <> ""

	Messagebox BoxMsg	, MB_ICONSTOP,  "SAILPOINT ALERT"	

	Call uidoc.FieldSetText("SailPointCk","Yes")

	uidoc.Refresh

	Call uidoc.FieldSetText("GrpName", "")

	Call uidoc.GotoField("GrpName")

	Set doc = MatchView.GetNextDocument(doc)

Loop

End Sub

Any suggestions would be appreciated. Ultimately the view will have about 20,000 entries. The view itself is very simple, just 2 columns with the 1st one being sorted.

2

Subject: Your code…

Your code does not make sense to me. Could be because I worked until 2am last night and had to be back at work by 8.30am, so I am tired…

But what you are doing is to get a document collection, then update the open NotesUIDocument (uidoc) once for each NotesDocument in the collection. But you are just writing the same values over and over again, as well as displaying a prompt over and over again. In addition, you do multiple uidoc.Refresh on the code, none of them are needed.

It does simply not make sense to me.

What I think you want to do is someting like this:

Sub Exiting(Source As Field)

’ checks GrpName and GrpNmDom fields against Sailpoint view to direct csr to Sailpoint if required - mb 5/2014

Dim workspace As New NotesUIWorkspace

Dim uidoc As NotesUIDocument

Dim session As New NotesSession

Dim db As NotesDatabase

Dim view As NotesView

Dim col As NotesViewEntryCollection

Dim entry as NotesViewEntry

Dim doc As NotesDocument

Dim groupname As String

Dim domain As String

Dim combined As String

Dim msg as String

msg = |Management of this group has been moved to Sailpoint.

This request will not be saved in this database.

For help with removal of IDs, Groups inside other groups or Generic ID provisioning, see SailPoint Help. |

Set uidoc = workspace.CurrentDocument

groupname = uidoc.FieldGetText(“GrpName”)

domain = uidoc.FieldGetText(“GrpNmDom”)

combined = domain + " " + groupname

Set db = session.CurrentDatabase

Set view = db.GetView(“Sailpoint”)

Set col = view.GetAllEntriesByKey(groupname,True)

Set entry = col.GetFirstEntry()

Do Until entry is Nothing

If entry.ColumnValues(1) = combined Then

MsgBox msg , MB_ICONSTOP, “SAILPOINT ALERT”

Call uidoc.FieldSetText(“SailPointCk”,“Yes”)

Call uidoc.FieldSetText(“GrpName”, “”)

Call uidoc.GotoField(“GrpName”)

Exit Do

End If

Set entry = view.GetNextEntry(entry)

Loop

End Sub

This assumes that the second column in the view conatins the domain and group name separated with a space.

I removed a lot of code that was not needed/used. You really want to keep your code clean and easy to read. You may want o reconsider the way you name your variables, there are ways to make it much easier to maintain. :slight_smile:

3

Subject: Your code…

Thanks so much for the response. Your code worked with one tweak, but I experienced the same limit - at about 2700 entries in the view the data was no longer validated on field exit. I changed the code to look at only the groupname and it then did work until I got to about 4000 records in the view.

Set uidoc = workspace.CurrentDocument

groupname = uidoc.FieldGetText("GrpName")

domain = uidoc.FieldGetText("GrpNmDom")

combined = domain + " " + groupname 



Set db = session.CurrentDatabase 

Set view = db.GetView("Sailpoint")

Set col = view.GetAllEntriesByKey(groupname,True)

Set entry = col.GetFirstEntry()

Do Until entry Is Nothing

	'If entry.ColumnValues(1) = combined Then

	If entry.ColumnValues(0) = groupname Then

		Msgbox msg , MB_ICONSTOP, "SAILPOINT ALERT" 

		Call uidoc.FieldSetText("SailPointCk","Yes")

		Call uidoc.FieldSetText("GrpName", "")

		Call uidoc.GotoField("GrpName")

		Exit Do

	End If

	Get entry = col.GetNextEntry(entry)

Loop

4

Subject: Just to clarify

If you find a match, you want to pop up an alert, and set the groupname field to null?Why not drop out of the forall loop at that point?