Solved

Dblookup other column

Posted on 2006-11-10
21
849 Views
Last Modified: 2013-12-18
I want to do a dblookup from a database that I do not have designer access so I cannot create the lookup view that I want.  So, I need to do a dblookup or something similiar where I can search column 4 , then bring back the values from column 6. I know that dblookup is for searching the "first" sorted column, but I need to search column 4.

Also, I need to bring back values where the document was created this year only.

Thanks!

0
Comment
Question by:schmad01
  • 6
  • 6
  • 4
  • +2
21 Comments
 
LVL 63

Expert Comment

by:SysExpert
ID: 17917886
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 !
0
 

Author Comment

by:schmad01
ID: 17918026
Column 4 is sorted, just not the first sorted column. And , I don't know script and am not familiar with FTsearch in script.
0
 
LVL 63

Expert Comment

by:SysExpert
ID: 17918055
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.




0
 
LVL 3

Expert Comment

by:Joep8020
ID: 17920723
try this:

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

that's all.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 17921146
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 ??
0
 
LVL 3

Expert Comment

by:Joep8020
ID: 17921182
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.
0
 
LVL 18

Expert Comment

by:marilyng
ID: 17921876
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?
0
 
LVL 18

Expert Comment

by:marilyng
ID: 17921891
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..
0
 
LVL 3

Expert Comment

by:Joep8020
ID: 17925139
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
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 17925421
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 ??
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:Joep8020
ID: 17925572
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
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 17926324
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"??
0
 
LVL 3

Expert Comment

by:Joep8020
ID: 17926681
Categories are always displayed (unless you mark the Do no show empty categories), just try it.
0
 

Author Comment

by:schmad01
ID: 17930437
I am attempting to have a view created so I may use column 1. Will update.
0
 
LVL 18

Expert Comment

by:marilyng
ID: 17942088
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. :)
0
 
LVL 18

Expert Comment

by:marilyng
ID: 17943092
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
0
 
LVL 18

Expert Comment

by:marilyng
ID: 17943111
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+ ?  :))


0
 
LVL 3

Accepted Solution

by:
Joep8020 earned 250 total points
ID: 17944971
just try this formula....

@If(@IsError(resultList);"ERROR";@Subset(ResultList;10))
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 17945341
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?
0
 

Author Comment

by:schmad01
ID: 18045964
Yes, thanks.
0
 
LVL 18

Expert Comment

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

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.

Join & Write a Comment

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

13 Experts available now in Live!

Get 1:1 Help Now