How to make a dropdown list in a database field

Give the text field a value list. In Base by Zaweba you type the allowed choices into the field's settings in the table designer, and they appear as a dropdown in the datasheet, on forms and on the exported website. Tick "only these choices" if nothing else should be allowed.

It works much like the typed list option in Access's lookup wizard, and for short fixed lists it is the quickest way to keep entries consistent.

Setting up the list

Open the table in the table designer and pick the text field, for example Status on an orders table. Type the choices: New, Paid, Sent, Returned. Save the design. From then on, clicking in that column in the datasheet, or in the matching box on a form, shows a dropdown of those words.

Think about wording once, at the start. Short, distinct choices are easier to pick and easier to filter later. Avoid two that mean nearly the same thing, such as Done and Complete, because people will use both.

Allowing only the listed choices

Without the restriction, the list is a convenience: people can pick from it or type something else. That suits fields like Town, where most entries are local but the odd one isn't.

With "only these choices" ticked, Base enforces the list as a CHECK constraint in the database. That is stronger than a form setting, because it applies everywhere a record can be written: the datasheet, forms, imports and button scripts. A CSV import with a stray value in that column will skip those rows and list them rather than letting a misspelling in.

If you add the restriction to a table that already holds records, tidy the column first. Sort or filter the datasheet to find the odd spellings and correct them.

When a lookup is better than a list

A value list is right for a handful of fixed words that won't grow much. When the choices are really records in their own right, use a lookup field instead. Customers, products, staff and suppliers all belong in their own tables, with a lookup from the orders table pointing at them. A lookup is also shown as a dropdown, of the other table's text, but you can add a new customer without redesigning anything, and you can store more about each one.

A simple test: if you would ever want to add a column to the choices themselves (a phone number for each supplier, a price for each product), it should be a table and a lookup.

Using the list afterwards

Fields with a fixed set of values are a gift when you come to ask questions of your data. In the visual query builder you can filter on one value or several using the in list condition, and group by the field to count records for each choice. A grouped report on Status gives you a heading per status with a subtotal under each. A crosstab can put the choices across the top as columns.

On forms you can also add conditional formatting so that a text box turns red or green depending on a value, which works well with a short list of statuses.

Dropdowns in the browser version

When you export the database with File > Export to website, the value list comes too. The add and edit pages show it as a dropdown, and the restricted lists are enforced in MySQL or MariaDB just as they are on the desktop. Base by Zaweba builds this from the table design, so there is nothing extra to set up for the browser version.