How to handle null value in a @DbLookUp 1

[mhudson]

A user has asked me to add a lookup value to an existing field on a form. The new lookup looks for values (amount of scrap)on a given date to be added to the scrap on a different form (different sources of scrap) to get a total scrap amount. The formula looks like this:
@If(date="";"";@Sum(@DbLookup("":"NoCache";"NOTES/My Company":"Producti.nsf";"Shop Score Calculation";date;"scrap")))+@DbLookup("":"NoCache";"NOTES/My Company":"Producti.nsf";"Insert/Debur Scrap";date;"insertqty")

If there was any Insert/Debur scrap on a given day, everything works fine when the Shop Score Calculation form is F9'ed to update on that same date. However, if there was no Insert/Debur Scrap entered for that date, the Shop Score form update errors out.
How do I account for the possibility of a null value to prevent the error? Thanks.

 

[pmonett]

Assign the result of the @dbLookup to a temp variable and control for errors there.
For example, instead of

@Sum(@DbLookup(...))

you could try

lkpData := @if(
   @iserror(
       @DbLookup(...)
   );
   0;
   @DbLookup(...)
);
sumval := @sum(lkpData);

Or something along that line.

Pascal.

 

[mhudson]

Thanks! I will give it a shot and see how it works. I am not all that knowledgable on Notes programming, so I may have to ask you to really dumb it down so I can understand it more easily. Thanks again.

 

[mhudson]

Hey pmonett,
I tried using your code as the basis for the modifications, but no matter what, I never could get it to work (kept fussing about the syntax). Could you please give me a verbatim set of code to use (based on the code I posted)? If you can, I would mbe most grateful.

 

[pmonett]

Sure, try this :

WkLkp:=@DbLookup("":"NoCache";"NOTES/My Company":"Producti.nsf";"Shop Score Calculation";date;"scrap");
WkSum:=@if(@isError(WkLkp);0;WkLkp);
@If(date="";"";@Sum(WkSum)+@DbLookup("":"NoCache";"NOTES/My Company":"Producti.nsf";"Insert/Debur Scrap";date;"insertqty"))