I have the same "question" - so far unanswered, you may find it in here, when searching for "Lookup fields", but finally a diffent approach, so I like to explain some guesses how it should work - as a workflow, as I make concepts, I am no programmer, and even that idea is not mine, it is the rebuild of something quite famous - in DOS and about 25 years ago.
Well, this is the problem. You describe what you have in mind rather than your actual database.
There are no "Base databases" since the main intention of Base is to serve as a frontend to existing relational databases which can be described in the same terms, following a uniform logic.
A database is a set of tables, tables consist of strictly typed fields with indices and keys.
The keys make one-to-many relations (1-n), one-to-one relations in rare cases, and you can implement many-to-many relations with the help of a mapping table, connecting the 3 tables with 2 one-to-many relations.
This is the same scheme in all common relational, SQL-capable databases since decades and Base forms are designed to let a user edit relations of such bread-and-butter databases. Manual relation editing through input forms maps keys to mnemonic names. This is the core of Base forms as I understand it and list boxes and subforms are the most important parts of the concept. All the rest is about the ergonomic convenience of type-ahead combo boxes, calendar controls, numeric spin button fields, pattern fields and so on.
Base has one important precondition: A valid database conformant to some formal rules.
Main problem with many Base users seeking for help this forum: They seem to have no database behind their forms. They always describe the forms, preferably through screen shots.
Quite often their database is a lose collection of fields derived from an informal mind map. Base forms do not work with this. Base forms are very, very simplistic. Each control serves one distinct purpose and nothing else. In particular you can not use a list/combo to filter records. Knowing that a list can select values into records and a subform can filter records, you may develop quite advanced forms combining the features of the given tool set.
All in all Base is not a replacement for any of the commercial tool sets and worse: Most of the wizards are misleading.
Just 3 of the problems:
The table wizard does not help you to build up a valid relational database. It just saves you some clicks.
The form wizard covers relations in one way only (one subform to reflect the n-side of a one-to-many relation). Manual creation of forms gives you far more options.
The built-in database engine which allows you to create a backend database from scratch is poorly supported by the Base frontend. You can add some important features to your database structure by means of the command line (Tools>SQL): Timestamps, constraints, sequences.