I need to create a list in a list box that pulls info from several fields.

[chemistry337]

Good Morning,
I am using Libre Office 6.3, specifically Libre Office base on Win 10.
I am trying to create a list box in a form that pulls data from several fields in one table to make data entry to another table easier and more consistent.
The table that I am pulling data from is called: Property_tbl and has the following fields:

Property_UID (Primary key)
House_name
House_number
Street
Town
County
Postcode

The table I’m trying to add data to via a form is called Booking_info_tbl and has the following fields:
Booking_info_UID (Primary key)
Property (Foreign key - to Property_tbl primary key)

I have managed to generate a list using:
SELECT “House_Name” || ', ’ || “House_number” || ', ’ || “Street_1” || ', ’ || “Town” AS “Address”, FROM “Property_tbl”

However, not every property has a name so some of the House_name fields in the Property_tbl are blank.
This causes the whole those entries in the list generated on the form to be blank.

How do I overcome this?

Also, is it possible to have 2 columns in the drop down list.

All help greatly appreciated.

Thanks and regards

Chem

[Ratslinger]

Hello,

Please note, with Base questions you should always include which database you are using.

[Ratslinger]

Hello,

Please do not ask multiple questions in a single post. It makes it difficult for other trying to find an answer to a similar question since the question is now buried in another question.

Your question is confusing as it appears you may be trying to update a table with several fields from a list box. This is not possible since the list box is only attached to one field. This also relates to your second question about 2 columns. Again confusing. You need to be more specific about what you are trying to accomplish rather than how you may think this is to be done. These are not always the same.

Now as far as the blank records, this is because you have NULL fields which when used in conjunction with anything else results in NULL. You can get around this with a COALESCE function. This will use the first non-NULL occurrence. For example:

SELECT COALESCE("House_Name",'') || ', ' || "House_number" || ', ' || "Street_1" || ', ' || "Town" AS "Address", FROM "Property_tbl"

will replace House_Name if NULL with a empty string. How you wish to format the remainder is at your discretion.

[chemistry337]

Thanks for your response Ratslinger. I apologise for my late reply, i’ve been away.
I’ll re-think my question and re-submit. I’ll try the COALESCE function for the null value.
How do I know what data base I’m using?
Thanks again.
Chem

[Ratslinger]

On the main Base screen in lower left corner (status bar) is the connection type to the database. To the right of that is the database being used.

[chemistry337]

Thanks Ratslinger! the COALESCE function worked a treat!