ListBox list content problem

pauly · February 3, 2022, 1:20pm

Hi friends. Today I have come across a problem. I have made a List Box, in the List content I have written:

SELECT “City” || ', ’ || “District” FROM “tbl_IssuedOrgan” ORDER BY “City” ASC,“District” ASC

It works well until, sometimes, there is no value in District because the City is too small, there is not even one District, then I got a empty row when I click the Listbox’s drop down button.

I tried to change the first part to:

SELECT “City” || ', ’ || “IF(ISBLANK([District]);”";[District])"

But unfortunately it gets an error:

The contents of a combo box or list field could not be determined.
Unexpected token: SELECT in statement ["SELECT "]

This problem is beyond my ability. Please help. Thanks!

(LibreOffice 7.1,5.2, Mac OS X 10.16, HSQLDB)

Ratslinger · February 3, 2022, 5:01pm

Hello,

Should be able to use Coalesce. It returns the first non Null item.

 COALESCE

COALESCE( <value expr 1>, <value expr 2> [, ...] )

Returns <value expr 1> if it is not null, otherwise returns <value expr 2> if not null and so on. The type of both arguments must be comparable.

pauly · February 3, 2022, 8:56pm

Thank you Ratslinger! Coalesce did the trick.

BTW, I found IFNULL works too, like:

SELECT “City” || IFNULL( ', ’ || “District”, ‘’ ) FROM “tbl_IssuedOrgan” ORDER BY “City” ASC, “District” ASC

I see how powerful BASE can be. I’m more interested and motivated in learning it now. Thank you for your support.

Villeroy · February 3, 2022, 5:14pm

If your connected database happens to be HSQL, it is documented at Chapter 9. SQL Syntax :

SELECT Coalesce("City" || ', ’ || "District", "City") AS "V", "ID" FROM "Cities Table" ORDER BY "V" ASC)

which selects the visible string as “V” and the primary key “ID” from “Cities Table” so the listbox writes the other table’s primary key into this form’s foreign key field. For better usability the row set is alphabetically ordered by the visible string.
Linked field: this form’s foreign key
Source type: SQL
Source: above statement
Bound field: 1 (0 is the visible, 1 is the second one we write).
If you need many similar list boxes:
Source type: Query
Source: the name of the query where you stored the above SQL.

pauly · February 3, 2022, 8:59pm

Hi Villeroy, thank you so much. You are right. Coalesce works perfect for this purpose. And thanks too for Chapter 9. SQL Syntax . That is a great resource.

pauly · February 3, 2022, 6:13pm

Guys, you are so helpful. Will check it ASAP. Thanks so much!
PS: What I want to achive is, to let District return empty value when it is NULL.

Villeroy · February 3, 2022, 6:18pm

This is not the problem. Nevertheless, it is totally unclear why you need a list box and from which source you intend to fill it. I just described the recipe which always works when you depict a one-to-many relation by means of a list box writing another table’s primary key into a form’s foreign key.

cpb · February 3, 2022, 7:07pm

use COALESCE to replace null value with empty string(’’):

SELECT "City" || coalesce(', ' || "District", '') 
FROM "tbl_IssuedOrgan" ORDER BY "City" ASC,"District" ASC

EDIT:
oh no!! been too hasty where is your bound field?

pauly · February 3, 2022, 9:05pm

[quote=“Villeroy, post:3, topic:73494”]
Nevertheless, it is totally unclear why you need a list box and from which source you intend to fill it. [/quote]

Oh, It is because one city can have many districts, and I need to select an organisation from a certain district of a city, that’s why when I click the dropdown button of the ListBox I want to see CITY and DISTRICT together. The source is a table called IssuedOrgan (Organisation that issued the document) table.

pauly · February 3, 2022, 9:27pm

Hi dear cpb, your solution is great. It did the trick. Thank you!

oh no!! been too hasty where is your bound field?

The source table for the list box has 3 fields:
IssuedOrgan_ID
City
District

The bound field is 1. I did it some 4 months ago, and I forget why at that time I set bound field as 1.