Base form: how to make a check box with the default state 'not defined'

[impocta]

I try to make forms with tristate check boxes that have a ‘not defined’ value as their default state. I want this because I want to force the users of the form to click every check box at least once. This way I know that they entered the information knowingly and don’t just pass the check box values without thought.
However as soon as I set NOT NULL in the table properties for the relevant collumns, the check boxes suddenly behave with 0 as their default value in the form, even though the check box control settings of the form are unchanged.

It looks like the default value from the table properties is forced over the check box control settings of the form. The check box only shows a default ‘not defined’ state if the collumn has a ‘not defined’ default state defined in the table properties and it proves to be inpossible to enter a ‘not defined’ default state in the table properties as long as a NOT NULL attribute is set to that collumn. I tried this (setting NOT NULL with default NULL) through the table editor gui and through sql (with the help of mysql workbench).

Am I facing a bug here? Or is there another way to get a working default ‘not defined’ check box on a collumn with NOT NULL attribute?

I am using LO Base 6.0.3.2 Build ID: 1:6.0.3-0ubuntu1 connected to an online MySQL database through driver: mysql-connector-java-5.1.45 on Xubuntu 18.04

[Ratslinger]

Hello,

You are wanting contradictory things to occur. You want the table to not contain NULL values but you are trying to set the form so the default is NULL. The Not defined value of a checkbox (in LO documentation) is:

Not defined corresponds to a NULL
entry in the table underlying the
form.

Edit 2018-08-28:

The above is questionable. Have done much more testing and here is what I have found thus far.

Tested on Mint 18.3 with LO v6.1.0.3 (from TDF) and MySQL 5.7

Using SBDC Native connector there is a problem as the controls seem to always default to a value of 0 or 1 for check box, option buttons or numeric field when the table field is a required entry.

By co-incidence I tried in HSQL embedded & had no problem as the controls were left blank or for the check box Not Defined but if not changed an error resulted when updating the record - Field required.

Back to MySQL. This time tried with JDBC. All worked same as HSQLDB. Did this with various JDBC connectors & all worked (except MySQL(JDBC) which is supplied with LO). Changed LO to v 6.0.2.1 and still no problem.

So now I am currently questioning where your problem lies.

Where did you obtain your version of LO from? Is it a distro version (Xubuntu release) a PPA version or direct from TDF (The Document Foundation)? Also MySQL here is local but not certain why this may be a problem. What version of MySQL are you accessing?

Edit 2018-08-29:

So now things have become a bit more confusing. Not fully satisfied with the testing thus far, I moved over to my Xubuntu 18.04 system. Installed my LO v6.1.0.3 and tried the JDBC tests again. On this OS it would NOT work as it did on Mint. Only difference I can figure so far is the Kernel - 4.4.x on Xubuntu and 4.15.x on Mint.

[impocta]

I want the field to be blank (NULL) when user to opens the form. However, for the record to be valid I need to have input 0 or 1 in the field. Whith this setup I force the user to think about entering a 0 or a 1. It will be impossible to save a record without thinking/deciding about the right content of the field.

I think I can probably make it work with a listbox yes/no, with 1/0 bound to it. However, I like the appearance of the checkbox better

[Ratslinger]

Please ignore last two deleted comments. More testing needed. Something different between MySQL & HSQLDB.

[impocta]

I use libreoffice 6.0.3.2 from ubuntu software sources and the mysql java connector 5.1.47 downloaded from the mysql website.

[Ratslinger]

Unfortunately, as stated in my latest edit in answer, the only difference thus far was the Kernel version. An update to 4.15.x on Xubuntu failed with an error. Awaiting a method to obtain a Kernel later than the 4.4.x currently installed. This does work as you wanted in Mint 18.3 but that has a newer Kernel (using 4.15.0-33).

[Ratslinger]

Was finally able to use updated Kernel (4.15.0.33.36) in Xubuntu but to no avail. At this point all I can recommend is to file a bug report → Bugzilla.