incubator-ooo-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andreas S├Ąger <>
Subject Re: Another Base Form question
Date Sat, 06 Oct 2012 19:24:30 GMT
Am 04.10.2012 15:52, Dan Lewis wrote:
>      When creating a Base form using Design View, I use the Control tool
> to select the properties I want for a field. Clicking it opens the
> Properties dialog. In the Data tab, the "Type of list contents" property
> contains these choices: Value list, Table, Query, SQL, SQL [Native], and
> Tablefields. I can use these: Value list, SQL, and SQL [Native].
>      Selecting Table lists the available tables in the "List contents"
> property. But selecting the appropriate table does not give me a
> dropdown list when I open the form. (I save my changes, close the form,
> save the database, and open the form.)
>      General question: What purpose does these properties serve? (Table,
> Query, and Tablefields) Any examples on how these can be used?
> --Dan

A list box maps the foreign keys of a one-to-many relation to human
readable list entries. You define a row set with 2 fields.
The visible list entries need to be in the first field (field #0). The
bound field (foreign key) may be bound to some other field (second field
#1 by default).

In the content type properties of a list box I see the following options
(AOO 3.4.1):
1) "Value List" is dysfunctional. For hard coded text entries choose
"List Entries" on the "General" tab.
2) "Table" is pure nonsense unless your table happens to keep the
visible content in the first field.
3) "Query": You have a query where you defined the 2 fields. Such a
pre-defined row set is useful when you use many instances of the same
list box. The stored query may work in "parsed mode" or in "direct SQL mode"
4) "SQL" lets you write a the 2-field query and store it with this
particular control. It will run in "parsed mode".
5) "SQL [native]" same as 4) but using the "direct mode".


The following receipt works for 99% of all my list boxes:

SELECT "Visible Field", "Foreign Key"
FROM "Some Table"
ORDER BY "Visible Field"

Type=SQL, bound field=1 (because the visible one is always 0).

Quite often the visible field is a concatenation like this:
SELECT "Surname"|| ',' ||"Forname"|| ',' ||"DayOfBirth" AS "Visible", "ID"
FROM "Persons Table"
ORDER BY "Visible"

which creates a 2-column list like this one
Visible | ID
Adams,Conny,2001-12-09 | 1
Bullock,Carl,1954-01-14 | 2
Zappa,Frank,1940-12-21 | 1254

The user writes the ID number into the foreign key field by picking the
right person by name and birth day.
The user starts typing the first letters of the surname. If there are
many persons with the same surname the users continues with a comma and
the first letters of the forname, then a comma with the year,... until
he gets the right person by typing or by picking the person from the list.
Alt+Down pulls down a drop-down box. for the above persons picker I
would use a tall multi-row list box though.

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message