Nov 7, 2017 9:12 AM
12 Posts

@DBLookup in View Selection - Example

  • Category: Domino Designer
  • Platform: All Platforms
  • Release: 9.0.1
  • Role: Developer
  • Tags: @DBLookup View Selection
  • Replies: 1

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

Nov 9, 2017 3:06 PM
215 Posts
That's an interesting idea ... the @TextToTime("Today") may stick at an earlier day though...

I too have had a lot of "fun" dealing with this kind of situation, and honestly, I wish the selection formula could be profiled much like the column formulas are.

Still, I know the selection formula can cause something of a drain on performance. The column formula does, too.

Cool idea though!

One other thought -- I've also done things like this with folders. I dunno, it works to a point, but it can also be slow-ish. I may try this out if something else breaks.