As all the documentation states, you can’t use a DBLookup in a view selection formula. However, you can use LotusScript. The following is an example of how to do this. We maintain a simple address book for people. I have several workflows that monitor badge-ins to our facililty. Only thing is that I only want to monitor those who are in certain business units. I keep those Business units in a keyword document so I only want them reported in this view. Of course BUs change infrequently and I want to avoid hard coding them into this view, I want to monitor those in the selected BUs who don’t have a badge number assigned.
The following QueryOpen code in the view will accomplish just that.
Sub Queryopen(Source As Notesuiview, Continue As Variant)
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim kView As NotesView
Dim kDoc As NotesDocument
Dim keywords As Variant
Dim selection As String
Set db = session.CurrentDatabase
Set kView = db.GetView(“Keywords”)
Set kDoc = kView.GetDocumentByKey(“Check-In Business Units”)
keywords = kDoc.GetItemValue(“Keywords”)
Set view = source.View
’ The “Keywords” field will have a table returned which we want to convert to a string with the “:” stuff in between to make
’ a formula list value.
selection = |cond:=@If(Active=“Active”;@True;@If(EndDate < @TextToTime(“Today”);@False;@True));SELECT Form=“Person” & Cond & BadgeID=“” & @IsMember(BU;“|+Join(Keywords,|”:“|)+|”)|
’ Only do this if the selection formula would actually change
If selection <> view.SelectionFormula Then
view.SelectionFormula = Selection
Call view.Refresh
End If
End Sub
The only draw-back is that whoever uses this view will have to have the ACL privilege to be able to create shared folders/Views. For this one it’s not a problem as the only people who use this view are [DBGod] and [Admin] roles who will always have this privilege.
I also placed this in the QueryOpen event because putting it in the global “Initialize” would result in an empty NotesUIWorkspace (but only if it’s not in debug mode). Meh, placing it here saves a couple of lines of code as the UIView is readily available…
If you found this useful, post a reply. Thanks