Populate foreign key using combo/dropdown on form

Question

I'd like to populate a foreign key field in one table using a combobox/dropdown where the values the user sees are not the IDs but a description field from the foreign table.

Consider the following setup:

create table people (
  id int identity primary key,
  name varchar(250),
  job_id int
);
create table jobs (
  id int identity primary key,
  description varchar(250)
);
insert into jobs(description) values 'foo';
insert into jobs(description) values 'bar';

I have specified the foreign key relationship in the relationships window:

And I'd like the user to see jobs.description when creating a new person via a form.

But I can't see how to do this. If I create a form based on a query which joins the two tables, then I can't create new data, and if I create a form based of people, the Subform based on existing relation option is greyed out:

In Access this is easy to achieve with the Form wizard:

How can I achieve this in LibreOffice/OOo Base?

Answer 1

This can't be done using the form wizard. The linked form stuff is a red herring.

But it can be done fairly easily, as follows.

1. Use the form wizard to get the form as far as you can with the fields from the people table only.
2. Edit the form in design mode (*hint: the icon is completely unintuitive, but looks like this: )
3. Add a 'list box' control to your form (note: not a combo box, these are something different in Base). This will open a wizard which will take you through everthing else.
4. At Choose the table from which the data should be used as basis for the list content, select jobs.
5. Select description as the Display field.
6. Under Field from the Value Table select job_id and under Field from the List Table select id.

That's it! The dropdown box will now work exactly as expected:

If you don't like wizards, you can manually edit the List Box control as follows:

1. Add your list box then close the wizard. Click on the "Control" icon, which is a gear. (This also makes no sense.)
2. Enter the information as follows, where 'List content' says "SELECT "DESCRIPTION", "ID" FROM "JOBS". Notice that the field you want to the user to see must come first, and that the 'Bound field' is zero-indexed and should refer to the ID column.

That's it!