Giving a field a dropdown of choices in Base by Zaweba

Plenty of fields only ever hold one of a handful of values. A status that is Open, On hold or Closed. A membership that is Junior, Adult or Senior. A size of Small, Medium or Large. Typing those by hand invites variations, and a report grouped by status then shows Closed and closed as two different things. Base by Zaweba lets you give a text field a value list: the choices typed straight into the table designer, offered as a dropdown wherever the field appears.

Anyone who has used the lookup wizard in Access will know the idea. It is the option to type in the values you want, rather than look them up from another table.

Where the dropdown appears

Once a field has a value list, it shows as a dropdown in the datasheet, on every form that uses the field, and on the website that Base can export. People pick rather than type, so entries are quicker and they match. You set the list once, in the field's own settings, and every place the field is used picks it up.

That is also why it is better to set the list on the field than on one form. A list on the field is there for imports to check against, for new forms you make later, and for the browser version of the database.

Only these choices

By default a value list is a helping hand: the dropdown offers the choices but someone can still type something else. Tick the option to allow only these choices and the list becomes a rule. Base enforces it as a CHECK constraint in the database itself, so a value outside the list is refused whether it comes from a form, the datasheet, an import or a script.

Use the strict option for codes and statuses that drive queries, reports or conditional formatting, where an unexpected value would quietly drop a record out of a total. Leave it off for fields where the list is a suggestion, such as a usual town or a common job title, and you want people to be able to add the odd exception.

Value list or lookup table

A value list suits short, fixed sets that rarely change. When the choices are long, change often, or need details of their own (a product with a price, a supplier with an address), make them a table and link to it with a lookup field instead. A lookup also lets you add a new choice by adding a record, with no change to the design.

A rough guide: if you'd never need to store anything about the choice except its name, and the list fits comfortably in a dropdown, a value list is simpler. If you can imagine wanting a second column, use a table.

Getting the list right

Write the choices exactly as you want them to appear in reports, with consistent capitals. Put them in a sensible order, which is often the order things happen (Enquiry, Quoted, Ordered, Delivered) rather than alphabetical. Keep the list short enough to scan at a glance.

If you switch on the strict option for a table that already has records, check first that existing values match the list, using the datasheet's filter or a query that groups by the field. Tidy any stray spellings, then turn the option on. A field with a well chosen value list is one of the cheapest ways to make every later query and report in Base more reliable.