Evaluate and dblookup

HCL Domino Domino Forum
1

I have some code in the exit function of a field on a document. When a person exists that field I want to do a dblookup to see if a document with the same criteria exists. I have put the @DBLookup formula in a button on the form using the formula language and it works. But, when using evaulate, I can not return anything. Can someone help me?

Code is this:

Sub Exiting(Source As Field)

Dim s As New NotesSession

Dim ws As NotesUIWorkspace

Dim db As NotesDatabase

Dim uidoc As NotesUIDocument

Dim doc As NotesDocument

Dim RepID As String

Dim enum As String

Dim creason As Variant

Dim tdate As String

Dim tlookup As String

Dim lookup789 As Variant

Set ws = New NotesUIWorkspace

Set db = s.CurrentDatabase

REM Get document used for passing data

Set uidoc = ws.CurrentDocument

Set doc = uidoc.Document



enum = doc.EmployeeNumber(0)

creason = doc.DChangeReason(0)

tdate = doc.Date(0)

tlookup = enum + " - " + Cstr(creason) + " - " + tdate

Call doc.ReplaceItemValue("Lookup1", tlookup)



RepID =  "852572E3:0056DE55"



lookup789 = Evaluate({@DbLookup( "" : "NoCache"; RepID ; "Admin\\789LookupView";  "}+ tlookup +{"; 11)})

' lookup789 = Evaluate(|@DbLookup( "" : "NoCache"; "| + RepID + |" ; "Admin\\789LookupView"; "| + tlookup + |"; 1)|)

If Isnull(lookup789) Then

	mess = workspace.Prompt(PROMPT_OK, _

	"Duplicate Entry", "The system already contains an entry with this inform.  Once you hit okay, this document will close.")

	Exit Sub

Else

End If

End Sub

2

Subject: evaluate and dblookup

Jamie,

Not sure if this is the culprit, but I’d use & instead of + for your formula. I like your second evaluate better than your first one.

regards,

raphael

3

Subject: evaluate and dblookup

You’d need to change how your RepID is being handled. It is a variable and you are not treating it as such in your evaluate string. So, it should look like this…

lookup789 = Evaluate({@DbLookup( “” : “NoCache”; “} & RepID & {” ; “Admin\789LookupView”; “}+ tlookup +{”; 11)})

I use the function below to do formula dblookups and pass in the paramaters you’d normally use:

So if you used it your code would look like this:

lookup789 = DBLookup (“Notes”, “NoCache”, “”, “”, Admin/789LookupView, tlookup, 11)

If lookup789 = “Error” Then

mess = workspace.Prompt(PROMPT_OK, _

“Duplicate Entry”, “The system already contains an entry with this inform. Once you hit okay, this document will close.”)

Exit Sub

Else

'do you need an else here???

End If

HERE’S MY FUNCTION:

Function DBLookup (strClass As String, strNoCache As String, strServer As String, _

strDatabase As String, strView As String, strKey As String, strReturn As String) As Variant

'*********************************************************

'This function accepts the dblookup parameters in the same way as @DbLookup.

'This function is faster than using script to get a db object,

’ view object, and doc object. A string array is returned.

quotes = Chr(34)

strFormula = “@if(@iserror(@unique(@trim(@DbLookup(” & quotes & strClass & quotes & “:” & quotes & strNoCache & quotes & “;” & quotes & strServer & quotes & “:” &_

quotes & strDatabase & quotes & “;” & quotes & strView & quotes & “;” & quotes & strKey & quotes & “;” & strReturn & “))));”& quotes & “Error” & quotes & “;”& “@unique(@trim(@DbLookup(” & quotes & strClass & quotes & “:” & quotes & strNoCache & quotes & “;” & quotes & strServer & quotes & “:” &_

quotes & strDatabase & quotes & “;” & quotes & strView & quotes & “;” & quotes & strKey & quotes & “;” & strReturn & “))))”

’ Print strformula

DbLookup = Evaluate( strFormula )

End Function

4

Subject: RE: evaluate and dblookup

I have changed teh line to this and I still do not get a value

lookup789 = Evaluate({@DbLookup( “” : “NoCache”; “}& RepID &{”; “Admin\789LookupView”; “}& tlookup &{”; 11)})

5

Subject: RE: evaluate and dblookup

I have changed the line to this and I still do not get a value

lookup789 = Evaluate({@DbLookup( “” : “NoCache”; “}& RepID &{”; “Admin\789LookupView”; “}& tlookup &{”; 11)})

6

Subject: RE: evaluate and dblookup

IsNull(lookup789) will always return false.

Use IsEmpty(lookup789) instead.

7

Subject: evaluate and dblookup

Jamie,

I don’t see anything wrong with the Lookup code itself – I took your code and modified it a little to run against my own database/view key and everything worked well so you need to look at yout KEY and ReplicaID and View name. Try running it in Debug and see what your KEY is really returning – is it what you expected? does that key appear in the view?

Also, as someone else suggested, use IsEmpty to test the result – it will be “Empty” if there are no results or if there was an error such as the view not being found, etc.

Also – where are you getting the “Error” from ??? Your original code had "If lookup789 = “Error” in it – that won’t cut it. At the very least you need to test element zero – I added (0) to the line, and even then, unless I’m missing something you’ll never see this unless your lookup returns “Error” as the first or only entry. I also added some messagebox code to loop through the results and dieplay them in a single msgbox.

lookup789 = Evaluate(|@DbLookup( "" : "NoCache"; "| & RepID & |"; "Admin\\789LookupView"; "| & tlookup & |"; 11 )| )



If Isempty(lookup789) Then

	Msgbox "lookup789 IsEmpty"

Else	

	If lookup789(0) = "Error" Then

		mess = workspace.Prompt(PROMPT_OK, _

		"Duplicate Entry", "The system already contains an entry with this inform. Once you hit okay, this document will close.")

		Exit Sub

	Else

		Forall v In lookup789

			msg$ = msg$ & v & Chr(13) 

		End Forall

		Msgbox "lookup789 = " & Chr(13) & Chr(13) & msg$

	End If

End If