[Solved] Getting Form Field Data

[fdanna]

Hello all,

I've created a form with Writer that is a bike rental contract. A section in the form is a table that is supposed to calculate the cost of the rental given inputs of length of rental, etc. The problem that I'm trying to solve is that I don't know how to perform calculations with the form fields. It *seems* like I need to write a macro, but I can't find any example of how I get the data from the field

Am I doing the equivalent of hitting a nail with a sledgehammer by trying to use macros and, if so, what would be a more appropriate method?

There other options I see here are: embedding a table from Sheets OR creating a database and then trying to recreate the form (which is text heavy with lots of formatting) under Base.

I appreciate any feedback or samples.

[MrProgrammer]

Hi, and welcome to the forum.

I've created a form with Writer that is a bike rental contract. A section in the form is a table that is supposed to calculate the cost of the rental given inputs of length of rental, etc. The problem that I'm trying to solve is that I don't know how to perform calculations with the form fields.

You may not need to use any macros. Depending on your data table and on your "form", a simple method might be to use Mail Merge. See Help → Contents → Text Documents → Special Text Elements → Creating a Form Letter. A spreadsheet can function as the "database"; it's easy to create this database interface with Base. File → New → Database → Connect to an existing database → Spreadsheet → Next → {specify spreadsheet file name} → Next → Register the database → Finish.

You will be using three components of OpenOffice:
• Writer, to create the form, with fields to hold the data. Normally this Writer document will be a template.
• Calc, to hold the data for the fields and to perform any calculations that you need, say Sales Tax.
• Base, to connect Writer's fields to the data in Calc, and, possibly, to create a query which selects data for the form.

Read about using Mail Merge in the Help, in this forum, or in the Wiki. There are hundreds of topics about mail merge, and some are no doubt similar to what you want to do. There are many topics about using a spreadsheet as a database. Note that Base is not a database, and through you wil be creating a Base document, it holds no data. That document just tells OpenOffice how to access the your "database" in Calc. The Calc document acts enough like a database that Base can use it. But Calc isn't a real database. For example, it's read-only in Base; you have to update it with Calc.

You could create a real database with HSQLDB or with MySQL or with SQLite, etc. and use Base to connect it to OpenOffice. Then you would not use Calc. Instead you would store the data in your real database and use a query or view with SQL to make any calculations. That is done either with standard database tools, or by creating forms in Base. Learning about and creating a real database has advantages but will require more study. Depending on your operating system, you may need to install this database software. If you need assistance connecting OpenOffice with a real database, open a new topic for that in the Base forum after reviewing tutorials there.

[fdanna]

Thanks for taking the time to give such a comprehensive reply! The only thing I don't get is the statement, "Note that Base is not a database." I mean, the menu says File | New Database and when you do that it says Embedded Database and HSQL Database Engine at the bottom. What's it for?

[Villeroy]

Just like MS Access, Base is a tool to work with databases. A database document can be connected to any database you have driver for (MySQL, PostrgreSQL, Oracle etc). It may also be connected to some pseudo-databases such as text files or spreadsheets. In either case, all your data are not saved within the database document. The document includes the connection parameters to access the database structure of tables, indices, relations. The document contains queries, forms and reports to work with the connected database. Writer documents and spreadsheets can be filled with database database data in the same ways regardless of origin.
The idea is that you already have some kind of database to be reused in the context of this office suite.
----------------
There is one special case when Base creates a new database from scratch. In this particular case a HyperSQL database will be wrapped into the Base document. Every time you start editing that database, it will be "installed" to a temporary directory and connected just like an external database. Every time when you stop editing the database, it will be rewrapped into the document.
---------------
You mentioned a spreadsheet. If the relevant list in the spreadsheet can be interpreted as a database table, a rectangle of adjacent data cells having one row of column headers, each row below headers representing one record, then connect a Base document to the spreadsheet document:
File>New>Database...
[X] Connect to existing database
[X] Register the new database (make it availlable for mail merge)
Specify the spreadsheet document
Save the database.
The used areas of each sheet represent one table.
Each explicitly defined database represents one table.
Base can hide away tables that do not match a table structure (Tools>Table Filter...)
The database with tables and queries will appear in Writer's data source window from where you can drag column headers into the document. They will appear as database fields (placeholders).