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

Getting your data onto paper - or the web - Discussing the reports features of Base

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

Postby julian » Tue Apr 29, 2008 4:00 pm

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.
julian
 
Posts: 19
Joined: Wed Apr 16, 2008 4:24 pm

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

Postby Villeroy » Tue Apr 29, 2008 4:07 pm

Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26852
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby julian » Tue Apr 29, 2008 4:38 pm

I've tryied:
Code: Select all   Expand viewCollapse view
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]
julian
 
Posts: 19
Joined: Wed Apr 16, 2008 4:24 pm

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

Postby Villeroy » Tue Apr 29, 2008 5:05 pm

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   Expand viewCollapse view
UPDATE "Table" SET "bool_field"=False WHERE "bool_field" Is Null

or setting Null=>True
Code: Select all   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26852
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Villeroy » Tue Apr 29, 2008 6:10 pm

Alternatively for all 3 states:
Attachments
tri_state_bool.png
tri_state_bool.png (8.33 KiB) Viewed 10916 times
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.x
User avatar
Villeroy
Volunteer
 
Posts: 26852
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Reporting

Who is online

Users browsing this forum: No registered users and 2 guests