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

[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"

[Villeroy]

Code: Select all

SELECT CASEWHEN("bool_field",'Yes','No') AS "Yes/No" FROM "Table"

[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]

[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

[Villeroy]

Alternatively for all 3 states: