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"
[Solved] REPORT with field type [YES/NO]boolean
[Solved] REPORT with field type [YES/NO]boolean
Last edited by julian on Wed Apr 30, 2008 8:31 am, edited 1 time in total.
Re: REPORT with field type [YES/NO]boolean
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: REPORT with field type [YES/NO]boolean
I've tryied:
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]
Code: Select all
SELECT "bool_field", CASEWHEN("bool_field",'Yes','No') AS "Yes/No" FROM "Table"
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]
Re: REPORT with field type [YES/NO]boolean
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...
or setting Null=>True
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: REPORT with field type [YES/NO]boolean
Alternatively for all 3 states:
- Attachments
-
- tri_state_bool.png (8.33 KiB) Viewed 13467 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice