I make a listbox in a subform, but i cannot add a lookup field as column.
e.g. table of actors (id-crew=key, name etc), table of theater plays (id-prod=key, play, director etc.) and table of actors (id-role=key, id crew, id prod, role, play)?
subform based on tbl-plays & tbl-actors with joined id-prod
The attached sample has
Plays
Productions
Roles
Actors
Castings
A casting collects which actor is playing which role in which production.
In the “Roles” form you assign roles to plays.
In the “Castings” form you select a production with a play-ID and the roles of the play appear in a subform. Then you walk through the roles and assign an actor to the selected role. Theoretically, you can assign more than one actor to the same role, which was not quite intended but possibly useful. On the right side you can enter the selected actor’s data (which is only his/her name in this sample).
The castings form includes a trick:
Because each casting includes a production-ID but the productions are not included in the parent form, which is about roles, we need to take over the grandparent form’s production-ID.
The source of the roles form is SELECT "ROLE".*, :pProdID AS "PRODID" FROM "ROLE" which takes one parameter pProdID as column “PRODID”. The parameter is associated with the parent form’s production-ID which effectively pulls over the production form’s ID value into the roles. Then this PRODID column can be used by the castings in the child form.
theater.odb (33.3 KB)
Regarding the initial question:
A list box selects some item’s ID value into a foreign key field, e.g. a cast’s actor-ID or a production’s play-ID. The perfect recipe for a working list box is a 2-column query of unique items: SELECT "some visible text", "Primary Key" FROM "Items" ORDER BY "some visible text" ASC. You can either store this statement as a query (the lb… queries in my sample) and link list boxes to that query, or you can store the query in the list box properties (source type:SQL). The “Bound field” property of a list box is 1, meaning the primary key in the second column; the first field would be 0. This way, the list box lets you select some item from an alphabetically sorted list of names and writing the corresponding item ID into a foreign key field of the form. The listbox of plays in the productions form fills the “PID” (play-ID) of the PROD table by selecting an ID-value from the PLAY table while presenting the play’s name. Referring to the above picture showing the database relations, the listbox selects the 1-side of a relation line (primary key) into the n-side of a relation line (foreign key).
Contrary to the list box selecting one distinct value into a foreign key field, a form selects an entire record for editing. And a subform selects all the records of another table that are related to the parent’s selected record. How they are related to each other is defined in the subform’s “Master” and “Slave” properties, which are not necessarily the same relations as the relations between tables.
There is an important limitation to LibreOffice list boxes: You can’t filter them easily. For instance, a list box can’t easily show only the female actors to be selected for a female role in a casting.