Checkbox three-part question

7 replies [Last post]
Walkinshaw
Offline
Last seen: 5 weeks 3 days ago
Title: ★★★
Joined: 3 Sep 2012
Posts: 34
Requesting help on following; 1. How to “tick” checkbox for all existing records in a database (17,000 plus of them…)? 2. Maybe a Listbox Yes/No is a better choice than Checkbox? 3. With the resulting mix of ticked and unticked records, how to run a query to get only the ticked items? In the checkbox control properties I have set Reference (ON) to Yes and Reference (OFF) to No. Thanks
LO 5.1.2.2 with HSQLDB 2.3.2 Split Database Linux Mint 17.3 KDE on 4GB ASUS M32 i3 Desktop and LO 5.1.2.2 with HSQLDB 2.3.2 Split Database Linux Mint 17.3 KDE on Core Duo 2GHz Toshiba Satellite L355 Laptop
frofa
frofa's picture
Offline
Last seen: 13 hours 17 min ago
Title: ★★★★★
Joined: 24 Feb 2012
Posts: 804
Updating and searching BOOLEAN-type data
Hello Walkinshaw:

1. Assuming the data type for your check-box column/field is YES/NO (BOOLEAN) as shown in the Edit Table list, then you may change ALL existing records to show a TICK by executing SQL code such as….


UPDATE "your_table"
SET "boolean_column" = TRUE;

IMPORTANT: Always keep a valid BACKUP COPY of your database when doing UPDATES in case something goes wrong!

2. For a QUERY to get all ticked items just try…
SELECT * FROM “your_table” WHERE “boolean_column” = TRUE

More info about the quirks of BOOLEAN form operations HERE.

Walkinshaw
Offline
Last seen: 5 weeks 3 days ago
Title: ★★★
Joined: 3 Sep 2012
Posts: 34
Updating and searching BOOLEAN-type data (SOLVED)
Thank you frofa for this support – issue solved. The only tweak I needed to do was to go back into Checkbox Control Properties and remove the YES/NO entries I had made in Reference (ON)/(OFF). After that the two SQL statements worked perfectly. Thank you again.
LO 5.1.2.2 with HSQLDB 2.3.2 Split Database Linux Mint 17.3 KDE on 4GB ASUS M32 i3 Desktop and LO 5.1.2.2 with HSQLDB 2.3.2 Split Database Linux Mint 17.3 KDE on Core Duo 2GHz Toshiba Satellite L355 Laptop
Walkinshaw
Offline
Last seen: 5 weeks 3 days ago
Title: ★★★
Joined: 3 Sep 2012
Posts: 34
Updating and searching BOOLEAN-type data - only 50% solved
Previously, I only verified that SELECT * FROM “table name” WHERE “column Name” = TRUE works. When I try to use SELECT * FROM “table name” WHERE “column Name” = FALSE I get zero results. In Control Properties of Checkbox I verified that I have Tristate set to No. Any suggestions would be appreciated!
LO 5.1.2.2 with HSQLDB 2.3.2 Split Database Linux Mint 17.3 KDE on 4GB ASUS M32 i3 Desktop and LO 5.1.2.2 with HSQLDB 2.3.2 Split Database Linux Mint 17.3 KDE on Core Duo 2GHz Toshiba Satellite L355 Laptop
frofa
frofa's picture
Offline
Last seen: 13 hours 17 min ago
Title: ★★★★★
Joined: 24 Feb 2012
Posts: 804
Does your table contain NULLS instead of FALSES?
Hello Walkinshaw:

I’m wondering if the rows you have set as FALSE have actually been set to NULL (in the table). If you are trying to FILTER for FALSE values though the FORM, you might be getting zero results because there are no false values set in the underlying table?

The FORM tri-state property I think only affects data entered though the form, but if the table already has NULL values setting the FORM’s tri-state property to ‘no’ I imagine would prevent NULL user input (someone correct me if I’m wrong here). Try inspecting the table listing directly, to check if NULL values have actually been set instead of FALSE values. It seems NULL values show up as a ‘-’ and FALSE values show up as a BLANK in the table listing – see attached screenshot.

Let us know what you discover.

AttachmentSize
boolean_tri-state_values.png 9.98 KB
Walkinshaw
Offline
Last seen: 5 weeks 3 days ago
Title: ★★★
Joined: 3 Sep 2012
Posts: 34
Checkbox column in Table contains three choices, not two
Thanks for suggestion frofa but this has got me more confused. Each record in the Checkbox column actually has three choices Solid tick shows up as a ticked record in form and is included in a = TRUE query. Dashed tick is unticked in form and has no entry in = FALSE query. Blank shows as unticked in form and is included in = FALSE query. I found this thread http://en.libreofficeforum.org/node/8278 but cannot execute the last suggestion ALTER TABLE “Table” ADD “Checkbox” BOOLEAN FALSE NOT NULL
LO 5.1.2.2 with HSQLDB 2.3.2 Split Database Linux Mint 17.3 KDE on 4GB ASUS M32 i3 Desktop and LO 5.1.2.2 with HSQLDB 2.3.2 Split Database Linux Mint 17.3 KDE on Core Duo 2GHz Toshiba Satellite L355 Laptop
frofa
frofa's picture
Offline
Last seen: 13 hours 17 min ago
Title: ★★★★★
Joined: 24 Feb 2012
Posts: 804
Boolean-type column - tri-state issues
Hello Walkinshaw:

Each record in the Checkbox column actually has three choices Solid tick shows up as a ticked record in form and is included in a = TRUE query. Dashed tick is unticked in form and has no entry in = FALSE query. Blank shows as unticked in form and is included in = FALSE query.

What happens when you set the FORM’s tri-state property to ‘yes’ – does it then show the false and null states the same as in the table listing (i.e. tick, dash and unticked)?

…but cannot execute the last suggestion ALTER TABLE “Table” ADD “Checkbox” BOOLEAN FALSE NOT NULL

Your code contains an error. The code below does work on my setup, and it creates a new column which DEFAULTS to FALSE (unticked) and CANNOT be set to NULL (dashed).


ALTER TABLE "your_table" ADD COLUMN "your_checkbox" BOOLEAN DEFAULT FALSE NOT NULL;

Walkinshaw
Offline
Last seen: 5 weeks 3 days ago
Title: ★★★
Joined: 3 Sep 2012
Posts: 34
Boolean-type column - tri-state issues (SOLVED)
Your patient support is much appreciated frofa.

Yes, with tristate form property set to yes, I do have three choices.

With the correct code
ALTER TABLE "your_table" ADD COLUMN "your_checkbox" BOOLEAN DEFAULT FALSE NOT NULL;
it does populate the table Checkbox column with default FALSE and no NULL selection possible.

The only problem I had was that the Checkbox did not show up in the Table edit so I could not add it to the form. It turns out that you need to close/reopen Office to populate the Table Edit and then all is well.
My two Queries – all true records and all false records now are working – and new records are false.
Just the way I want it!
Thank you again.

LO 5.1.2.2 with HSQLDB 2.3.2 Split Database Linux Mint 17.3 KDE on 4GB ASUS M32 i3 Desktop and LO 5.1.2.2 with HSQLDB 2.3.2 Split Database Linux Mint 17.3 KDE on Core Duo 2GHz Toshiba Satellite L355 Laptop

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.