Evaluate statement (@DbLookUp) doesn't work with Lotusscript

Question

The last week I asked how to solve an error in an evaluate statement (Error in Evaluate statement macro).

Once fix it, I have other error with the same evaluate statement, it doesn't give me any value.

I will describe what I have and what I try.

@DbLookup in Calculate Text

I have this code into in an calculate Text and it works fine.

suc := @Trim(@Left(LlcPoliza;2));
_lkp := _lkp := @DbLookup("":"NoCache";"C1256EAD:00478951";"People2"; "D"+suc; "FullName");
@If( @IsError( _lkp ) ; " " ; _lkp );
@Name([CN];_lkp)

LlcPoliza is a document field (doc.LlcPoliza) and in a document it has for example the value C2H2H2.

The formula give first the value C2 and then look up into People2 who is D+C2 and give me a person.

It works fine.

Evaluate Statement (@DbLookup) in a Class

I have a class DirectorSucursal.

Class DirectorSucursal

    Private m_branch As String

    'Constructor class
    Public Sub New (branch)
        Dim subString As String
        subString = Left(branch, 2)
        me.m_branch = subString
    End Sub

    'Deleter Class
    Public Sub Delete

    End Sub

    'Sub show the code about Suc
    Public Sub GetCodSuc
        MsgBox m_branch
    End Sub

    'Function get the name director
    Public Function getNameDirector As String
      Dim varResult As Variant
      varResult = Evaluate({@DbLookup("":"NoCache";"C1256EAD:00478951";"People2"; "D} & m_branch & {"; "FullName)"})
      getNameDirector = CStr( varResult(0) )
    End Function

End Class

Then, in a button I instantiate the new object DirectorSucursal with the parameter of the field doc.LlcPoliza(0) like this.

Sub Click(Source As Button)
    Dim director As New DirectorSucursal(doc.LlcPoliza(0))
    director.GetCodSuc
    director.getNameDirector
end Sub

The field doc.LlcPoliza(0) has the value C2H2H2. GetCodSuc show the value C2, but the function getNameDirector doesn't work.

It shows the error: Operation failed

Evaluate Statement (@DbLookup) in click button

I have tried the same but into a click sub.

Sub Click(Source As Button)
    Dim subString As String
    subString = Left(doc.LlcPoliza(0), 2)

    Dim eval As String
    eval = Evaluate({@DbLookup("":"NoCache";"C1256EAD:00478951";"People2"; "D} & subString & {"; "FullName)"})

    Msgbox eval
End Sub

The field doc.LlcPoliza(0) has the value C2H2H2. But it doesn't work

It shows the error: Operation failed

My question is: what am i doing wrong? Why the code works fine in a calculate text with @Formula but with Lotusscript not?

Thanks.

EDIT 1:

I have added and Error Goto, modified the class code, modified @dblookup in calculate text and I have this error:

Error in EVALUATE macro

Answer 1

1) My suggestion is to never (or at least very seldom) use Evaluate() in Lotusscript. You have proper Lotusscript functionality to do almost everything. One of the major reasons is that the code is very hard to debug (which is what you are now experiencing).

2) Don't use extended notation when you work with fields. The best practice is to use the GetItemValue and ReplaceItemValue methods of the NotesDocument class for performance reasons as well as compatibility reasons.

3) In the examples with buttons you have a reference to doc, but it is never declared or initialized in the code. If you would use Option Declare at the top of your code you would catch these kinds of errors.

4) I also reccomend against using replica ID to reference databases, that makes it very hard to maintain in the future. Unless you have a very good and convincing reason, reference them by server and filename instead.

I would suggest you refactor your code to something like this:

'Function get the name director
Public Function getNameDirector() As String
  Dim db as NotesDatabase
  Dim view as NotesView
  Dim doc as NotesDocument
  Dim key as String
  Dim fullname As String
  Dim varResult As Variant

  Set db = New NotesDatabase("Server/Domain","path/database.nsf")
  If db Is Nothing Then
      MsgBox "Unable to open 'path/database.nsf'"
      Exit Function
  End if
  Set view = db.GetView("People2")
  If view Is Nothing Then
      MsgBox "Unable to access the view 'People2'"
      Exit Function
  End if
  key = "D" & m_branch 
  Set doc = view.GetDocumentByKey(key)
  If doc Is Nothing Then
      MsgBox "Could not locate document '" & key & "'"
      Exit Function
  End if
  fullname = doc.GetItemValue("FullName")(0)
End Function

Ando of course update the button actions in the same way.

Yes, it is a few lines longer, but it is much more readable and easier to maintain and debug. And you have error handling as well.

Answer 2

Please read documentation and use help! evaluate always returns an ARRAY, as stated in the help:

Return value
variant
The result of the evaluation. A scalar result is returned.

To make your code return a STRING you need to change it like this:

Public Function getNameDirector As String
    Dim varResult as Variant
    varResult = Evaluate({@DbLookup("":"NoCache";"C1256EAD:00478951";"People2"; "D} & m_branch & {"; "FullName")})
    getNameDirector = Cstr( varResult(0) )
End Function

The CStr is just there for the case where the @DBLookup returns an error or a number (both possible)

Just a few things in general:

• NEVER write even one line of LotusScript- code without error handler. It will cause you trouble FOR SURE. If you had error handling in place, then it would have told you in which line the error occured...
• NEVER use the result of @DBLookup without checking for @IsError... It will cause lot of troubles when the lookup fails.
• IF you use @Iserror, then don't do the Lookup twice, assign the lookup to a variable and check that one for @Iserror, like this. Otherwise performance will go down in big forms:

Example:

_lkp := @DbLookup("":"NoCache";"C1256EAD:00478951";"People2"; "D"+suc; "FullName");
@If( @IsError( _lkp ) ; " " ; _lkp )

EDIT: As Knut correctly stated in his answer the real cause for the error was a typo in the formula ( Fullname)" instead of Fullname") that I fixed in my example as well.

Answer 3

Change your last part in @DbLoookup code line to:

 "FullName")})