I’m making a form in Base (with HSQLDB), and i would like to have a drop down list of people with their forename, middle name (if they have one) and surname. I want the list to display the full name, but the value in the form to be the ID. But, i get an empty line in the list for people without a middle name when using this query in the “List content” field:
SELECT “Middlename” || ’ ’ || “Surname” || ', ’ || “Forename”, “ID” FROM “People” ORDER BY “Surname” ASC
I have these columns in table “People”:
ID (automatically assigned)
Forename
Middlename (optional)
Surname
Would there be a way to use two different queries, one if the column Middlename is NULL, and one if it has any content? For example these two queries:
SELECT “Surname” || ', ’ || “Forename”, “ID” FROM “People” ORDER BY “Surname” ASC
SELECT “Middlename” || ’ ’ || “Surname” || ', ’ || “Forename”, “ID” FROM “People” ORDER BY “Surname” ASC
Returning a list like this:
Surname, Forename
Surname, Forename
Middlename Surname, Forename
Surname, Forename
Middlename Surname, Forename
Middlename Surname, Forename