Require any one of a number of fields

[Zoidman]

In a database table I have 3 fields: HomePhone, WorkPhone and MobilePhone I want to make a phone number required, but not all three nor any specific one. Is there a way to make it so that as long as any one of the three fields is filled out then the other two are not required. There may of course be other ways of doing the same thing, like having a required PrimaryPhone field and linking it to one of the three fields...

[UnklDonald418]

You are on the right track

a required PrimaryPhone field and linking it to one of the three fields...

but instead of linking to one of the three fields PrimaryPhone would contain a foreign key PhoneNumberID linking it to a PhoneNumber table.
In addition the the column "ID" of type integer set as an auto incrementing primary key, that table would at a minimum have a column PHONENUMBER, and a column TYPE ( HomePhone, WorkPhone and MobilePhone) and a foreign key MAINTABLE_ID linking it back to the original table.
That way you can require one and possibly have all three available.

[Zoidman]

Thanks UnclDonald418. Although It looks like you just gave me a good solution, I will have to hold off on calling it solved for now, because in my search for a solution I also came across "Why NOT to use an embedded database engine with Base", so now I am faced with starting from scratch and getting HSQL up and running first, all which I am unfamiliar with, including being mostly terminal and definitely Java illiterate. Every can of worms I open contains several more cans of worms, and the skids of worm cans are just piling up.

I am using a computer to get work done more efficiently, or so I was lead to believe, not to increase my work load hundredfold (Not your bad), but I digress.

[UnklDonald418]

Actually you aren't

faced with starting from scratch

all of your work in the embedded database can be easily transferred to the split database.

[Zoidman]

What work, I have spent countless hours, weeks, months and even years just trying to get a single contacts table set up. I am an electrician by trade, and can take computers apart, upgrade them..., Have additional electrical and mechanical engineering training, I am master guitar builder (the business I need a data base for) and put a good 14 hrs into my business 7 days a week, and every time I try to use any so called "Intuitive software", I am faced with the fact that "Intuitive" has the prerequisite that I have to have an extensive education in software development to even get further than a few pages into a manual before I hit a huge wall. Hell, OO can't even format a date or telephone number without me having to learn masking techniques, boolean strings... let alone not be so riddled with bugs that it is near impossible to use it when copy and pasting keeps crapping out or changing with each build, or anything else working as described, if good documentation even exists. I keep getting new features on softwares where the basic features are not even sorted out and stable after decades of development, and the same are being ported to work on smart phones and cars, and soon I will be able to buy a toaster with Android OS, OSX or Linux preinstalled, none of which even work trouble free on the devices they were originally made for!

I installed HSQLDB from the Ubuntu software center last night, without a hint to where it was placed (I found it eventually), and then when looking for how to get it up and running, found out that the instructions require insight on Java and SQL, none of which I have, a front end that I can work with... So far I am afraid to even deploy it without more information because I can surely expect trouble. I want to set it up in server mode as my goals are to possibly have a few employees some day and the ability to use it in the office as well as in my workshop...

Sorry for all the complaining, but I grew up in the start of the computer age, with "The Jetson's" on TV and the promise of computers making our lives easier, and so far all we have brought it to is mass surveillance by big brother and even private corporations (Good luck with Windows), thousands of unproductive ways to waste our time, Hackers everywhere, social media; a playground for sociopaths, and the biggest marketing machine ever, selling more of the same stuff that keeps us from spending quality and meaningful time with each other.

[Villeroy]

Computers make nothing easier. Developers do. And you are the developer of your database application.
Solution: one mandatory phone number and a many-to-many relation to additional phone numbers.

[Zoidman]

No they don't, but they are supposed to, and could, and should, they are tools after all. And sorry, but only developers who understand that not everyone is or can be a developer, and make programs with that in mind make software easier. The ones that write their documentation for unexperienced users make stuff easier. I sure am THE developer, but not A developer (big difference).

When I set up my preferences for my locale, and open a drop-down list where I can choose phone numbers and postal codes... I expect it to know the formatting or, if I enter 11 as the prefix of a phone number, I expect that it can tell that it is in Germany, and format it right automatically and fill in the country. As soon as I type in the name of the city it should fill in the postal code and so on. It takes real intelligence to make artificial intelligence, and it takes good reasoning, imagination and foresight to step out of the box to find new and better ways to do things. No one said it's easy, but without taking on challenges we can not move forward.

Thanks again my friend, Now that I have the HSQLDB thing solved, I can concentrate on this issue. Perhaps in a few months (not under 2 weeks though) I will have learned enough to help others with their DB questions.

[Villeroy]

When you design a database, you are a developer. You build up your own data structures (typed fields in interrelated tables), add queries to get information out of data, add some kind of GUI (forms and reports) providing your aleged supposed end users with easy access to the underlying structures. The underlying SQL language exists since the 70ies. It is the foundation of most databases today. This is development at its best.
Too bad that there is a Base component among Writer, Calc, Draw and Impress. In the days of OpenOffice 1.x this was not a hidden expert feature although the functionality was already there (feed office documents from connected SQL data sources).

[Zoidman]

Alleged in German is angebliche, I think you may just mean targeted (Gezielte)? Ich bin hier in der U.S.A. geboren, aber wirklich ein wasch echter Bayer, und dort gelebt und zur schule gegangen!

In the true sense of the word you are right, but developer is a real profession, and professional I am not in this case. As for easy access to the underlying structure of the data base: I would think that ease of use is also important, and how is that any different than providing easy access and ease of use to the development aspects? When the expert features are no longer needed because the software has evolved beyond the need for an expert to make the software do complex things, it opens the doors for more people to use and enjoy it. As you said SQL has been around since the 70's, and now it's 2016. How long will it take until it can be used to high potential without really having to understand it? The wizard concept is a great Idea, but under utilized. Around 1992 on my 486 Win 3.1 with Symantec Powertools shell (much better than Win 95). I had a program that had a help document window pane at the bottom, that changed as your mouse moved close to any control, and not only was it practical, the help file was one of the most thorough I have seen to this day! Another program was in beginner mode after install, it kept score of your ability to use it, and unlocked features as you learned and the interface grew based on how your learning progressed. It seems to me that everyone overlooked such smart concepts, and software has regressed. Now it can do so much more, but none of it really well except for spying on you...

I cant wait till I get to the GUI part, so just be ready for many more questions and confusion until then.

[RoryOF]

There are a number of useful OO database programming documents at
http://www.pitonyak.org/database/
in addition to Andrew Pitonyak's macro guides at
http://www.pitonyak.org/oo.php

[Villeroy]

@Rory
Macro programming does not help when you don't understand the target. I use Base extensively with very few lines of most simple macro code. You need SQL in order to be productive with databases like you have to master the formula language in order to be productive with spreadsheets.

I would recommend the LIbreOffice Base documentation: http://www.libreoffice.org/get-help/documentation/

First thing to understand: When working with Base, you work with your office suite and a second software http://hsqldb.org/ which is the actual database engine behind the scenes. This is the most important part of the documentation for the embedded database you are working with: http://www.hsqldb.org/doc/1.8/guide/ch09.html. Of course it is aimed to developers. This database engine is almost identical to any other SQL database on the market. Nothing special. Just a simple and somewhat limited relational database. Base can connect to many other well known 3rd party databases instead of HSQL. Most users connect Base to a pseudo-database (spreadsheet, text, address book) in order to do mail merge with Writer. Pseudo-databases present flat lists of some files as if they were database tables.

Second thing to understand: The embedded HSQLDB (see status bar of your Base document) is good enough for educational purposes and demos. When you start using the database for something "serious", you better convert it to an external HSQLDB in order to avoid data loss in the long run. The conversion is not a big deal.

[Zoidman]

Thanks RoryOF, but now it looks a little like you guys are trying to get rid of me!

You guys are great, either you just know where the good stuff is, or I am doing something wrong in searching for this kind of info.

Another one of my pet peeves> Google. They can make stuff like neural networks... but the one thing they got their start with, the search engine, never evolved in its search capabilities, but it has become the most elaborate spying tool next to M$ Windows 10, and now they are working on self driving cars! To make it even worse: being one of the richest corporations in the world, their troubleshooting and support department is a forum of unpaid volunteers! Here it makes perfect sense, but Google?

You need SQL in order to be productive with databases like you have to master the formula language in order to be productive with spreadsheets.

Now you are scaring me: Syntax, I'd rather pay sin tax!!! The hsqldb.org documentation is aimed at developers, I have been trying to extract useful information from it, but to me it's like a snake speaking in tongues!

[Villeroy]

What is the name of your table with the mandatory phone number and what is the name of the phone number field primary key?

[RoryOF]

Also
"Base Tutorial: From Newbie to Advocate in a one, two... three!" by Mariano Casanova

We can't do the background work for you, any more than you can teach us how to make a guitar in a small number of emails.

[Zoidman]

The table is Contacts, but I have worked it over so many times as I kept finding new information about better practices and smarter ways of doing stuff, that I just started from scratch, so I am not there yet. Don't worry I only had two rows of test data in it.

I found Base_tutorial 1-2-3.pdf which is more of a disambiguation than a tutorial, but it is packed with information that should give me a better picture of a data base as a whole, and I am reading it right now. I decided I should get through it first, and model the data base before I waste too much time screwing things up, only to have to fix them later. So the name of the Phone number field is not a priority. I did have three a while back, Home, Work and Mobile, and one suggestion was to also have a primary one as required, that can come from any one of the other three (not required), so that I can have several with at least one required.

It is important that I have a phone number for each client, but very rare that I have to get a hold of someone so bad that it cant wait. Also sometimes someone gives me their work number, and then their employer catches them on the phone, and I am told to use another number. So an easy way to switch around which one is the primary is something I also want to look into. I have big plans, for a guy in training, but I have no right to complain about bad developing and developers, now that you made me an honorary one!

[Villeroy]

OK, the table is named "Contacts". And what is the name of the primary key in that table?

[Zoidman]

ClientID, and I am reading "Base Tutorial: From Newbie to Advocate in a one, two... three!" by Mariano Casanova, right now, but sometimes I strip filenames down to the essential when saving.

[Villeroy]

Namen sind Schall und Rauch.
"We are Americans. Our names mean a shit." Giancarlo Esposito in https://en.wikipedia.org/wiki/Night_on_Earth#New_York
As long as the names work, don't change them.

menu:Tools>SQL...

Code: Select all

CREATE TABLE "PHONES"(
  "FON" VARCHAR(15) NOT NULL,
  "CID" INTEGER NOT NULL,
  "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY;
  FOREIGN KEY("CID") REFERENCES "Contacts" ("ClientID")
);

[Execute]
menu:View>Refresh Tables
See also menu:Tools>References... each PHONES.CID belongs to one ClientID.

Now you can add a subform to your contacts form link it to the phones table by means of the common "CID" and "ClientID". Add a table control to the subform with a text field linked to the FON column. The CID is inherited from the parent form's ClientID.

[Zoidman]

I know all about sound, I build guitars, and my head may just go up in smoke, if I try to think about to many things at the same time!!

What do you mean by

As long as the names work, don't change them.

?

Base has good suggestions, and I won't use any that make little to no sense. Does HSQLDB have defaults it expects?

For right now I have no tables, reports, forms or queries, just an empty database, and writer docs to fill out manually.