I am trying to send an error message to a user if they key in a change order number that is not valid (looking at another Domino db). I have the following formula sitting in InputValidation:
value:= @DbLookup(“”;“Notes1”:“Engreg.nsf”;“All Documents”;ECR;5);
@If(responsibility=“Engineering” & value = “”;“Eng Change Request # Required if Engineering is Dept Responsible”;@Success);
No matter what I key into the change # field, it always gives me the following error:
“field didn’t pass validation formula”.
What am I doing wrong? (be gentle… I’m trying to wing my way through this Domino stuff. Haven’t had any training yet)
Subject: field didn’t pass validation formula
-
If a @DbLookup doesn’t find anything, it returns an error. You should explicitly check for this with @IsError.
-
If you want a validation formula to show a specific message to the user, you should user @Failure.
-
@DbLookup caches results so, if a new ECR number has recently been added, the @DbLookup might not find it. You can change this behaviour by adding “NoCache” to the @DbLookup.
Note this may cause a slight performance reduction. If new numbers are rare, it might be better to leave out the “NoCache”.
This formula should work:
value:= @DbLookup(“”:“NoCache”; “Notes1”:“Engreg.nsf”;“All Documents”;ECR;5);
@If(responsibility=“Engineering” & (value = “” | @IsError(value)); @Failure(“Eng Change Request # Required if Engineering is Dept Responsible”); @Success);
Subject: RE: field didn’t pass validation formula
Thanks for the help! I changed the formula to read exactly as you showed me, and the “Field didn’t pass validation” error message went away. So I continued testing… keyed in a bad change order number… gave me the error message. However, when I key in a valid change order number I got the error message as well. I thought I’d give the @prompt thing another shot, so I commented out the @if formula and replaced it with@If(@IsError(value); @Prompt([Ok];“Test”;value);@Success);
hoping that it would show me why it wasn’t finding the change order numbers. When I ran it, it gave me the “Field didn’t pass validation” error message again. How can I tell what I’m getting back from dblookup? Did I not do @prompt correctly?
Thanks again, I really appreciate the help!
Subject: RE: field didn’t pass validation formula
I haven’t tried, but it’s possible that @Prompt just doesn’t work in validation formulas. You could try using @StatusBar instead.
I’d put a @StatusBar call as a new line in the middle of the existing formula, so that the other lines aren’t changed.
I’m also using @Text inside the @StatusBar call, because error values are not normal text.
The complete formula would then look like this:
value:= @DbLookup(“”:“NoCache”; “Notes1”:“Engreg.nsf”;“All Documents”;ECR;5);
@If(@IsError(value); @StatusBar(@Text(value)); “”);
@If(responsibility=“Engineering” & (value = “” | @IsError(value)); @Failure(“Eng Change Request # Required if Engineering is Dept Responsible”); @Success);
Subject: RE: field didn’t pass validation formula
I tried the revised formula…still got the error message and nothing showed up in the status bar. I messed around with it a bit more and revised the code as follows:
value:= @Trim(@DbLookup(“”:“NoCache”; “Notes1”:“Engreg.nsf”;“All Documents”;ECR;5));
@If(@IsError(value);@Prompt([Ok];“Test”;@Text(value));“”);
I did this in hopes that I would be able to get a prompt to display with the text of the error. I ran it and I did get a prompt but it didn’t display anything? It acted as if it returned an empty string. This confuses me, as I thought if there was an error, shouldn’t it return what the error is? If the lookup doesn’t find the value, doesn’t it just return a null string and no error?