Good morning all,
I have a background in database design for websites, but I don’t have experience with Base, (or MS Access). I am trying to create a form that presents a dropdown list for a related table, but presents the NAME field of the table, not it’s ID, for the display or selection of the ID. I can create the dropdown that displays the IDs for selections, but I cannot figure out how to present the NAME.
Main-Table has a TYPE field, INT, linked as n-to-1 to the ID of the TYPE table. I want to display the NAME from the type field in the dropdown, but select the ID (hidden) for storage in the TYPE field in the Main-Table.
How do I make the dropdown present the NAME instead of the ID?
Thanks!
Get the data properties of the listbox.
Linked field: the column with the integer foreign key
Source type: SQL
Source:
SELECT "Text" AS "Visible", "Primary Key" FROM "Other Table" ORDER BY "Visible"
replace “Text”, “Primary Key” and “Other Table” with the real names of column and table.
Bound field: 1 (zero based, the visible would be 0)
Now you write the the other table’s primary key key into the form’s foreign key by picking a text from the listbox. The text field may also be a concatenation such as “Surname” || ', ’ || “Last Name”.
Alternatively, if you need more of the same listbox, store the SQL as a query choose source type “Query” instead of “SQL” with the query name as source.
OK, I tried the command suggested. Here is the exact SQL in my query.
SELECT “Type” AS “Visible”, “ID” FROM “DidType”
Running this query results in the following output, as expected.
Visible ID
FN 0
IE 1
PM 2
I have setup the form as shown as you suggested (I can’t upload more than one embedded. The images are here: Libreoffice Base Troubleshooting - Album on Imgur)
However, when I load the form for use, I get the following error:
For reference, here are the relations in the database:
I may have something somewhere else messing it up through my attempts to get this working but I don’t know where.
Appreciate the help!
Statik
I can reproduce the error message with a wrong “Bound field”. Bound field should be 1 if the second column contains the other table’s primary key to be written into the form’s foreign key.
Yeah, that fixed the error. For some reason, the Control Properties didn’t update when I changed the type to Query until I closed and opened the form. The Bound field was set to 2 and I changed it to 1. Now the proper dropdown appears in the form.
I also found somewhere along the way that I had changed the entire form to a query, rather than a table, so the form didn’t work. That is fixed now.
Thanks!
Now I have to figure out how to do the subtable with the many-to-many setup. 
You need the form navigator (menu:Form>Form Navigator).
RIght-click the form and add a subform.
[Tutorial] Forms in OpenOffice.org Base
[Example] Relations reflected by list boxes in forms (demo file with one-to-many and many-to-many).
I went through the tutorial and the forms and it looks like I have everything setup the same, showing the proper data for the database, however, I cannot seem to add new data to the database. If you look at the Persons form in the demo, the many-to-many is the many T_IDs table. I have created the same sort of table and it is picking up the proper field label. What I am not getting is the selectable drop down in the table. Once I can figure out how to make that dropdown work, I think I’m golden.
RACI.odb (30.0 KB)
The “Security” column in form “DID” of your RACI.odb?
While in design mode, rIght-click the column > Replace with > Listbox
Data properties:
Linked field: SecurityID (the foreign key)
Input required: Yes
Source type: SQL
Source: SELECT “Security”, “ID” FROM “Security” ORDER BY “Security” ASC
Bound field: 1 (refers to the second column)
or create a query with the same SQL for the listbox, choose source type “Query” and point to that query.
I’m running version 7.3 so the quotes threw it off. Its working now. Thank you! I think I can build the rest of it now.
The problem is that this forum software converts straight quotes into typographic quotes unless you put everything in a code block.
SELECT "Security", "ID" FROM "Security" ORDER BY "Security" ASC
A minor issue remains with your table design. The linking tables of your many-to-many relations have auto-IDs as primary keys. This allows for multiple assignments of the same (same Security ID twice fot the same DID).
In my linking table P_T between things and persons, I declared both foreign keys as a compound primary key. This ensures that the combination of PersonID and ThingID is always unique while each P_ID has a matching Person.ID and each T_ID has a matching Things.ID. WHile in table design you can select multiple column definitions and mark them as primary key.
However, this will be difficult to fix in your database because you have to release all relations, drop the primary keys, redefine tables and link everything again.
Yes, I noticed that. My original training with MySQL, way back, didn’t allow for multiple keys like that. I don’t have much data in the DB right now so I can recreate that table and create the uniqueness I need.
Thanks!