[Solved] How to display one or other field(s) in a listbox

Creating and using forms

[Solved] How to display one or other field(s) in a listbox

Postby gkick » Sun Jun 21, 2020 10:32 pm

Hello,

In a form I need to pick a client from a listbox. In the underlying table the cient may be stored in a field called company or in fields such as title,firstname, lastname.
So if the company field is blank the listbox should pick up title, first and last, whereas if there is no entry for first and last display the company instead - sort of a double coalesce ???
How can one do this ?

COALESCE("company",("title"| ' '||"cfname" || ' ' || "clname") FROM "tblClient" )

Above attempt failed miserably.

cheers
Last edited by gkick on Tue Jun 23, 2020 5:20 pm, edited 1 time in total.
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 250
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: How to display one or the other field(s) in a listbox

Postby UnklDonald418 » Tue Jun 23, 2020 4:19 am

Try this. I assumed "ID" was the Primary Key field for "tblClient"
Code: Select all   Expand viewCollapse view
SELECT "A".*
FROM
   (SELECT ("title" || ' ' || "cfname" || ' ' || "clname")"lbList", "ID" FROM "tblClient" WHERE COALESCE("company",' ') = ' '
UNION ALL
    SELECT "company" "lbList", "ID" FROM "tblClient"  WHERE "company" <> '' )"A"
ORDER BY "A"."lbList"

This looks a little odd, but beginning with HSQL 2.4.0, Base queries with UNION statements don't display the results unless you alias them.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1322
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: How to display one or the other field(s) in a listbox

Postby gkick » Tue Jun 23, 2020 7:39 am

@UnclDonald418,
Just brilliant, exactly what was needed ! Thank you. Only a small cosmetic thing, how can I eliminate the blank since I can not set both company and names to not null in the table.
Very much appreciated
Cheers
Attachments
brilliant.png
brilliant.png (7.57 KiB) Viewed 407 times
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 250
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved]How to display one or other field(s) in a listbo

Postby UnklDonald418 » Tue Jun 23, 2020 6:21 pm

Check you data, the only way I have found to duplicate that issue is by entering a record in tblClient where both those fields are Null.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.6 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1322
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] How to display one or other field(s) in a listb

Postby gkick » Wed Jun 24, 2020 1:37 am

Hmm, interesting, the data is clean, the query return the right results, only the listbox for some reason puts in a blank

no prob, just cosmetics, thanks
Attachments
dataclean.PNG
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 250
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: [Solved] How to display one or other field(s) in a listb

Postby Villeroy » Wed Jun 24, 2020 9:02 am

This concatenates not null strings with a separator:
Code: Select all   Expand viewCollapse view
COALESCE("VWZ1",'') || COALESCE(CHAR(10)||"VWZ2",'') || COALESCE(CHAR(10)||"VWZ3",'') || COALESCE(CHAR(10)||"VWZ4",'') AS "VWZ"

I copied this from my HSQL2 database. I have 4 textual annotation fields and concatenate the non-blanks into one field with CHAR(10) as separator.
COALESCE( separator || "Field", '' ) returns the empty string '' if "Field" IS NULL because any operation with NULL gives NULL. If all fields are Null, the expression concatenates 4 empty strings.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28431
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] How to display one or other field(s) in a listb

Postby gkick » Wed Jun 24, 2020 12:11 pm

Thanks Villeroy
Libre Office 6.2 on Windows 10 HSQL 2.5 backend
gkick
 
Posts: 250
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile


Return to Forms

Who is online

Users browsing this forum: No registered users and 1 guest