Solved

Solution for @DBColumn 64k limit

Posted on 2004-09-08
32
2,451 Views
Last Modified: 2013-12-18
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:
@Unique(@DbColumn("":"NoCache";"":"ESSAP01_Archive.nsf";"TCode";1))

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
0
Comment
Question by:lianaizhouzhiruo
  • 11
  • 7
  • 6
  • +4
32 Comments
 
LVL 19

Expert Comment

by:madheeswar
ID: 12004585
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.
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 12004599
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 12004614
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12004737
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.
0
 
LVL 13

Expert Comment

by:CRAK
ID: 12005155
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.
0
 
LVL 14

Expert Comment

by:p_partha
ID: 12007479
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)

Partha

0
 
LVL 31

Expert Comment

by:qwaletee
ID: 12008337
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 site:experts-echange.com (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!
0
 
LVL 13

Expert Comment

by:CRAK
ID: 12010281
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!
0
 

Author Comment

by:lianaizhouzhiruo
ID: 12013516
RE:madheeswar
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.

RE:sjef_bosman
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

RE:CRAK
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

RE:p_partha
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)

RE:qwaletee
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
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12013626
lianaizhouzhiruo,

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

Sjef
0
 

Author Comment

by:lianaizhouzhiruo
ID: 12014389
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!
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 12014405
Use:
FIELD tbTCode:=tbTCode;
@If(tbTCode != "";@DbLookup("";"";"TCode";tbTCode;"Description");"")
0
 

Author Comment

by:lianaizhouzhiruo
ID: 12014549
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.
0
 
LVL 13

Accepted Solution

by:
CRAK earned 55 total points
ID: 12031109
Here's the pop-up approach you asked for:

1st:
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"

2nd:
create a form "KwdPop"
write 2 lines of HTML on it:
   [<select name="PopSelect" size="20">
   </select>]
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:
   fld=document.forms[0].PopSelect;
   opener.document.forms[0].anyField.value = fld.options[fld.selectedIndex].text;
   opener.focus();
   window.close();

3rd:
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 = window.open('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!

Enjoy!
0
 

Author Comment

by:lianaizhouzhiruo
ID: 12051110
Hi CRAK,

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.
0
 
LVL 13

Expert Comment

by:CRAK
ID: 12053215
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....
0
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.

 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12053333
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.
0
 
LVL 13

Expert Comment

by:CRAK
ID: 12059235
> 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!"
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12061987
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!
0
 
LVL 13

Expert Comment

by:CRAK
ID: 12062520
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???
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12062775
Picodon! Small goat's cheese over here. Yummie!
0
 
LVL 13

Expert Comment

by:CRAK
ID: 12062814
Thanks, but I'll pass.....
("wat een boer niet kent....")
0
 
LVL 3

Expert Comment

by:Andrea Ercolino
ID: 12078206
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...
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 12078328
See also http:Q_21131995 for a different approach.
0
 
LVL 3

Expert Comment

by:Andrea Ercolino
ID: 12078386
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)

0
 

Author Comment

by:lianaizhouzhiruo
ID: 12081339
Hi!

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! :)

PS: RAPUTA
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! ;)
0
 
LVL 13

Expert Comment

by:CRAK
ID: 12081953
RAPUTA,

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


lianaizhouzhiruo,

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...
0
 

Author Comment

by:lianaizhouzhiruo
ID: 12082535
Hi CRAK,

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.
0
 
LVL 13

Expert Comment

by:CRAK
ID: 12082708
Get Description-button:

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


Confirm-button's hide formula:

Desc=""

or:

@If(
   Desc="";
   @True;
   @False)
0
 
LVL 13

Expert Comment

by:CRAK
ID: 12084452
Regarding the 1000 doc's:
Please see http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_20114865.html

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 http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_20547319.html or use the standard option.

0
 

Author Comment

by:lianaizhouzhiruo
ID: 12098263
Hi CRAK,

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



DominoNotes Beginner
0
 
LVL 13

Expert Comment

by:CRAK
ID: 12100009
Excellent!
;-))
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

18 Experts available now in Live!

Get 1:1 Help Now