Solution for @DBColumn 64k limit

Hi Domino Notes experts,

I have a problem here that needs help badly with my 1st web app.

I have a main form with an image hotspot that, when clicked, will open another browser window. This new window is a form that contains a combobox with Use formula for choices:

This combobox is supposed to take all values of the 1st column of the TCode view and populate the combobox. When user selects an item from the combobox, the selected item should appear in one of the fields in the main form. It has been working fine before, until I recently imported a lotus worksheet into the view which resulted in 50 over thousand documents being created in the view. The form no longer works and I believe it is due to the 64k limit of the @DBColumn. (@Picklist not an option as its a web app.)

What I would like to be enlightened on is,  is there a way to solve this problem? I need to get all the values for the 1st column in the view and populate the combobox with them. (Theres over 53k values in the view right now and its still going to be increased by loads in the future)I've found some suggested solutions to this on the web but I need more concrete help(sample codes/instructions). Would using javascript to first, get all the values from the view then populate the combobox work? If yes, could anyone provide example codes? If no, is there any other way to solve this?

Any kind of help would be greatly appreciated.

DominoNotes Beginner
Who is Participating?
CRAKConnect With a Mentor Commented:
Here's the pop-up approach you asked for:

create a view
1 column (sorted): "<option>" + kwd
(where kwd is the field that holds the fields that you're currently collecting)
set view property "treat view contents as HTML"

create a form "KwdPop"
write 2 lines of HTML on it:
   [<select name="PopSelect" size="20">
embed the view (step 1) in between the two lines.
embedded view properties: select desplay "using HTML" and uncheck "show header".
On the bottom of the form I put down a button. I wrote "select" on it at gave it following code in the Web/Javscript onClick event:
   opener.document.forms[0].anyField.value = fld.options[fld.selectedIndex].text;

I created a dummy input form with a field "anyField" (text, referred to from the buttons JS-code!).
Somewhere near the field I created an action hotspot that gave following code (web, javascript):
   nwWin ='KwdPop?ReadForm', '', '')
The 3rd parameter can be given dozens of parameters to control the pop-up's behavuour (show/hide the menu, scrollbars, define its size, position and alow/deny resizing).

It could use a bit more attention, like keeping focus on the popup untill the button is pressed but basically this is all you need!

Use a view with only one column and add <option> tag in the front and back of the column value. Embed the view in the required form and add <select> tag in the starting and end it after the view. Now it becomes a combo box.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Sjef BosmanGroupware ConsultantCommented:
I pity the poor user who has to search through 64+ Kb of selectable options. Isn't it possible to make some sort of categorization in your choices, to make a two-step approach? If only one option is to be selected?

If you need all the options, you can write a WebQueryOpen agent that does the looking up for you, creates a select-field in html, if necessary with some JavScript to activate the form. Embedding could be a lot faster, but it's not my favourite.
If you're collecting keywords, it might help to collect from a categorized view; the categorized column to be exact.
You're now using @Unqiue to remove duplicate entries. Categorisation does the same thing. Difference is that you'd be removing duplicates BEFORE the @DbColumn!

Alternetively, you could simulate a @Picklist by showing a view in a pop-up window. Each entry could be presented as checkbox or radio button. With a bit of additional HTML / JS you could feed the selected value(s) bach into the parent window.
categorize the first column and when u do a dbcolumn u get only the categorized headings and do a unique of it, u shd be set . If it really crosses 64k, then normally we do a split with teh characters.

For ex: Most of the time in our applications , we end up getting 64k for displaying the names. So we do a split of that using each character with the first column @left(<yourfieldname>;1)


I replace all Madheeswar comments with a suggestion to search before your post.  Don't have access to Experts Exchange's own search?  Google with (or download the google toolbar and use the Search This Site option).

And BTW, CRAK is the astute one here.  Categorize instead of sorting, and you are probably done!
Thanks qwaletee! I had to get a dictionary to tell what you were saying, but thanks!

Do note that, as your db grows, in time you might get stuck on the same barrier again!
lianaizhouzhiruoAuthor Commented:
I tried what you said with the <select> and <option> tag but it doesnt seem to work. The combobox shows up with the <select> tag but its empty. Am I missing any steps?
Regarding your link to qwaletee's post, I tried that too but the onClick doesnt seem to work. Again, I might be missing something or doing something wrong.

Hi, there is no 2-step approach. All values in the column are unique and needed. Could you show me an example code of a WebQueryOpen Agent that will do what you said? Thanks

Categorizing the column doesnt work as all the values are unique. Could you show me an example of simulating the picklist in a popup window with each entry as a radio button or combobox? Thanks

Hi, @Left etc doesnt work I keep getting "The data type of two matching results are incompatible". No idea whats wrong. The code I used in the selection formula is this:
"Select a TCODE": @Left(@Text(@DbColumn("":"NoCache";"":"ESSAP01_Archive.nsf";"TCode";1));100)

Thanks for that tip on using google but I still couldnt find any solution to my problem( either they dont work or I dont understand enough or how to implement the solution)

Thanks for the replies everyone.

DominoNotes Beginner
Sjef BosmanGroupware ConsultantCommented:

> Hi, there is no 2-step approach. All values in the column are unique
> and needed. Could you show me an example code of a WebQueryOpen Agent
> that will do what you said? Thanks

So you say  that there are thousands of unique values the user has to choose from? What are they: codes, names, meaningful or not? Is there anybody who can remember all those codes? Cannot you limit the list per user to a sensible amount?

lianaizhouzhiruoAuthor Commented:
Ok I've decided I can do this another way since I just found out that the users should know which codes they want.

I'll have a main form with an image hotspot that will popup a new window and form when clicked.On this form, I'll have 2 textbox fields(tbCode and tbDesc). Users will enter the code they want in the tbCode and when they press enter, the description will appear in tbDesc. If this is the code the user wants, he/she will click a hotspot button(hbOK) and this will send the tbCode and tbDesc values to the main form.

Right now, i'm having a problem with the textboxes. tbCode has a default value of tbCode. tbDesc has a value of tbDesc;
@If(tbTCode != "";@DbLookup("":"NoCache";"":"ESSAP01_Archive.nsf";"TCode";tbTCode;"Description");""). When I type in the tbCode and press enter, the url changes to http://servername/databasename.nsf/formname?OpenForm&Seq=1   with a page that says HTTP404 - File not Found error. May I know what is the correct way to code what I have described in my second paragraph? Thanks!
FIELD tbTCode:=tbTCode;
@If(tbTCode != "";@DbLookup("";"";"TCode";tbTCode;"Description");"")
lianaizhouzhiruoAuthor Commented:
RE: madheeswar

Hi, I put your code in the value of tbDesc which is a computed for display field. Now, I get to a page that says "Form Processed" instead.
By the way, a correction, the 2 textfields are tbTCode and tbDesc.
lianaizhouzhiruoAuthor Commented:

Firstly thanks alot for the detailed instructions. It helped me alot when trying to implement your suggestion correctly. However, it seems that the combobox items are limited to 1000. I've tried changing the Lines to display property but is still limited to 1000. I've also tried categorizing the column and the limit becomes 500.

I have 53k+ options that needs to be shown in the combobox. Is there any way to overcome this? Or should I find other ways? I dont really need to have the options in a combobox etc, but I'd need to have the user be able to see all the options and select one.

Either that or, I could have a textbox where user enters the Code they want then ill lookup the code in the view and send the columns values back to the opener form. This way, I wont need to have the users be able to see all the 53over thousand codes. However, I am having trouble trying to implement that.

Once again CRAK, thanks for the wonderfully detailed instructions. Most appreciated.
Limited to 1000? Never heard of it. Reason of that could be that no one ever told me....
Isn't the view limited to show only 1000 docs? Still... the max you could set it to is 9999. ;-(

You could break it up into smaller pieces; e.g. categorisation by 1st letter (using single category view). Put the alphabet over it so people can jump to the keywords starting with e.g, "M". Still.... 53k+ / 26 = 2k+ (need to find out about the 1000-limit first!)

Let me know if you can bypass the current limit (embedded views properties: 1st tab, bottom: don't use the default!). I've got the alphabet thing ready somewhere. Perhaps even already posted somewhere on EE....
Sjef BosmanGroupware ConsultantCommented:
What a waste of time... This costs ages to implement, and at $500 a day (modest) it's not only a waste of time but also a waste of money. I'd try a different approach if I were you.

Why don't you show them the whole view? If you want with only one column, the key, and show all values in the view, without a limit. Or categorized, by first symbol. Then open the document, and have a button available to create a new document.
> Why don't you show them the whole view?
I did.... but then lianaizhouzhiruo (glad they invented copy/paste!) answered that only 1000 entries were shown. So I pointed out where the no. of docs shown in a view can be overrules in an embedded view.
Unless you refer to a plain (links only) view. It certainly is an option, but this way a user can safely alter his choise within the popup.

> Or categorized, by first symbol....
That was my next suggestion: the alphabet approach.
> Ages to implement, and at $500 a day (modest) it's not only a waste of time but also a waste of money.
Not if it's easy to implement, using a few lines of code that we (I) already have at hand someplace....
Can't decide on the waste of time or money. I already had 2nd thoughts when I red about 53k values in the original question. I was seriously hoping for loads of duplicate entries.... for the user's sake! As a programmer it's challenges like this I love most in a job.
At my current assignment my collegues tend to say "if they ask for cheese, cheese is what they'll get!"
Sjef BosmanGroupware ConsultantCommented:
I think (hope) you missed my point, but maybe I missed yours... I'm not suggesting some keyword list, instead I just want to show the whole view and nothing but the view. No selection forms, no other stuff, just the flippin' view. When the user clicks a document in the view, which is functionally completely equivalent to selecting a keyword from a large list, the document opens and in the document there can be a button to create whatever is necessary. So justt the standard old-fashioned approach.

Anyway, if there are 52000 entries and they all start with a letter, there will still be 2000(!) entries categorized under some letters. Ghastly.

Cheese: I used to say "If they want a square wheel, they get a square wheel". By the way, j'aime le Picodon!
We must be thinking in the same direction!
I'll quote you on the square wheels; it's more clear than the chees thing - thanks, but my knowledge of the french language isn't exactly what it could have been.... you love what???
Sjef BosmanGroupware ConsultantCommented:
Picodon! Small goat's cheese over here. Yummie!
Thanks, but I'll pass.....
("wat een boer niet kent....")
Andrea ErcolinoCommented:
I'm really curious about the structure of the code. Could you post a description? A general solution for something that goes beyong the limits of Notes is generally difficult. So maybe a custom solution is easier.

My experience says that if you cannot do something through a @formula, you need to do it with LotusScript, maybe changing a little the architecture. And this is particularly true for web apps.

In my opinion, you should put a LotusScript WebQueryOpen agent in the form you want to use for showing the choices. And build a new HTML page in a string, with all those options, and all the logic you need on the page. Then end the agent with the following two lines:

  Print "Content-Type: text/html"
  Print choices

Yow know, in LotusScript there is no limit to the number of documents you can access in a view...
Sjef BosmanGroupware ConsultantCommented:
See also http:Q_21131995 for a different approach.
Andrea ErcolinoCommented:
On the other hand, I think you should test how well behaves a listbox with 50000 options in a browser!

If it's ok, then go on with the select html tag, else try a different approach, perhaps a paged one, with encyclopedic style links to the pages (not page 1, 2, 3, ... ; but code[1]-code[10000], code[10001]-code[20000], ... beeing code[n] the value of the code at the index n -- six pages should be acceptable for your huge requirement)

lianaizhouzhiruoAuthor Commented:

Thanks for all the suggestions! I've decided to just open a new window where users can input the code in a textfield(i just found out the users SHOULD know which codes they need/want but they need to check the description) then i do a @DBlookup on the description column with the key when they click the "Get description" button and display the description. When user confirm the code and description are correct, they click the confirm button and I send them back to the opener form!

This would have been much less complicated if my supervisor had told me more about the users earlier. Anyway, so I've managed to do all that but I have a little problem here, this "Confirm" button I have here, should not be visible unless a valid code and description are in the code and description textfield and both fields are not empty. I have this code in the COnfirm button's Hide paragraph if formula is true:
@If(desc != "")
But with this code, the button shows up when I enter an invalid code into the textfield and press Get Description. I tried:
Code != "";
@DbLookup("":"NoCache"; "":"code.nsf"; "CodeView"; @Text(Code); 1) = Code
but it doesnt work.

Sorry for not having being clearer about what I want to implement but Im really new to notes and have no training or education on it(currently a student on industrial attachment), neither have I been given enough time to self study whatsoever.

I am very appreciative of the efforts all the other experts who have taken time to try and help me with this problem(especially bosman and madheeswar) but I'm gonna have to give the points to CRAK as he provided stepbystep and detailed instructions on how to implement stuffs(even though i cant use it for my problem but it helped me learn something new and could prove useful next time), something a beginner like me was very much looking forward to and needed. I'd like to thank all who have replied and suggested possible solutions. Thanks! :)

Hi, I might be needing help on something similar to that webquery agent you talked about and will be posting a new question on it. Hope you and the other experts would drop by and help when you see it! ;)

You wrote: "I'm really curious about the structure of the code".
Was that a reference to the alphabet approach or something else?


Thanks for the points, but if your problem is still as big as it was before, don't write us off yet.
Are you still stuck on the 1000 entries? I'll see if I can reproduce that...
lianaizhouzhiruoAuthor Commented:

Yes, the combobox is still stuck with 1000 entries even after changing the lines to display property but it does not matter now as I have got around my original 64k @DBColumn problem by another way(described in my previous post).

The only problem left is a Hide formula for a button(described in the 2nd paragraph of my previous post). Let me make it clearer.

I have 2 textfields named Code and Desc.
Code is editable with a default formula value of Code.
Desc is computed for display with a default formula value of Desc.
I have a button named Get Description with the formula:
FIELD Desc := @If(Code != "";@DbLookup("":"NoCache"; "":"db.nsf"; "TheCodes"; @Text(Code); 2); "")
in click event. When users enter a code in the Code textfield and press the Get Description button, the button will lookup the view for the description of the code and place it in Desc textfield. All this works fine.
I have a 2nd button named Confirm which will send the values of the Code and Desc textfield to the opener form. I want this button to appear only when a valid code(valid as in it can be found in the specified view) is entered in the Code field and the Desc field has the code's description. The formula I have in the Hide if True property doesnt work: @IF(Desc != "")
The button will appear when i type something in the Code field and then press the Get Description button. Even if i just enter a space in the Code field and press the Get Description button, the Confirm button will appear, which is not what I wanted. I want it to only appear when the fields have a valid Code and a valid description.
Get Description-button:

tmpDesc := @If(
   Code != "";
   @DbLookup("":"NoCache"; "":"db.nsf"; "TheCodes"; @Text(Code); 2);
Field Desc := @If(

Confirm-button's hide formula:



Regarding the 1000 doc's:
Please see

I totally forgot about it! I did raise is once, but never work with lists this long.

Better return to Sjef's approach: a view with modified links, with or without links to keywords starting with "A", "B" etc. but in all cases: the possibility to jump to a/the next page.
See or use the standard option.

lianaizhouzhiruoAuthor Commented:

Thanks alot! The buttons work correctly now~! Your After-Points-Service is excellent! Hehe. Cheers and have a good day!

DominoNotes Beginner
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.