I work with a large Notes application that uses @DBLookup formulas to generate selection lists in dialog box forms. These dialog boxes are used all over the place and currently, because of the growing amount of data in the application, users are getting "Field or view column exceeds 32 Kb" errors from the @DBLookup formulas. I have been working around these errors by cutting down the amount of information contained in the column that the @DBLookup formula returns, for example returning only the MATERIALID field (a unique 7 char field used by several classes of documents in my application) rather than a combination of the MATERIALID field and Description field. I would prefer not to do that, however. Is there a way of building a selection list in a dialog box form that can exceed the 32 Kb limit in an @DBLookup formula?
Actually, the limit is 64K, but on some systems, Lotus' Multi-Byte Character Set (LMBCS) causes the number of characters to be cut in half. Lucky you! Well, there's no direct way to remove this limitation, but there are a couple of work-arounds:
- Use an @Picklist, and show a view in your database. If you only need some data, make a special view for it. Whatever shows in the view will show in the picklist. Of special note, in R5 and higher, there's a new property "SingleCategory". If your view is categorized on the first column, and you set this property of the @Picklist equal to one of those category entries, then the Picklist box will ONLY show you the contents of that category! It won't even show you the category column - it just shows what's to the right of it. So you could have a view with all kinds of lookup information, sorted by "type", and categorized, then you can use the @Picklist to show the contents of the category you're interested in. A word on this, though: @Picklist has a 256K limit.
- More drastically, you could:
- Create a form with an embedded view (with your choices in it)
- Open the form with @DialogBox
- Have the RegionDoubleClick event of the view write to the INI file the UNID of the record selected in the view
- Upon closing the dialog box, read the INI file to get the UNID of the selected document
- then go to that doc and get your value.
The advantage of this is that you have no worries about the size of what can show in the dialog box. The disadvantage is that it's a lot more work to make and a lot more that can fail.
Tip 1: @DBLookup function for Web
Tip 2: LotusScript equivalent of @DBLookup and @DBColumn
Tip 3: Use scheduled agent to refresh all @DBLookup/computed values
Tip 4: Error with @DBLookup command: 'Entry not found in index'
Tip 6: Using Ajax as a replacement for @DBLookup
Tip 7: Set up a help desk app with @DBLookup and @Columnlookup
Tip 8: "Field or view column exceeds 32 Kb" errors using @DBLookup
Tip 9: Workaround for @DBLookup error
Tip 10: @DBLookup and @DBColumn for all browsers using Ajax