Dblookup other column

A few options, none very efficient.

Use @DBcolumn to get the data  and get to the document you need.

2) Do it in Lotus script, perhaps using FTsearch or another serach .

In any case these wil be slow f you do not have access to a sorted view on the key you are looking for or a full text index.

I hope this helps !

Column 4 is sorted, just not the first sorted column. And , I don't know script and am not familiar with FTsearch in script.

To solve difficult problems you need to expand your tool set.
Script will allow you to do lots of things not possible using using functions.

Let's wait awhile for the others to throw in their comments.

try this:

searchList:=@DbColumn("":"NoCache";"<db>";"<view>";4);
resultList:=@DbColumn("":"NoCache";"<db>";"<view>";6);
searchValue:="xxx";
@Right("*" + searchList + "*" + resultList;"*" + searchValue + "*")

that's all.

That code will fail when there are lots of documents, several thousands or so... Once the strings grow over 32K or 64K, it's all over.

I second SysExpert's idea to use an FTSearch, which only works when the db to be searched is full-text indexed...

Are you sure that there is no view you can use? Did you check all hidden views in that db? With Ctrl+Shift+ click on View/Goto ??

I hate to bring this to you, but the 64k limit on Dblookup and dbcolumn has been removed since R6.5....

So the dbcolumn won't fail, it won't fail on the size of the string as well, because it is as string list, and each of the elements in the list can be up to 32k.

Hmm... I was about to jump for joy.. then I checked the Designer Help for 6.5.5

@DbColumn can return no more than 64K bytes of data. Use the following equations to determine how much of your data can be returned using @DbColumn.

Reference: http://www-12.lotus.com/ldd/doc/domino_notes/6.5/help65_designer.nsf/Main?OpenFrameSet

Same is true for the R7 designer Help: @DbColumn can return no more than 64K bytes of data. Use the following equations to determine how much of your data can be returned using @DbColumn.


Have you tried returning more than 64K?

Also, just wondering, Schmad01, is this something that is for production or personal use? If it's for production, then the FT option is your best bet, but if you have permission to create a private view and it's for personal use, then you may be able to do that.   But again, your @dbcolumn formula will be limited.

I think you would have to filter the view to bring back the column 4 values first, and then do a collection on the column 6 filtered results.

Just thinking out loud..

I checked the SPR and technotes on the Lotus site, and it looks like the help files aren't updated...

The limit was removed as off R6.0 (however in 7.0.1 a regression occured):

see this technote:
http://www-10.lotus.com/ldd/r5fixlist.nsf/PublicTech/1DC32ADB85DA571C852570EF00111203?OpenDocument

Well, that IS some news to me. It would seem to work in the formula you gave. But I wonder, there must be some limit somewhere? Or is there no limit whatsoever as long as the resulting output for the field is < 64K ??

yes.... you are right, no limit


I remember when I first came to know about this feature.. I was first sceptical, but now I use this nice option often.

You still have a problem in dialog lists however, the total for the options can not be longer than 64k.

This is a particular nice feature to use on documents you have no read access on. You can create a categorised view, do a dbcolumn on the view, and get the information out, without people having access to the documents themself

Beg pardon?? So you say that the ACL and Reader fields are not applied to a view when a @DbColumn is used? AFAIK unreadable documents don't even show in a view, but apparently there are some gaps in the Notes security.

Just another Notes "feature"??

Categories are always displayed (unless you mark the Do no show empty categories), just try it.

I am attempting to have a view created so I may use column 1. Will update.

Hmm.. Joep8020, it seems your link indicates that the 64K limit is NOT lifted, just whether or not the @isError pops up or can be trapped.  Again, have you tried this?  It seems that the limit is NOT lifted.   However, to be sure, I will personally run some tests tonight. :)

Ok, tested.  Here are the results.  R6.5

Applied the first formula:
searchList:=@DbColumn("":"NoCache";@dbName;"($ALL)";12);

To a mail file having 3700 entries to return.  I first did this in a computed text, multivalue field.  
I received this error messagebox before the form opened:
The result of a computation cannot exceed 65535 bytes

Oops, hit submit by accident.

Ok, tested.  Here are the results.  R6.5

Applied the first formula:
searchList:=@DbColumn("":"NoCache";@dbName;"($ALL)";12);

To a mail file having 3700 entries to return.  I first did this in a computed text, multivalue field.  
I received this error messagebox before the form opened:
     >>>The result of a computation cannot exceed 65535 bytes<<<

I then added a similar formula to a dialog list, combo box, and list box to return a list of selectable items.
  Here is the formula used:

       resultList:=@Text(@DbColumn("Notes":"NoCache";@DbName;"($ALL)";12));
       @If(@IsError(resultList);"ERROR";ResultList)

Here is the first and only selectable entry returned (suggesting that the @isError trap failed):

     >> The specified database lookup generated more than 65,000 bytes of results, which is too large for Notes to handle in this context.<<


This tells me that the 64K limitation has NOT been lifted.  Shall I try on R7+ ?  :))

Marilyn, it's the final value after all computations that gets stored in a field. So it seems, the intermediary results may be (much?) larger than 64K. I haven't tried yet, awaiting your efforts. ;-))

Please post your full code...

Sorry, Schmad01, for messing up your question a little, but I hope you think it's not off-topic. By the way, did you try Joep's code?

Yes, thanks.

Interesting that the accepted answer only brings back the first 10 entries in the view... schmad01, you are aware of this I hope?