Learn how to a build a cloud-first strategyRegister Now


DBLookup not working

Posted on 2007-07-26
Medium Priority
Last Modified: 2013-12-18
Have a DBLookup I am working on whick references an Agent ID "dai" and then pulls in the username for that agent. The list of agents is in the 2700 range, when I refresh the doc, I only get a name in 70 cases, where there should be well over 2000 that will match. Is there an issue with the number of agent records? What would be a fix for this issue?

@IfError(@DbLookup( "" : "NoCache" ; ""  ; "usernames" ; dai ; 2); "Name Not Available")
Question by:jforget1
  • 3
  • 2
  • 2
  • +1
LVL 22

Accepted Solution

Bill-Hanson earned 668 total points
ID: 19577901
I don't see anything syntactically wrong with the statement above, but...

There is a 64k limit on the data returned by @DbLookup.  If you take the @IfError call out, you will see what the actual error is (if any).

Also, I know that there are some bugs with @IfError.  That's why Lotus depricated the function.  I'm not sure if it is affecting your code or not, but you should try using @If and @IsError instead.

Author Comment

ID: 19577932
Using the @IfErro, just to handle if there is no value that it can pull from the @DBLookup. THe actual data it is pulling is only a name, does the 64K limit have anything to do with the fact that there are over 2000 records it has to scan to see if that Agent ID exists.
LVL 22

Expert Comment

ID: 19578089
Nope, the limit only applies to the amout of data returned, so you should be ok.  I'm not sure what the problem is unless it is @IfError itself.  Have you tried using @If and @IsError instead?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 664 total points
ID: 19578524
What I don't understand is the line with "when I refresh the doc, I only get a name in 70 cases, where there should be well over 2000 that will match". Match?? If you want a list of all agents, why don't you use @DbColumn? Or do you want one value from one (or more) documents?

Obvious questions:
- the view is sorted?
- there is content in the 2nd column?
- did you already phase out @IfError yourself? (if not, just do it... ;-)
LVL 31

Assisted Solution

qwaletee earned 668 total points
ID: 19580020
@IfError only has problems with bad math values.  I've gotten that on good faith from some Iris developers.  Somebody decided not to bother fixing that, but it will continue to work properly for things like @DbLookup. You could alternatively use the [FailSilent] option, but that would return a blank string instead of Name Not Available.

Most likely, your problems are UNRELATED to the @IfError, but it doesn't hurt to try to eliminate that as a possible source of problems.

If you blew the 64k limit, you should be getting an error value about blowing the limit.  You wouldn't get a partial rersult.

Is your view categorized or sorted?  Sometimes I find categories helpful as a diagnostic, as it shows me "bad data," e.g., two values look the same, but some turn outto have a space at the end.  If they categorize together, then their sort key will definitely be the same.

Try the following code as a sanity check.

Const dai = "some agent key code"
Const column = 2
Dim s as new notesSession
Dim db as notesDatabase, view as notesView, result as notesViewEntryCollection, row as notesViewEntry
Dim temp as variant, values as string, count as integer
Set db = s.currentDatabase
Set view = db.getView("usernames")
Set result = view.getAllEntriesByKey(dai,true)
values = result.count & " entries found"
print values
Set row = result.getFirstEntry
Do until row is nothing
  temp = row.columnValues(column - 1) '@DbLookup uses offset 1, columnValues uses offset 0
  if isArray(temp) then
    Forall tempElement in temp
      count = count + 1
      print count "of" result.count tempElement
      values = values & "," & tempElement
    End Forall
    count = count + 1
    print count "of" result.count temp
    values = values & "," & temp
  End If
  Set row = result.getNextEntry(row)
msgbox values,,len(values)

Author Comment

ID: 19581728
Reason for the @IFError is if I do not have it and there is not a match to an agent ID I get "ERROR: Server Error: Entry Not found in index." Not the cleanest this to see on a record.

The first column of the view is ascending sorting, not categorized. and the second column of data is the username I am trying to match. I have taken a random ID that I know has a corresponding name that it matches, I look at the record which has that same Agent ID which should pull a name and it does not pull the name from the view.

Author Comment

ID: 19581976
I have used the FAILSILENT instead of the @IFError, but it still does not pring back a name for an Agent ID # which should have a corresponding name. Strange that some work, but the majority do not bring back a result.

Just noticed something, the ones where it worked the IDs have a leading zero and are data type text, the other are all data type number, I will play around with adding @Text to the code.
LVL 31

Expert Comment

ID: 19582745
Yup, that's a good way to get bad results. DOmino sorts numbers and text separately, and when you lookup, your search key had better match the type of the key value being searched against.

You can probably solve this by simply putting @Text into the view column... except for cases where the stored value is a number, but SHOULD be text and SHOULD have a leading zero.  For those cases, you'll have to fix the data.

A better solution would be to go back to the form set the field as text, and make sure any import routines are also importing as text.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Loops Section Overview
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question