@DBLookup exceeds 65.000K problem

HCL Domino Domino Forum
1

I have a field on a form that asked for a loan number. Another field does a validation by using @Dblook to check a view of the loan numbers to see if it has already been entered and then throws a validation error if it finds a duplication.

Every loan number is unique and I have reached the 65000k limit. The list of numbers will continously be growing. Does anyone know of a workaround?

2

Subject: @DBLookup exceeds 65.000K problem

Post your code, perhaps we can figure out why you are getting so much data back when you should be looking to get none back.

3

Subject: RE: @DBLookup exceeds 65.000K problem

It’s either because he’s really using @DbColumn, or using @DbLookup in a bizarre way, e.g. looking up by form name in a view that contains all the documents sorted by form, and then searching the result list for a matching value.

Create a view that’s sorted first by the unique key you want to locate, and contains only documents of the type that you’re concerned about a match with. Use @DbLookup in that view to search for the key you want to check for uniqueness.

4

Subject: RE: @DBLookup exceeds 65.000K problem

This is in a field validation event:

oldids := @DbColumn(“”:“NoCache”; “”:“”; “Customer Number”; 1);

@If (Untitled3=“New document”&@IsMember(bank_no+cus_number+loan_num;oldids); @Failure(“The loan number you are entering already exists on the database. You must enter a different loan number in order to save this document.”); @Success)

5

Subject: RE: @DBLookup exceeds 65.000K problem

Create a new view using the customer+loan number as the first column (sorted and ‘Show multiple values as separate entries’)

Then use @dblookup instead of @dbcolumn, customer+loan number as the key. If the @dblookup fails then the loan number is not used.

6

Subject: RE: @DBLookup exceeds 65.000K problem

Instead of pulling back all the loan numbers and then checking if the new loan number is a member of it, try doing a lookup of the new loan number against the view to see if it exists. That way you don’t run into this 65K problem. If you get an error, that (should) mean that the loan number isn’t found and is ok to use.

Maybe something like this:

view := “Customer Number”;

key := bank_no + cus_number + loan_num;

col := 1;

lookup := @DbLookup(“”:“NoCache”; “”; view; key; col);

@If (Untitled3=“New document”& @IsError(lookup); @Success; @Failure(“The loan number you are entering already exists on the database. You must enter a different loan number in order to save this document.”))

Hope that helps.

7

Subject: RE: @DBLookup exceeds 65.000K problem

Thanks for all the responses. I tried your way Wing and i get the validation error everytime i try to save a doc where the key fields are not found in the view. It’s kind of working the complete opposite.

8

Subject: RE: @DBLookup exceeds 65.000K problem

That’s me! Always getting things backwards! :wink:

Try rearranging your If statement to

@If (Untitled3=“New document”& !@IsError(lookup); @Failure(“The loan number you are entering already exists on the database. You must enter a different loan number in order to save this document.”); @Success)

The way I had it coded, the failure would also occur if Untitled3 was anything other than “New document”. So now, only if it’s a new document and there is NO error on the lookup, then the failure will occur.

hope that helps.

9

Subject: RE: @DBLookup exceeds 65.000K problem

You da man!! It appears to be working. Thanks for your help.