Apache OpenOffice (AOO) Bugzilla – Issue 99532
Listbox Limiting or Narrowing for form List box
Last modified: 2013-03-08 15:14:08 UTC
A common question on both community forums in the "Base" section is how to design a form where the selection made in one List Box determines the available choices in another. A crash course in Basic programming is usually what follows, deterring many beginners from continuing to learn the database application. The procedure to "code around" this missing feature often requires at least a page of customized code. A worthwhile enhancement to the list box would be for this procedure to be vastly simplified. Presently the wizard associated with a list box steps the user through constructing a list box whose list is governed by a SQL statement, which can reference a table or a view. This would assure that most users would set the list box up this way rather than using a value list. By setting up two tables which have a primary key, foreign key relationship, the data structure required for a list box to limit the values in another is borne. The data can be input into these tables in the required related fashion by simply creating a form with a mainform - subform relationship. This is easily achieved when constructing forms using the "Forms Wizard". An enhancement suggestion would be that an additional pair of properties in the list box be created such that a foreign key value could be associated with a foreign key field from the data table of the listbox. This would then be used in the "WHERE" part of the SQL statement. The listbox should be able to obtain the value from either the form or the form's rowset and then execute a "refresh" when ever this value changes. For an example of the type of macro presently required, please see these postings on the forum... http://www.oooforum.org/forum/viewtopic.phtml?t=71055&postdays=0&postorder=asc&start=15 and http://www.oooforum.org/forum/viewtopic.phtml?t=74688 For an indication of the popularity of the question on the forum please go to these community forum's search field and type in listbox and Voobase as the author... http://www.oooforum.org/forum/search.phtml and http://user.services.openoffice.org/en/forum/search.php
To me, this sounds like a "filter control", e.g. a special case of the more general idea where the user input in a control limits (filters) the content displayed in a (logical) form. In this case, there's just currently an open window for all users wanting this to vote for it :) See http://blogs.sun.com/GullFOSS/entry/still_have_some_wishes_for for details.
No, I do not believe it is a similar concept. A "filter control" is, as you mentioned, concerned with changing the filter set for a form and possibly then organising a reload of the form. Using one listbox to narrow the selection in another would instead require one listbox control to effect a second listbox control. A list box "filter control" would be concerned with displaying information from your form such as.. display all names with surname "Smith". A list box which narrows another would instead be concerned with entering information into a form (mostly), for instance select in one list box the manufacturer and then the list in the second list box is immediately limited, to only show the model's available from that manufacturer. Presently a "filter control" is already easily achievable in Base with only a couple of lines of macro code required (to organise a form reload). The trick is to use the link/join between a mainform and subform. To limit the list in a listbox depending on anothers selection requires much more code. To see for yourself the difference between the two, please have a look at these two example databases. The first is for a "drop down record switcher" which would be a similar concept to the "filter control" and is found here... http://www.oooforum.org/forum/viewtopic.phtml?t=72134 The second is for an example database called "classes" which is found here... http://www.oooforum.org/forum/viewtopic.phtml?t=71055&postdays=0&postorder=asc&start=15 (You will need to import the macro file separately for this one which should be in the zip file). I believe, on the community forums, it has been asked more often by at least a factor of 2 to 1 for help to get one list box to narrow another, other than for a listbox to filter a form.
Ah, I see what you mean. Well, assuming that the second list box is filled from the database, too (and does not use value lists), then if the first list box would act as filter control not for its form, but for the result set which populates the second list box ... then I could somehow save my "this is a filter control" approach :) But you're right, those are different things.
Just wanted to add a few comments here: I would agree that the more common request is to have a selection in one list box influence the available choices in a second list box. At least from my anecdotal experience. Also, that when the question from uses is regarding using a list box to filter a result set that the use of a simple macro usually results in a reasonable outcome for the user - when it is to alter the items in a second list box it is much more problematic. Going along with the discussion here, I also agree that the hurdle that seems to be the most troublesome is the inability to get at the actual FK value used by the control - another way to say this might be - the inability to get at the actual result set that produces the itemList. Adding a new property to the ListBox control to expose this FK value would be one way to approach this, sure. At this point at least the creation of the required macro would be about equal in complexity to that of the listbox as filter.
Created attachment 80383 [details] New content type item in Form properties dialogbox RFE: add to Form properties dialogbox, a new item for "Content type" to choose a listbox control. This will more helpful for end-users.