[bug?] Editing/adjusting Calc's default data entry form

Discuss the spreadsheet application
Post Reply
ind
Posts: 12
Joined: Tue Jun 19, 2018 2:49 pm

[bug?] Editing/adjusting Calc's default data entry form

Post by ind »

Hi!

I would like to do data entry on Calc sheet using the default form input. However, I have 23 columns, so the automatic form (menu Data->"Form...") extends beyond the bottom of the screen and doesn't scroll, which makes it impossible. I could recreate this form myself, but ideally I would just want to edit it to have columns, or else be able to reduce the width of some of the input areas, etc.

Is there any way to edit Calc's automatic form?
Last edited by ind on Wed Apr 03, 2019 2:51 pm, edited 1 time in total.
LibreOffice on Ubuntu (latest stable versions for both).
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Editing/adjusting Calc's default data entry form

Post by Villeroy »

Calc is not a database.
Calc is a calculator.
This is a database form: download/file.php?id=35183
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
ind
Posts: 12
Joined: Tue Jun 19, 2018 2:49 pm

Re: Editing/adjusting Calc's default data entry form

Post by ind »

I'm looking to change the layout of the default Calc form found in menu item Data->Form...
menu item
menu item
which shows this default form layout:
data form
data form
but when there are so many columns, the form does not scroll them into view, which makes editing an viewing impossible:
big form
big form
Is there any way to change this default form?
LibreOffice on Ubuntu (latest stable versions for both).
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by Villeroy »

Forget all that bullshit about databases on calculator sheets. You need something better.
Create a new directory for a dBase database.
Save your spreadsheet in that directory as file type "dBase (*.dbf). Only the active sheet will be saved.
--------------------------------
File>New>Database...
[X] Connect to existing database
Type: dBase
Specify your dBase directory containing the dbf file.
[X] Register the database (make data availlable for text documents and sheets)
Save the database.
Use the form wizard to embed a form document to the database. A database form is attached to a Writer document and the Writer document is embedded in a Base document.
-------------------------------
My demo form download/file.php?id=35183 is a database form attached to a stand-alone Writer document. I created it manually with toolbars "Form Design" and "Form Controls". The underlying database of my example form is the "Bibliography" database which is shipped with OpenOffice and LibreOffice. That one is also a dbf file in a dBase directory.
 Edit: Improved form: download/file.php?id=37979 
Last edited by Villeroy on Thu Apr 11, 2019 12:37 pm, edited 1 time in total.
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
ind
Posts: 12
Joined: Tue Jun 19, 2018 2:49 pm

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by ind »

I do not wish to debate the value of using Calc vs. Base, and I do not want to find other solutions.

I am looking for a solution to the problem I mentioned.
LibreOffice on Ubuntu (latest stable versions for both).
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by Villeroy »

I do not discuss Calc vs. Base. I tell you how to create a most simple database for your most simple (but large) list.

Get the source code of that Calc tool and edit it if you can.
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
ind
Posts: 12
Joined: Tue Jun 19, 2018 2:49 pm

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by ind »

How do I find the Calc source code for the form? And how do I edit it and incorporate it into a live version of Calc?

I don't mean, of course, where can I find the source code in general, but where is the code for the specific form itself. (I am having a trouble navigating this unfamiliar code base.)
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by Villeroy »

Get in touch with the LibreOffice developers mailing list.
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
User avatar
RoryOF
Moderator
Posts: 34570
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by RoryOF »

/Data /Form is a LibreOffice additrion to the code, so Villeroy's advice is probably the best we can offer.

In /Tools /Macros /Organise Macros /Basic under OpenOffice (or LibreOffice) Macros, you will find FormWizard, which is a set of macros for generating forms. Use of these might be a possible solution, but be aware: Macro writing in OO or LO is not a trivial undertaking; it may be simpler to adapt your requirements to the facilities you already have.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
ind
Posts: 12
Joined: Tue Jun 19, 2018 2:49 pm

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by ind »

Thanks, RoryOF. However, I am trying to fix the default solution for data entry, and I will not be able to debug a programmed solution in the future. Not only columns, but data types will be deleted/added in the future. Plus I don't have time to do the programming.

Is it even possible to change the default form layout?
User avatar
RoryOF
Moderator
Posts: 34570
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by RoryOF »

I don't know. If I needed to do this, I would look to see if there was a configuration file that might be edited, but I don't use LibreOffice or that form, so cannot help further.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by Villeroy »

RoryOF wrote:/Data /Form is a LibreOffice additrion to the code, so Villeroy's advice is probably the best we can offer.

In /Tools /Macros /Organise Macros /Basic under OpenOffice (or LibreOffice) Macros, you will find FormWizard, which is a set of macros for generating forms. Use of these might be a possible solution, but be aware: Macro writing in OO or LO is not a trivial undertaking; it may be simpler to adapt your requirements to the facilities you already have.
These macros are the source code of the form wizard in Base. The code asks for record sets, fields, relations and layout of the resulting form/subform. There is no record set, not field, no relaton in Calc and the data aware form controls don't apply to StarBasic dialogs. Improving the spreadsheet dialog (which is a rather dysfunctional anti-feature) may take weeks of free time for someone who is unfamiliar with the code base. Switching the tool set is a matter of minutes.
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
ind
Posts: 12
Joined: Tue Jun 19, 2018 2:49 pm

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by ind »

Villeroy wrote:These macros are the source code of the form wizard in Base. The code asks for record sets, fields, relations and layout of the resulting form/subform.
Do you have a URL to the specific file where Calc uses this for the default form?
ind
Posts: 12
Joined: Tue Jun 19, 2018 2:49 pm

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by ind »

Villeroy, just because Base is a better way to store data does not remove the practicality of Calc. Maybe it will help you to understand if I explain my current project goals:

1. A Base database will not be maintainable by the data owner, who is not database literate.
2. This data will be used by people who only know spreadsheets.
3. I will no longer have access to the spreadsheet once the project is done.
4. Any other spreadsheet manager is not expected to know database design.
5. Education about database design is not part of this project.
6. I have no budget for developing a specific form for this data.
7. This type of "spreadsheet only, no programming" project happens _frequently_ for me.

Base cannot meet this very common use case.

The solution is fixing the default form, which has been a bug for years. Very simply, another scrollbar is needed on the default form (ideally, the default layout should be accessible for change, and that layout should be attached to the Calc document). Though adding a scrollbar may be a longer task, solving that bug would allow people to use free, open-source solutions instead of closed-source or paid MS Excel or Google Sheets.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by Villeroy »

It is exactly the other way round. Databases are easy to use once they are set up properly. They are like apps for one distinct purpose. The end users sees input forms and report and the pain is on the developers side. Nowadays we are using some database whenever we open a web browser This forum is a database too.
The user of a database should be locked out from all maintainance. This is not possible with the set up I have suggested, but at least you could store the database in a hidden place (e.g. office profile folder) and put the input forms and linked spreadsheets on the desktop. The frontend documents can be read-only while their attached form controls writing to the dBase tables remain usable (just like a web form).
Editing spreadsheets properly is extremely difficult and that form dialog does not help by any means. Did you test the functionality of that darn thing with a smaller data set and some different data types?
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
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by Villeroy »

The attachment looks more like the Calc form. The linked "Bibliography" database consists of text only but it is easily possible to create calendar controls, time controls, numeric controls, check boxes, option buttons, combo buttons, list boxes and more.
The document which carries the form is write protected. The form edits the underlying dBase table <user_profile>/database/biblio/biblio.dbf The connection is provided by the database document <user_profile>/database/biblio/Bibliography.odb
There are push buttons like the ones on the Calc form. The last one shows the form in an additional grid view.
There is a navigation bar attached to the form.
There is a floating navigation toolbar.
Text input can be multi-line or not.
The labels are editable freely.

The user does not care about save dialogs nor file formats since the records are stored instantly when moving to another record. The document is read-only. You only get a save warning when you close the window while the current record is not saved.
The user edits input boxes with predefined lengths and data types (not implemented in this quick demo).
The user is limited to the tools on the navigation toolbar and the context menues of the form controls.

YOU take care of the data types in the dBase table. If we are talking about many thousands of rows: dBase supports indexing. There are hundreds of alternative applications that can edit dBase.
YOU take care of the input form.
You install it on the user's machine. I can provide a macro which does this automatically.
Attachments
biblio_form(2).odt
(12.61 KiB) Downloaded 129 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
ind
Posts: 12
Joined: Tue Jun 19, 2018 2:49 pm

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by ind »

Villeroy, thank you for your input. The default Calc input form needs fixing and has for years, so I want to find that solution, not something else.
Villeroy wrote:Databases are easy to use once they are set up properly.
Of course. But I am not getting paid to do that, so there will always be a business case for spreadsheets.
Villeroy wrote:The user of a database should be locked out from all maintainance.
Of course. But the owner should not, since they may use any other developer. Spreadsheet developers are cheap compared to database developers, so there will always be a business case for spreadsheets.
Villeroy wrote:Editing spreadsheets properly is extremely difficult [...]
Of course. But that doesn't mean it won't be necessary to edit them, so there will always be a business case for spreadsheets.
Villeroy wrote:[...]and that form dialog does not help by any means.
The default for informs the end user what the fields mean. That seemingly small amount of information has a huge impact:
  • over the course of a year
  • for different data entry personnelle
  • in different data collection methods (phone calls, email, in person, etc.)
So there will always be a business case for spreadsheets.
Villeroy wrote:I do not discuss Calc vs. Base.
That seems to be the only thing you do. I have explained my case fairly well, yet you refuse to accept it, continuously ignoring the business case for spreadsheets. I do not believe you can or will help me with this; I do not want alternates to fixing the default Calc input form which for years has needed fixing. I want to find that solution, not something else.

Thank you for your input.
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by Villeroy »

No business case at all.
You are doing someone a favour.
And I do you a favour.
Within 5 minutes the problem could be solved. You can't do that with Calc.
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
ind
Posts: 12
Joined: Tue Jun 19, 2018 2:49 pm

Re: [bug?] Editing/adjusting Calc's default data entry form

Post by ind »

ind wrote:I do not want alternates to fixing the default Calc input form which for years has needed fixing. I want to find that solution, not something else.
Thank you for your input.
Post Reply