Hi R,
Interesting question! There are two ways to create a listbox.
1) Add an enum column to your table.
You can do this in the Data Designer. Enter your listbox values in column “List values” in the following format: ‘italian’, ‘dutch’, ‘english’ (and so on). The first value will be the default. If you prefer another default value you can enter that value to column “Default value” in format ‘dutch’. I added a screenshot to see this in action.
https://wpdataaccess.files.wordpress.com/2019/05/2019-05-08.png
If you like to create a listbox that supports the selection of multiple values, just change “Column type” from enum to set.
You can also create your own create table script and execute that script in the Data Explorer (use button “Import data/Execute script(s)”). Here is an example:
CREATE TABLE person (
id mediumint(9) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
gender enum('male','female') DEFAULT NULL,
email varchar(255) DEFAULT NULL,
language enum('italian','dutch','english') DEFAULT 'dutch',
PRIMARY KEY (id),
UNIQUE KEY unique_email (email)
);
If you want to add a listbox that supports the selection of multiple values, just change column language as follows:
language set('italian','dutch','english') DEFAULT 'dutch',
NOTE
Option 1 will store the text values in your database. So ‘italian’ will be stored as ‘italian’. Not as ‘ITA’. To store your ‘italian’ as ‘ITA’ you could create a lookup table. Which brings us to option 2.
2) Create a lookup table and a relationship between your application table and the lookup table.
Your lookup table could look like this (this is just an example):
CREATE TABLE language_lookup (
language varchar(10) NOT NULL,
text varchar(100) NOT NULL,
PRIMARY KEY (language),
);
Languages can be added like this:
INSERT INTO language_lookup (language, text) values ('ITA', 'italian');
You could change the person table like this:
CREATE TABLE person (
id mediumint(9) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
gender enum('male','female') DEFAULT NULL,
email varchar(255) DEFAULT NULL,
language varchar(10) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique_email (email)
);
Finally create a relationship between the tables person and language_lookup:
– Navigate to Data Project
– Click tab “Manage Table Options”
– Add table person
– Edit table person
– Add a relationship of type “lookup”
– Select table language_lookup as your target table
– Select column language as Source column name as well as Target column name
– Save your relationship
– In area “Manage columns for data entry form for table person” select text in the lookup listbox
– Save your settings and your done
You can also add column text to your list table in are “Manage columns for list table of table person”.
With option 2 you can select only one value from the listbox but column language in table person will now have value ‘ITA’.
That was quite some typing… ?? Please let me know if this helps.
Best regards,
Peter
