How to get the Email Address from names.nsf domino directory - Lotus Notes

Question

I have an employee info form that has fields Employee No, Employee Name & Employee Email Address. When registered a new employee info, I am not able to get Email address ( from the Domino Directory names.nsf), the employee name from the form has to match with the name in directory and bring the mail address.
Please help me as am new to Lotus notes :( I tried this formula...for Email address field...

server := @Name([CN]; @Subset(@DbName; 1));
err := "No email address found, plz chk Domino Directory";
@If(EmpName != ""; @DbLookup("":"NoCache";server:"names.nsf";"People";Name;4); Email);
@If(@IsError(err)=err)

But this is not working, I get only the error but not the email address. Plz help me :( I then tried with DbColumn... itz populating all the email addresses in the email field.
My requirement is bring the email address of the name matching with the employee name registerd from the domino directory, if email addr not exist, display error.

Answer 1

Wow, you are new to Lotus Notes. That formula is a mess! :-)

First, the "People" view is not appropriate for a @DBLookup. The first column is not sorted. Check it out with Designer.

I usually use the hidden view ($Users). The first column has numerous variations of a person's name and is sorted. The email address is available in column 8, so something like this might work:

result := @DbLookup(""; server_name:"names.nsf"; "($Users)"; name_to_match; 8; [FailSilent]);

With [FailSilent] result will be "" if there is an error or no match. If it isn't working and you really think it should, take the [FailSilent] out out and use @Prompt([OK]; "Result"; @Text(result)) to see what the error is.

Unless you expect the Person documents in the names.nsf to be getting updated frequently, you can leave the "NoCache" out of your @DbLookup. It will be faster.

Answer 2

Mark has pretty much answered your question.
After formatting the question to make the formula more readable, I noticed what could still cause a problem: Assuming this is a formula for the value of a computed field Email, the formula should return the result of the @DbLookup, if successful. The one you use doesn't seem to. Also, the last line seems to be mistyped.
So, combining Mark's approach with your formula and re-arranging the code a little while addressing your comment:
1. No need to go further if field EmpName is empty, just return the current contents of Email:
@If(EmpName = ""; @Return(Email); "");
2. For server_name you can do what you use the first line of your formula, or just:
server := @Subset(@DbName; 1);
3. Now do the lookup, the return value is the value that will go to the Email field, so there should be no semicolon at the end:
@DbLookup(""; server:"names.nsf"; "($Users)"; EmpName; 8; [FailSilent])
And, yes, the ($Users) view contains the user name in format Username/Domain.

If you decide you want to display the lookup error (if any) in the field, you should use this code instead: errorMessage := "<your message here>";
result := @DbLookup(""; server:"names.nsf"; "($Users)"; EmpName; 8);
@If(@IsError(result); errorMessage; result)