DBLookup not working

Posted on 2007-07-26
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
    LVL 22

    Accepted Solution

    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

    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

    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?
    LVL 46

    Assisted Solution

    by:Sjef Bosman
    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

    @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

    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

    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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
    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.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now