New to Base. What can I do!!? What can't I do?

Discuss the database features
Locked
ryetee
Posts: 5
Joined: Fri Apr 23, 2021 11:32 am

New to Base. What can I do!!? What can't I do?

Post by ryetee »

Hi all. Bit of a preamble first before getting to the real questions.
I've been in IT a long time back to the dark ages where we had to input our programs on punched cards. I've been a trainee programmer up to senior manager.
After retiring I decided to get back into something technical and started writing small Access systems. I'm no expert but know my way round.
A client of mine referred me to someone that wants a relatively small piece of work doing. Trouble is she has a Mac and I have a Windows 10 laptop.
After exploring all the possibilities I decided that I could develop this in Base and hand it over and all would be well.
So here are the questions
1. Is Base full portable. i.e. can I develop on my Windows laptop and give her the odb and expect there to be no problems?
2. I've been told I can't add code. The system , to begin with, is going to be fairly straight forward but even then I think I'm going to need some extra logic thrown in there. Is this possible.
3. Any pitfalls using base?
4. I'm following the getting started pdf to set up forms/queries/tables/reports and have had a few 'funnies'. I get the funnies in Access as well but can usually fix in design mode whereas I'm having to delete the object and start again in OO. Maybe it's because I'm new and don't know how to go about it. For example a report where a date was displayed was coming out as a number. When I redid the report in exactly the same way it worked!
5. I want to make the end product idiot proof. For example in Access I can make the tables etc inaccessible to the user so they can't add amend delete outside of using a form. I can hide things on the forms if necessary such as record keys (next record, last record, end, beginning new record) and control that myself. Can I do all of this and more?

I'm sure any responses will provoke more question but until then that's it for the time being!
Apache OpenOffice 4.1.5
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: New to Base. What can I do!!? What can't I do?

Post by UnklDonald418 »

If you are just starting out then I recommend Libre Office https://www.libreoffice.org/download/download/
Currently, it is much the same as OpenOffice, but bug fixes and development is far more active.
If you decide to continue using OpenOffice Base, then install the ORB extension (Not necessary in LO because that is the the default report writer)
https://extensions.openoffice.org/en/pr ... rt-builder
what little documentation there is, outside the Help system, can be found at
https://wiki.openoffice.org/wiki/SUN_Re ... umentation
1. Is Base full portable. i.e. can I develop on my Windows laptop and give her the odb and expect there to be no problems?

Yes, the the .odb files are usually portable, BUT the Embedded format can be unstable, especially it seems for laptop users. Frequent/regular backups are highly recommended. An .odb file is actually a Zip archive, and if anything goes wrong, the .odb file becomes corrupt and the data can disappear.
A solution to that issue is [Wizard] Create a new 'split' HSQL 2.x database
2. I've been told I can't add code. The system , to begin with, is going to be fairly straight forward but even then I think I'm going to need some extra logic thrown in there. Is this possible.
It is best to learn to use the power of Base and SQL before delving into macro programming. The API is quite complex. There is a book on macro programming in OO that can be downloaded from http://www.pitonyak.org/oo.php . Unfortunately, he doesn't cover the Base module.
The book "Database Programming with OpenOffice.org Base & Basic" by Roberto Benitez is only available in print.
If you do find the need to write macro code then install and learn to use the
MRI extension
it allows you to see and navigate through the API.

4. I'm following the getting started pdf to set up forms/queries/tables/reports and have had a few 'funnies'. I get the funnies in Access as well but can usually fix in design mode whereas I'm having to delete the object and start again in OO. Maybe it's because I'm new and don't know how to go about it. For example a report where a date was displayed was coming out as a number. When I redid the report in exactly the same way it worked!
Dates are stored as a numerical value, so changing the formatting of the control will usually resolve that issue.
5. I want to make the end product idiot proof. For example in Access I can make the tables etc inaccessible to the user so they can't add amend delete outside of using a form. I can hide things on the forms if necessary such as record keys (next record, last record, end, beginning new record) and control that myself. Can I do all of this and more?
That is covered by this tutorial Standalone Forms
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
ryetee
Posts: 5
Joined: Fri Apr 23, 2021 11:32 am

Re: New to Base. What can I do!!? What can't I do?

Post by ryetee »

Thanks for this. I've not fully digested but in the meantime...
Is it easy without code then to navigate round forms or for example have a form that merely wants the user to enter a customer name and the id of the customer is then used to create a report. I know how to do this in access which is a piece of proverbial but not quite sure how to do in Base. I presume it's event drivern?>
Apache OpenOffice 4.1.5
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: New to Base. What can I do!!? What can't I do?

Post by Villeroy »

Yes, it's possible to create user friendly forms but not easy to create. Base is just a tiny addition to this office suite. There is a form based filter tool built-in which is a bit clumsy to use. On this forum you find dozends of examples for "power filtering" which offers some creative solutions based on subforms and filter criteria stored in a dedicated table.
If you already have some database up and running, Base is a great tool to feed office documents with data. It is not a great development tool. You must not compare it with MS Access which is a multi billion product since 30 years.
You must not use the embedded HSQLDB except for education, testing and sharing. It is fairly easy to convert it into a stand-alone HSQLDB or use something like MySQL.
You must not rely on the wizards. Except for the report wizard, they are almost useless.
Macro programming for Base is a nightmare.
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
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: New to Base. What can I do!!? What can't I do?

Post by UnklDonald418 »

It sounds like you are needing a listbox, which allows the user to pick a name from a list and the primary key for the selected name is stored or passed on to a subForm.
Near the top of the main Base Forum page are some sub-Forums including Base Tutorials. You might find some useful information there. That Forum also has a sub-Forum Database Examples. Here are links to 3 that you might find helpful.
[Example] Relations reflected by list boxes in forms
[Example] Invoice Forms (without macros)
This link has several examples, some making use of macros to simplify using the form document.
[Example #1] Filter/Search with Forms (leveraging SubForms)

Be aware that unfortunately the word Form has multiple meanings in Base. Along the left of the main Base window is a category Forms. When you select that it will display a list of Forms which are actually form documents, Writer pages (the OO/LO word processor) containing Forms to access the data stored in the underlying HSQL database engine and Controls to display and collect that data.
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
ryetee
Posts: 5
Joined: Fri Apr 23, 2021 11:32 am

Re: New to Base. What can I do!!? What can't I do?

Post by ryetee »

Villeroy - so it sounds like I shouldn't rely on Base at all. Do you know if there is anything else out there I could use that can be developed on a Windows platform and deployed to Mac and preferably free!

UnkDonald - thanks I'll take a look. and let you know how I get on. I fear though that things that were easy to resolve in Access are going to be a bit of a pain with Base.
Apache OpenOffice 4.1.5
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: New to Base. What can I do!!? What can't I do?

Post by Villeroy »

I use it since 10 years in various ways and with zero problems. Most of my office documents rely on dBase data, HSQL or csv via HSQL. If you know nothing about normalization and relations, if you can't handle SQL commands, you have to use MS Access exclusively.
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
ryetee
Posts: 5
Joined: Fri Apr 23, 2021 11:32 am

Re: New to Base. What can I do!!? What can't I do?

Post by ryetee »

Yes I know all about normalisation
I've not had to do a great deal of SQL in my life but I've been doing IT in one guise or another for a long time so can work it through.
I've had a look at the links and started to get my head around things and have put together a few things together to work out how it all hangs together. It doesn't seem to bad at all.
I'm stuck om how to get to get a price off a product table. I'm using a list box to pick the product itself with some SQL but not sure how to drop the price in which can then be amended. BUt I'll figure it out and if not I'll be back here!
Apache OpenOffice 4.1.5
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: New to Base. What can I do!!? What can't I do?

Post by Villeroy »

All relations between an article and its price(s), vendor(s), invoices and what not are accessible through forms and subforms with listboxes. There is nothing but forms, subforms and listboxes. A subform shows a subset of records depending on the parent form's current record based on common fields or named parameters.
A table control bundles one or more bound form controls in order to show more than one record in a grid.
All the other form controls are about proper representation of dates, times, currencies etc. A combo box is just a text box with auto-complete. All this is very simple which makes it difficult to get something meaningfull out of these chess pieces.

Short intro [Example] Relations reflected by list boxes in forms with one-to-many and many-to-many forms.
Base turorials
More database examples
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
ryetee
Posts: 5
Joined: Fri Apr 23, 2021 11:32 am

Re: New to Base. What can I do!!? What can't I do?

Post by ryetee »

Thanks Villeroy. I've not been able to do much this past week but will now plough through the links. I Understand the form and sub form and have just put together a simple Customer and Invoice form. I can get the product into the sub form using a list box to get the product id for the invoice details lines but can not work out how to display the price and description. Ultimately I'd like to 'copy' the price across and allow it to be amended for 'special' customers but don't know if that's possible. I suppose I can get round that by having a discount %age or a price off field.
But I need to take my first step and work out how I can display the product price.
Apache OpenOffice 4.1.5
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: New to Base. What can I do!!? What can't I do?

Post by Villeroy »

You find invoices in the subforum for Base examples.
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
Locked