Trying to disable form in Base from automatically entering a new record when form loses focus

[notorious.dds]

Hi All,

I’ve been working on a form in my LibreOffice Base (version 6.1.5.2) running on Debian 10. My .odb file is connected to a MySQL server where my database has 3 tables: Students, Patrons, and Donations.

TL;DR version:
Is there a way disable the “autosave new record” when a form loses it’s focus?

Full Version of question:
The Patrons table has a foreign key contraint from the Students table
The Donations table has a foreign key contraint from the Students table and one from the Patrons table.

The .ODF form I’m working on has 3 actual forms all connected at the top level (as seen in “Form Navigator”) . In other words, none of the 3 forms in my .ODF form are subforms of the other 2.

The reason for this is that I want this .ODF form to be used for entering “New Data Only”. However, new records going into the “Donations” table need to be linked to records in the Patrons (and sometimes) Students tables. If I make the Donations table a subform of Patrons (or Students) in a form intended for new data only, I have to create a new Patron (or Student) even if one already exists.

Having all 3 forms on the same level (i.e. no sub-forms) of the .ODF allows the user to enter new Donations and add new Patrons or Students WHEN NECESSARY without opening a separate form.

This seems to be working all well and good… except for when data is placed into the Donations form and the focus is moved to another form to add a new student or patron before the Donation entry is completed. Whenever the focus leaves the Donations form and goes to one of the other 2, LibreOffice Base saves (or tries to save) whatever info was placed into the Donations form as a new record. Making matters worse, the only way that Base will allow the focus to change forms is by saving new donation record in to the database (incomplete as it may be) , or by clearing the partially complete Donations form (so that the controls set to “input required” stop complaining).

So the question is: Is there a way disable the “autosave new record” when a form loses it’s focus?

[notorious.dds]

After some some significant back and forth with Ratslinger (Thanks again BTW), I felt that I should just post a summary of our discussion as an answer. So, here it is:

The behavior that was labeled “autosave new record” in the question is not something that can be modified or disabled. To the best of my understanding, this has to do with making sure that subforms get correct data from the main form. (If this could be disabled, using a sub form would first require that you manually save any changes to the main form before moving on to the sub form… which would be very problematic.)

In my case, I had three main forms and NO subforms. Thus, the “autosave new record” behavior wasn’t necessary like it would be if I was using subforms. As such, I couldn’t understand why it was saving data when it did. My personal take on this is that although you can have multiple main forms on one .ODF Base form, LibreOffice Base’s forms are primary designed to have one main form and one or more subforms such that each parent form shall have one or fewer child (aka sub) forms. Straying away from this structure isn’t necessarily wrong, but can yield unexpected results.

Further, there is a bug in LibreOffice which was first reported in 2014 and still exists today (2022) in many of the LibreOffice packages for many (most ?) of the popular operating systems. Essentially, this bug allows saving new records to your database even when controls in your form are set to “Input Required = yes” and yet no data exists in the control.

Although this bug isn’t directly associated with the original question, it will exacerbate the “autosave new record” behavior by saving partially complete records into your database.

[Ratslinger]

Hello @notorious.dds and welcome.

There is no “autosave new record” but simply saving the data for new or modified records. This is protection from closing the form without saving an existing entry.

@notorious.dds

If I make the Donations table a subform of Patrons (or Students) in a form intended for new data only, I have to create a new Patron (or Student) even if one already exists.

As I just tested, you may be setting something incorrectly. It is not the Form set as New only but rather the Sub Form.

So in your application you have:

Patrons > Donations (Form >Sub Form)
Students > Donations (Form >Sub Form)

[notorious.dds]

Thanks Ratslinger!

I’m assuming from your response that even though there exists no “autosave new record” behavior, the behavior of “simply saving the data for new or modified records” can be appropriately considered as a feature that cannot be disabled (short of a source code edit).

Please advise if my assumption is incorrect.

Regarding the second part of your response, yes, you are correct in that I can have a subform that is new data only from a main form that is without this limitation.

The issue is that my use case does not lend itself well to this type of form organization. Given my foreign key constraints, I could organize them using (Main Form > Sub Form 1 > Sub Form 1.1 >) such that (Students > Patrons > Donations).

However:

1. Most of the Patron records (hundreds) DO NOT have a value in the column linked with Students
2. Most of the Donation records (thousands) DO have a value in the column linked with Students

This is the primary issue with trying to keep things all on a “single form”.

The secondary issue with this organization is that there are only a handful of active students and patrons. My current organization allows me to easily filter out the those that are active using SQL commands in a list box (see image). I believe this filtering is also possible via the form controls, but it’s wonkier (in my opinion).

To make a long story short, if I could simply disable the “simply saving the data for new or modified records” behavior, I’d have what I desire.

As an aside, when encoutering the “simply saving the data for new or modified records” behavior on Debian 10 running LO 6.x, I’ll get errors because of form controls that require input where no data exists. This is expected. It is also preferable over what happens on Linux Mint 20. The errors on Debian 10 allow me to clear the erroring form before moving to a different form. On Linux Mint running LO 6.x or 7.x, I don’t get any errors. The form simply freezes and my “clear form” button gets greyed out. I can reset it by clicking my refresh button, but the partially entered garbage data gets saved to the DB anyway. I’m pretty sure this last thing should be considered a bug.

Again, if I could disable the “simply saving the data for new or modified records” behavior, this would be preferable.

[Ratslinger]

Hello,
Your Linux Mint issue seems like a different problem. May try later. Have been using Ubuntu 20.4 for my testing here which is quite similar to Mint…
.
As for Disabling the saving, that is simply undoing/clearing the entry (don’t know of another method) - a click on the navigation bar or a macro to wipe it out but that also can lead to loss of an entry when inadvertently closing the form or even a wrong click on the form.
.
My answer indicated two sub forms of Donations one attached to each form to eliminate the problem you have with the two links - sub form cannot be linked back to more than one form.

[notorious.dds]

Thanks again!

[notorious.dds]

Ratslinger,

If it’s of any value, this form freezing business which is occuring on Linux Mint also happens on Windows 10 as well. (Using OpenOffice 4.0 anyway).

[Ratslinger]

Just tried Mint 20.02 with LO 6.4.7.2 (distro version) and there was no lock up. Just the error message. Could be a User Profile issue for you - see LibreOffice User Profile
.
Do not have OpenOffice on Windows but suspect it is not a problem there either.

[notorious.dds]

Okay… I just uninstalled OO and installed LO 7.2.7.2 on my Windows 10 machine.

The locking up of the form is still occuring. Is it possible that this issue is limited to switching between forms that are all top level forms of a single .ODF file? In other words, is that what you tried when you did not have the issue I’m having?

FWIW, the only way out of the lock that I’ve found is either using my refresh button (in image posted earlier) or my submit button. Both have the same result in that the form is cleared and insuffient data is saved to DB.

[Ratslinger]

Using:

Version: 7.2.5.2 (x64) / LibreOffice Community
Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5
CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: default; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

there was no lock, just an error message:

.
Used two main forms (even though one is titled sub form):

.
Same in Mint test.

[notorious.dds]

Weird. You mentioned earlier that it could be a User Profile issue. Is that something you think could survive an uninstall of OpenOffice and then a reinstall with LibreOffice… exhibiting itself on multiple machines and different OS/APP combinations?

Ugg… At any rate, now this issue is just a personal annoyance for me.

Thank you for your insight and testing of my issue.

[Ratslinger]

OpenOffice and LibreOffice use separate profiles. Re-installing LO does not clear its’ profile (Mint?).
Can you post a redacted Base file having the problem?

[notorious.dds]

Sure thing!

Thanks!

I’ll get on that and hopefully get it posted something before the weekend.

Really appreciate your help. Thanks again.

[notorious.dds]

Hi Ratslinger,

As mentioned in my OP, my .ODB Base file is connecting to a MySQL DB. Did you want me to dump a redacted version of that DB from MySQL server and post it along with the .ODB Base file, or are you really only interested in the .ODB file?

[Ratslinger]

Yes, I do need the table structures and what is causing the issue for you. Don’t have MySQL on Windows but do have on Mint & Ubuntu. Will be able to transfer to another source if needed for the Windows test.
.
BTW - what version of MySQL are you using?

[notorious.dds]

Sorry about the delay. Today’s work load was a little higher than yesterday’s.

At any rate, here you go.

Freezing Form.odb (61.3 KB)
RPBands_schema.odt (6.3 KB)

I had to rename the file extension on RPBands_schema from .sql to .odt in order for the forum to permit me to upload the file. Feel free to rename as needed.

Also…
Server version: 8.0.27 - MySQL Community Server - GPL

Just for clarity, I have 3 forms at the top level of my “Create New Donation” form. The issue occurs when working on either the “Add Donation” or “Add Student or Alumnus” forms (it doesn’t seem to occur with the “Add Patron” form) where data exists and at least of the working form’s controls requiring input contains no data. Upon clicking on one of the other forms, instead of getting the expected “input required” error, the form freezes such that the only way to “unfreeze” it is by either using my “Refresh” button or “Submit” button. Oddly, doing this inserts whatever data is in the form (where control requirements have NOT been met) as a new record.

Thanks again Ratslinger!

[Ratslinger]

Have spent some time on this. There are a number of issues. For one, there are fields on the Patron form which are missing but are required.
.
Another is trying to used what should be a sub form to attach to either of two other tables.
.
The problem you have is the Foreign key fields can be NULL. This allows the entry to be made regardless of a selection.
.
I would certainly go back to:

Patrons > Donations (Form >Sub Form)
Students > Donations (Form >Sub Form)

.
Then you simply select (or a new entry) the donor & enter donation.
.
As for locking up, I did see a hang at times because of difficulty in determining missing field data.

[notorious.dds]

Thanks again for all of your help in troubleshooting this.

I took your advice and went through my DB Structures and found a few things that could be cleaned up on that front. I then tried to simplify things by separating the 3 forms out of the one .ODF file into individual .ODF forms within the .ODB file. I started with the “Add Student” form because it’s the only table one that does not have any foreign key constraints.

When using it on Debian 10 using LO 6.1.5.2, it worked as expected. The “input required” error popped up upon trying to submit when a required field was still empty. HOWEVER… upon opening the file on Linux Mint 20 using LO 6.4.7.2 and attempting the exact same thing, I received NO errors. The form happily wrote the record with insufficient data into the DB.

Here’s that edited .ODB file:
Input NOT Required.odb (13.5 KB)

Also, the only change I made to the Students’ table structure was this:

If it’s of any consequence, I’m opening the .ODB via a samba share when on Linux Mint, but it’s opening from a local drive when on Debian 10.

Thoughts?

[Ratslinger]

Not certain why you posted that new Base file. It only has a form for adding students.
.
Also, the error will remain with the current structure since the foreign key fields allow NULL’s.
.
Don’t know what you have on Debian (and the LO version is old but most likely not the issue) but is appears to possibly be different in some way.
.
I am still at the Form/Sub form answer.
.
Edit:
Quick form using Student (Patron is a duplicate of this):
Freezing FormJDBC.odb (75.1 KB)
Need to add this simple table for Selection:

.
To enter a new student, select blank line in List Box a then button to select student.

[notorious.dds]

@Ratslinger

Not certain why you posted that new Base file. It only has a form for adding students.

It was my attempt to demonstrate that a simple form utilizing only one table and having no foreign keys would allow record creation even when controls set to “input required = yes” were left empty.

Regardless and after some testing, it appears that for whatever reason the “Input Required” property of LO form’s controls is meaningless (in my setup at least). I assume it’s not always meaningless and that maybe it’s just limited to MySQL databases. Although, that is just a guess.

What’s interesting is that the property is NOT meaningless with LibreOffice 6.1.5.2 on Debian 10 (everything else being held constant). However, it was meaningless in every other configuration I tested.

In the end, I was able to get the errors I anticipated which prevented the garbage data from getting saved as a new record. However, the only way I as able to do this was by editing my MySQL schema. For every control in my ODB form I wanted to make required, I had to set the corresponding column in my MySQL database to not accept null values.

Of note, everything is working now using the setup I posted earlier. I still have all 3 forms at the top level (i.e. no sub forms) of my .ODF form and all of my foreign key columns are still set to accept null values.

Thanks again Ratslinger for putting your time into my issue! It’s much appreciated. Although it didn’t appear to be the nulls in my foreign keys, that suggestion ultimately led me to the solution.