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.
Thanks for both answers. I am trying to adapt my tables to what you sent me.
- For my plays-table, I need to adapt a field (see print screen), how do I find/replace data in the field Id-prod (letter a must be 01, b = 02, c = 03 etc.), then spaces need to be deleted and finally where data is not longer than 6 digits, 00 must be appended.
- I see in your samples that you have 2 or 3 primary keys in a table. When I want to do this, the first PK is deleted and placed in the second field???
Sorry for the dummy that I am.
Why do I have to get my head around screen shots? Do you really expect me to generate a test table and type in sample values for testing?
From your screenshot, I see that the third column has the same numbers as in the second column. These data are redundant. I would delete the year numbers in the second column and keep
0300
1001
1002
1003
1200
etc.
Whenever you need both values in one string, you can concatenate both columns.
Is Id-stuk the primary key? All these details can not be seen on a screenshot. You don’t send photos of your broken car to the mechanic, do you?
Id-stuk is indeed primary key. I have arranged the fieds to be integer?
Now I want the relationship between crew-rollen and producties, but not possible. Yes, another print screens joined. Makes this all a bit clearer?
There is a value in “crew-rollen”.“id-crew”, which isn’t in “crew”.“id-crew”. So the two fields couldn’t be connected.
According to me “crew”.“Id-crew” and “crew-rollen”.“Id-crew” do exist and are even connected. But what I want to connect is “crew-rollen”.“Id-prod” with “producties”.“Id-prod” ???
codes.odb (13.1 KB)
TBL1 is what you have.
TBL2 is what I would prefer: 3 columns for year number, 2-digit code and the trailing code (a,b,c → 01,02,03).
There is a view which converts what you have. It returns “Old Code”, “New Code” and the ID.
UPDATE TBL1 SET C = (SELECT "New Code" FROM "vConvert_TBL1_Code" WHERE "vConvert_TBL1_Code"."ID" = TBL1.ID) will do the replacement.
Another view splits up your old code into year, code1 and code2. It can be helpful to fill a new table with the split codes of the old table.
This enforces unique combinations of actor, production and role. It can be difficult to modify the structure of a database, particularly when the tables contain data and particularly when primary keys are involved.
A single column as primary key will do as well. In order to prevent duplicate castings, you may add a unique index. Open the table for editing, Tools>Index Design, enter a descriptive index name, mark it as unique and add the columns.
The “id-prod” of “producties” table must be a primary key! Alternatively, “id-Stuk” may be used as the “relationship” primary key with the “crew-rollen” “id-prod” as the foreign key. It is suggested to 1) delete the “producties” from relationships, 2) save and close relationships, 3) important: save the database! This gets rid of any “phantom relationships” that may exist and adversely affect the relationship schema! 4) reopen relationships and add “producties” table, 5) try creating relationship between “id-Stuk” and “id-prod”! Suggestion: Before creating the relationship, “producties” “id-prod” may be deleted, save the table, edit “producties” and rename “id-Stuk” to “id-Prod”, save table and DB, create the relationship! Note: To edit primary or foreign key columns, their relationships must be deleted and DB saved first!
1 Like