[Solved] Click between multiple forms while editing a record

Creating and using forms
Post Reply
Durre
Posts: 4
Joined: Mon Dec 26, 2016 1:47 pm

[Solved] Click between multiple forms while editing a record

Post by Durre »

Hello hello,

Please could someone give me some advice about the following:

I have 4 forms in a writer document. Each form is used to add a record to a different table of the database bound to the forms:

* Transaction form --> adds record to Transaction table
* Location form --> adds record to Location table
* Entity form --> adds record to Entity table
* Category form --> adds record to Category table

You can see a (partial) screenshot of the writer document below:

Image

As you can see there's an error in the screenshot. This error is produced when I enter values on the transaction form and then click on any of the other forms BEFORE
completing all mandatory transaction fields. As I understand it, the cause for the error is twofold:

1. When I click of the form the user input is automatically sent to the database for insertion
2. The insertion of the input into the transaction database table fails because mandatory information is missing

Can the behaviour in point 1 be changed? Is it possible to prevent the data from being sent/saved to the database when the form loses focus? In other words: how can I click between forms without the input being autosaved? If not possible, what are some workarounds?

A good reason for me to want this kind of behaviour is the following scenario: imagine I am entering data on the transaction form. At some point I want to select a receiver from the drop down list, but realise the receiver does not yet exist in the database. So I need to go ahead and add a new entity to the database. Currently this is not possible for me unless I either ...

a) complete all mandatory fields including the receiver field (e.g. use a dummy value which I then need to change later) or ...
b) discard all data and start over.

Any advice is much appreciated,

Mattias

EDIT: reworded some of the sentences to clarify the topic.
Last edited by Durre on Sun Jan 29, 2017 12:00 pm, edited 6 times in total.
LibreOffice 5.2.2.2 | Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Click between multiple forms while editing a record

Post by UnklDonald418 »

I've looked at your question nearly every day since you posted it, but it was only today that it occurred to me that perhaps your problem could be solved using a filter table for data entry on your form. Using a filter table you would be editing an existing record so leaving the form before all the required fields had been entered shouldn't generate an error message. Once you have completed entering data on the form, the filter table fields could be appended to your transaction table, and then of course the filter table fields would need to be reset.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Durre
Posts: 4
Joined: Mon Dec 26, 2016 1:47 pm

Re: Click between multiple forms while editing a record

Post by Durre »

It seems like there is not much documentation out there about filter tables. I assume it's a table which has only one entry at any given time and which is bound to the input form? Then when I save the user input by clicking a button, the record is moved from the filter table to the actual table containing all transaction records? Are you aware of any good readings that could help me implement this, especially the part about transferring the record?

In any case, thanks for you tip. I am going to get to work with it right away. So far the only thing I was able to come up with is to set table fields to non-mandatory and to cancel row updates each time the user enters info in a form control. Your solutions seems way more elegant and way less troublesome.
LibreOffice 5.2.2.2 | Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Click between multiple forms while editing a record

Post by UnklDonald418 »

I assume it's a table which has only one entry at any given time and which is bound to the input form?
Yes, filter tables contain a single row. The columns would need to be free of any Not Null constraints, and the Primary Key would be non-incrementing. In fact in one example the Primary Key field was of boolean type.
viewtopic.php?p=198364#p198364
Near the end of the text is a paragraph about using a boolean Primary Key. The obd attachment has an example using a simple filter table but for a different purpose.
Are you aware of any good readings that could help me implement this, especially the part about transferring the record?
I not aware of much else on the topic, so you would be in mostly uncharted territory.
Having not tried any of this I can only guess, but maybe a SQL UPDATE command could be used to transfer the data. If you provided more details about the two tables perhaps the SQL guru's who frequent this forum might help with that.
I suspect some dreaded macro coding may be required.
This example might provide some ideas for you
viewtopic.php?f=100&t=56006
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Click between multiple forms while editing a record

Post by UnklDonald418 »

Actually it would probably be a SQL INSERT not UPDATE command.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Durre
Posts: 4
Joined: Mon Dec 26, 2016 1:47 pm

Re: Click between multiple forms while editing a record

Post by Durre »

Yes, filter tables contain a single row. The columns would need to be free of any Not Null constraints, and the Primary Key would be non-incrementing. In fact in one example the Primary Key field was of boolean type.
viewtopic.php?p=198364#p198364

...

I suspect some dreaded macro coding may be required.
This example might provide some ideas for you
viewtopic.php?f=100&t=56006
Thank you for the links, very useful for later down the road as well! Macros aren't really a problem, but I was hoping to avoid involving SQL in them. With SQL I'll have to hard code the table column names. Or I'd have to loop over the names and trust that both the filter table and transaction table have identical column names. It would be so much easier and transparent if there was a method in one of the services to move a record around!

Thanks again for your input, it is much appreciated! Time for me to get it to work! :D
Last edited by Durre on Sun Jan 29, 2017 8:33 pm, edited 2 times in total.
LibreOffice 5.2.2.2 | Windows 10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Click between multiple forms while editing a re

Post by UnklDonald418 »

Macros aren't really a problem
That being the case, in his book “Database Programming with OpenOffice.org Base & Basic” Roberto Benitez provides an example of how to insert a new record into a table using the Row Set Service, no SQL!
As far as I know that book is only available in print format. It is a Lulu publication.
http://www.baseprogramming.com/
He does provide the book source code for many of his examples for download from
http://www.baseprogramming.com/resources.html
In this case Chapter 5 Example 3 shows how to insert a new row into a table using an array as a data source.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply