Issue 99532 - Listbox Limiting or Narrowing for form List box
Summary: Listbox Limiting or Narrowing for form List box
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 1.0.0
Hardware: Unknown All
: P3 Trivial with 4 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2009-02-23 13:26 UTC by voobase
Modified: 2013-03-08 15:14 UTC (History)
2 users (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---

New content type item in Form properties dialogbox (112.83 KB, image/png)
2013-03-08 15:14 UTC, oooforum (fr)
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description voobase 2009-02-23 13:26:02 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...

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...  and
Comment 1 Frank Schönheit 2009-02-23 20:28:29 UTC
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 for details.
Comment 2 voobase 2009-02-24 12:47:13 UTC
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

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... The second is for an
example database called "classes" which is found here...
(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.
Comment 3 Frank Schönheit 2009-03-04 21:37:52 UTC
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.
Comment 4 drewjensen.inbox 2009-03-04 22:45:26 UTC
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.

Comment 5 oooforum (fr) 2013-03-08 15:14:08 UTC
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.