Unique Field Lookup Issue

HCL Domino Domino Forum
1

I’ve been scouring the Notes help documentation and forums on the web for several days now trying to determine if Notes will look up a field to determine if it is unique. I found this code snippet in the Notes 5 forum and made a few small changes to it for my application. However, while the first part of the code works and I get an error message if the field is empty, the second part where the value is compared against existing values to determine that it is indeed unique, I never get an error. Does anyone have any insight? Many thanks!

@If(RequestNo = “”;

@Failure(“You have to enter a Req Number”);

@IsError(

@DbLookup(“”:“NoCache”; “” : “” ; “All Reqs”; RequestNo; “RequestNo”));

@Success;

@IsNewDoc = 0;

@Success;

@Failure(“This Req Number Already Exists.”))

Here’s a link to the original posting: http://www-10.lotus.com/ldd/46dom.nsf/55c38d716d632d9b8525689b005ba1c0/dbe549cbd7b7921b85256a3700496de7?OpenDocument

Again, thanks for helping!

Karen

2

Subject: Unique Field Lookup Issue

The code seems incomplete.

When taking the code apart it is stating this:

condition 1 of the if - if the RequestNo = “” give a failure message

condition 2 of the if - if there is an error with the @DBlookup make the lookup Successful

condition 3 of the if - if the document has been saved (which is the same as !@IsNewDoc) make this condition successful

else condition give a failure message.

I don’t know what is needed to be done, but I figured if you understand the @if formula you will know how to correct it.

HTH – Cheers

3

Subject: Unique Field Lookup Issue

Try like this:

look := @DbLookup(“”:“NoCache”; “” : “” ; “All Reqs”; RequestNo; “RequestNo”);

@If(RequestNo = “”; @Failure(“You have to enter a Req Number”);

@IsError(look); @Success;

@elements(look) = 1 & !@IsNewDoc; @Success;

@Failure(“This Req Number Already Exists.”))

I think this’ll cover you.

hth

Dana

4

Subject: RE: Unique Field Lookup Issue

It’s nice that you’re thinking about checking uniqueness even if the document is not new, but this formula isn’t going to do it either. If the field is editable when the document is not new (and I can think of no other reason to check for uniqueness in that case if not!) there’s no way to know whether the one document you find with your lookup, is this document or some other. If it’s some other, you do want to display an error, but in either case the matching count is 1.

Furthermore, it’s a big waste of time to do the lookup at the beginning of the formula. First check @IsDocBeingSaved, for efficiency.

That said, there’s basically nothing wrong with the formula in the original post, per se. It doesn’t handle the case of subsequent saves, but I choose to believe Karen’s taking care of that by making the field non-editable on subsequent opens. I suspect the @DbLookup is always returning an error because the design of the view is wrong for doing that particular lookup. The view has to exist, for instance, and must sorted by the field whose value you’re searching for.

If you’re not sure what the problem is, you could put a button on your form that just does @StatusBar(@Text(@DbLookup(“”:“NoCache”; “”; “All Reqs”; RequestNo; “RequestNo”)))

BTW the recognized indication for “the current database” is “”, not “”:“”. The latter will work, but why use more complex expressions than you need?

5

Subject: Unique Field Lookup Issue - Thanks!

Wow, thanks for your input I got it working.