Advanced Database – Create, modify or delete a lookup in a field
Libreoffice Base doesn’t allow you to create a ‘lookup’ list in a table in the way it does in MS Access. We will have to use a form to do the same thing.
Right click on the Jobs Entry form and select ‘Edit’
This will open up the design mode for the form.
We’re going to create a ‘lookup’ list for the Staff Number field, so select the box by holding ‘Ctrl’ on the keyboard and left clicking the text box.
Now, right click and hover over ‘Replace with’ and select ‘List Box’.
A list box allows you to create a drop-down list so people can enter only certain specified values.
Next, right click and click on ‘Control…’
This will open the control dialogue box.
Click the Data tab, make sure the Data field is ‘Staff Number’, Input required ‘Yes’ and select ‘Valuelist’ for Type of list contents.
Click on List content. If we need the option of a null value we’d press Shift+Enter together on the keyboard to leave a blank line, but null values aren’t an option for this field.
Type in the first value 101, then press Shift+Enter together to skip to the next line.
Then type 104, press Shift+Enter and so on until you have the six possible options.
Press enter when finished to store the list.
Click the drop down menu for List content again, select all the values and press Ctrl+c to copy the values for the next step.
Click the ‘General’ tab at the top of the dialogue box.
Click the List entries drop-down menu and press Ctrl+v to paste the list values.
Select ‘Yes’ from the Dropdown option.
Type ‘6’ into the Line count box, as we have 6 options in our list.
Close the properties dialogue box.
Click the Design Mode icon to toggle out of design mode, allowing you to make changes to records.
Click the Data source as table icon to display the Jobs table on the top of the window.
Click the New record icon to add a new record.
Use the drop-down box for Staff Number to check that you can only select the values we set in the list box.
To modify the lookup/list, just go back to form design mode, right click the list box, select control then edit the lists under the data and general tabs.
To delete the lookup/list, go into form design mode, Ctrl+right-click the list box, select Replace with and change it back to a text box.
A combo box allows you to include a list of possible values to enter (like a list box), but also allows you to type an entry.
Only use a combo box if you don’t mind other entries that aren’t on the list, i.e. maintaining data integrity isn’t crucial for that field.
To specify a combo box, ctrl+right click on the entry box, select ‘Replace with’ and choose ‘Combo Box’.
Right click the combo box and select ‘Control…’
Similar to the previous example for List box, go to the Data tab, choose Valuelist and add the specific values.
Copy those values and paste them in List Entries under the General tab.
Close the control and switch from design mode to entry mode.
Try typing in a value that isn’t in the drop-down menu.
Notice that unlike a List Box, a Combo Box will allow you to type in a value that isn’t on the list.
This is good for fields where you may have lots of common entry values, but occasionally may need to enter a completely different value or text string.
E.g. an address form where most live in the 3 closest towns, but some live further afield.
