Base- Drop down list on form in Datasheet view- Possible and how?

[WickerChair]

I am using LibreOffice 5.3 and working on a many-to-many relationship. I am using an intermediate table to allow the relationship, but when entering data I would like to be able to populate the sub form from a drop down menu.

I am looking at something like an order form where descriptions rather than part numbers are used.

I know that it is possible to have this arrangement in Columnar format, but I have been unable to figure out how to do it in the “Data Sheet” arrangement.

Is this possible to do and how?

EDIT:
I am working with a pull-down menu in the main form in much the same way that you have designed FilterDemo.odb. However, I am working with a sub form that is in Data Sheet “Tabular” format for clarity.
The entries in the Data Sheet “Tabular” format MUST match an external table (a single character changes the meaning: this vs thin).
I am entering in Data Sheet “Tabular” format so that the transcription of the information is simpler. (I do not want duplicates and I may have 2 or 200 items in the table on the sub-form.)
I can enter the information in the format that you recommend, but must then ensure that I know not only what entry I am on, but which entry I just completed.

From your example (sorry, I cannot upload the revised file):
The Bike Table is altered. Bike_Alt: ID_Bike, Bike_Make.

New tables are created:
Color: ID_Color, Color_Desc
Wheels: ID_Wheels, Wheels_Desc
Bike_x_Color: ID_Bike, ID_Color
Bike_x_Wheels: ID_Bike, ID_Wheels.

This parallels my many-to-many relationship.

The main form contains Bike_Alt.
Sub Form 1 involves Bike_x_Color and should allow for the color to be selected from a pull-down menu in tabular format (assuming that the bikes may have more than one color per bike).

Sub Form 2 is similar for wheels.

Hopefully, this clarifies what I am trying to do.

Here is the Sample using FilterDemo.odb:
FilterDemo.odb

Here is the database set up in a similar manner to that I am working on. The sub Form does not contain the value for Color, just ID_Color.
Here is the Sample file.
Bike_3.odb

[Ratslinger]

Will continue to re-read your description but example will be helpful. Have given you Karma to do so.

[WickerChair]

Thanks, I think I uploaded the file.

[WickerChair]

The table stores color and ID, but the cross-referencing table contains extra information that applies only to the color and that particular bike. For example, the main frame of the bike may be green, but the front fork is red. This bike would then have 2 colors associated with it, each color associated with a particular part of the bike.

I want to be able to query what color parts are available, what colors are on a particular bike, etc.
I’m just still in the design and data entry phase.

[Ratslinger]

I don’t get it. What was given has the Id in the record & displays the optional colors. Which table/field makes no difference. What is it you want displayed/saved?

[Ratslinger]

It appears the design isn’t apparent. The “cross-referencing” table makes no sense. If Bike is a Type, then its’ sub table would be model which contains all the options. You would have frame color, fork color, fender color, etc. Each color could be a drop down selection storing color Id but displays the color.

[WickerChair]

Color and Bike have a many-to-many relationship.
As I understand the normalization of tables, an intermediate table is designed so that information about each color and bicycle is recorded once and the intermediate table provides the relationship between them.
However, in order to make that intermediate table small, it contains only the primary keys of the other tables. I want to display the ID for the Bike (non-color information is different sub form) and the Color name (while saving color ID)

[WickerChair]

In the context of a bicycle, a table of all possible parts that could have a color is doable.
When you are working with over 7000 parts, and 20000 bicycles…
I am truly only trying to make the form do one thing. Connect colors, parts, and bicycles. Since all of these have IDs as primary keys, I cannot figure out how to combine names rather than the IDs on the drop-down table.

[WickerChair]

Okay, perhaps it would be better to explain that my database is designed to list symptoms of diseases identified as VARCHAR(350) and drugs identified as VARCHAR(150) with abbreviations VARCHAR(20). In this case it should be evident why I am using IDs, but want pull-down menus.

[Ratslinger]

Are you saying the dropdown will have over 7000 items?

[WickerChair]

Yes, but I have found that beginning to type in the abbreviation calls up the correct one. It is just that typographical errors are very bad news in this case.
This is a split database to deal with the large volume of data.

[EasyTrieve]

@WickerChair, I have a similar many-to-many database that I am working on porting from Access. It also uses a table of links between for example People and Addresses to allow many-to-many. It works fine on Access but I have run into bugs in Base and to date am stalled at getting it to work correctly on Base. I’ll report back here if I am able to get something to work. The bug that’s got me stuck at the moment is one that incorrectly updates a compound query key when a new record is inserted.

[Ratslinger]

Edited 5/31/17:

I have removed all previous text in this answer because of new information provided. However, the original answer of replacing the column in a grid control with a list box still stands.

I have attached your new sample with a modified form showing how this is done. The two forms produce the same result with the new alternate form being easier to use.

Sample - ForumRubric.odb

Edit: I am adding a screenshot with the listbox in the table control simply because this is now the third sample provided and yet you still seem to think this is impossible. I don’t understand why.

If this answers your question please click on the (upper left area of answer).

[WickerChair]

Useful, but, not what I need.

[WickerChair]

Thanks for all of the help.

The short answer to my question at the current time is: It is impossible to have a pull-down menu in a cell in the datasheet view of a form.

However:

Here is a workaround so that the information can be entered in a sub Form in columnar view (which allows list boxes) that feeds into the datasheet (so that multiple entries are displayed in the table).

Here is the database and forms that I have developed to work around the problem.
Demo Database.odb

The way that I figured out how to accomplish something approaching what I wanted is as follows:

1. Use an alphanumeric string as the primary key rather than a numeric identifier. This increases the size of the intermediate table significantly but allows for this workaround to function;

2. The datasheet is modified so that all columns are visible but are read only and disabled;

3. List boxes are placed above the table for data entry (These are accompanied by buttons that indicate when the particular entry is complete); and

4. A button is placed to proceed to the previous or next record on the main form.

Hopefully this will help someone else.