DBLookup not working

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")
Who is Participating?
Bill-HansonConnect With a Mentor Commented:
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.
jforget1Author Commented:
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.
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?
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Sjef BosmanConnect With a Mentor Groupware ConsultantCommented:
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... ;-)
qwaleteeConnect With a Mentor Commented:
@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)
jforget1Author Commented:
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.
jforget1Author Commented:
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.