[Solved] REPORT with field type [YES/NO]boolean

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
julian
Posts: 19
Joined: Wed Apr 16, 2008 4:24 pm

[Solved] REPORT with field type [YES/NO]boolean

Post by julian »

I have a table with a field type [YES/NO]boolean

when I open the table and create a record I can check the box or not

finally I have a report that show:

fieldName: TRUEif I checked the box or fieldName: FALSE if I didn't

In the report I wanna put a different word instead TRUE or FALSE ( if I checked YES , otherwise NO)

Is it possible?

How? maybe creating a query (the report is based on a query)

Maybe I've to edit the report, select the field, and in properties- data tab - data field type - user defined function

¿where can I create this user defined functions?

thanks

THE SOLUTION is by this query:

SELECT "FIELD_BOOL", CASEWHEN( "FIELD_BOOL" = False, 'NO', 'YES' ) FROM "TABLE_BOOL"
Last edited by julian on Wed Apr 30, 2008 8:31 am, edited 1 time in total.
User avatar
Villeroy
Volunteer
Posts: 31279
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: REPORT with field type [YES/NO]boolean

Post by Villeroy »

Code: Select all

SELECT CASEWHEN("bool_field",'Yes','No') AS "Yes/No" FROM "Table"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
julian
Posts: 19
Joined: Wed Apr 16, 2008 4:24 pm

Re: REPORT with field type [YES/NO]boolean

Post by julian »

I've tryied:

Code: Select all

SELECT "bool_field", CASEWHEN("bool_field",'Yes','No') AS "Yes/No" FROM "Table"
this is the result:

bool_field |--------| Yes/No
------------------------------------------------
[ ] |----------------| No
[ ] |----------------| No
[v] |----------------| No
-----------------------------------------------

Always return No and the alias is Yes/No
I can't figure out what I 'm doing bad[Table=][/Table]
User avatar
Villeroy
Volunteer
Posts: 31279
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: REPORT with field type [YES/NO]boolean

Post by Villeroy »

Possibly your boolean field allows Null values (neither true nor false, just empty). They look similar as True values, but slighly greyed. The function returns 'Yes' for every True value and false for anything else (False or Null in this case). You can set the default value to True or False which will set the respective value for each omitted value in a newly created record. Additionally or alternatively you may not allow Null values. The latter works only after you updated all existing values to True or False.
You can do this for all Null values at once. Tools>SQL...

Code: Select all

UPDATE "Table" SET "bool_field"=False WHERE "bool_field" Is Null
or setting Null=>True

Code: Select all

UPDATE "Table" SET "bool_field"=True WHERE "bool_field" Is Null
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31279
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: REPORT with field type [YES/NO]boolean

Post by Villeroy »

Alternatively for all 3 states:
Attachments
tri_state_bool.png
tri_state_bool.png (8.33 KiB) Viewed 13453 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply