Yes/No Boolean Queries

Creating tables and queries
Post Reply
PufferCat
Posts: 3
Joined: Sun Sep 24, 2017 5:50 pm

Yes/No Boolean Queries

Post by PufferCat »

Hello, this is my first post on the forums and I hope it's in the right section; the company I'm currently working for prefers to use Base and I have a limited knowledge of it, as opposed to my high school training in Access.

I've been put on the job to do a database for a condominium and its inhabitants in order for them to be easily found as it's all about the central heating unit: it's not so much difficult as some of the things I got asked to do I can't seem to actually do them in Base. For starters:
  • 1. It was asked of me to create a field that has the YES/NO boolean value (the field being "Can be Contacted Via E-Mail", y/n value) that, if it's a YES, needs to ask me the e-mail field to be filled out. He then wants it repeated for the dilation of the complete payment sum in the nominal Customer's table (the field being "Paid in Installments", y/n value), so if it's YES I need the table to ask me to fill in the individual installment sum and the final amount that needs to be paid, if it's a NO then the value of the "Nominal Installment" can be null).

    2. It was asked of me to have a Fiscal Code field (my primary key for the Customer's list) with two of these fields as well:
    a YES/NO field for the VAT Number if it's a company (the field is "Company?" and the field is YES/NO - if YES, I need it to require the VAT field to be correctly filled out, if NO it's good even if the value is null).
Can I do any of this in Base? I know it sounds confusing - if nothing is making sense for you, I'm here to better explain it! Thanks for any help you can give me!
OS: Windows10
OpenOffice Version: 4.1.3
Most Used Program: Base
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Yes/No Boolean Queries

Post by Villeroy »

if it has a mail address then store it, if not do not store it.
If it has a vat-id then store it. if not do not store it. Extra check boxes make no sense.
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
PufferCat
Posts: 3
Joined: Sun Sep 24, 2017 5:50 pm

Re: Yes/No Boolean Queries

Post by PufferCat »

I should just get rid of the YES/NO fields and just have the fields as normal? It'd make sense as it'd reduce the DB overall width.
It's just what he has asked me to do, my initial idea was to just have the fields and only have "E-Mail" and "VAT ID" along with the Fiscal Code, this one being the only PK of the table and the only required one.
OS: Windows10
OpenOffice Version: 4.1.3
Most Used Program: Base
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Yes/No Boolean Queries

Post by Villeroy »

Of course you are free to add additional booleans to the text fields. What they mean is entirely up to you. Something like "well, there is an email address but it is only relevant for this and that if the boolean value is True". But what if the boolean is True while there is no valid mail address? 2 fields for the same information can be confusing. You have to be very clear about the meaning of the boolean and its associated text.
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
PufferCat
Posts: 3
Joined: Sun Sep 24, 2017 5:50 pm

Re: Yes/No Boolean Queries

Post by PufferCat »

Technically, in this case scenario, if the person has agreed to be contacted by e-mail, then the field must have a valid e-mail or it won't be accepted - the boss' idea was to add this boolean in a way that, if flagged yes, it would have asked the person to input a valid e-mail, if not flagged and therefore a no, it would have been okay to have set as null as it wasn't required. I don't know if I'm making myself clear due to my English but it would be as if I'm doing a flux chart and it all starts on the requisite of the e-mail. If yes, then ask for e-mail, if no then leave the field null.

It's nothing that the boss wouldn't be able to do without, sure, just wanted to ask if it was possible to do so I could possibly please him since he's already given me another task that is pretty lofty and I'm not sure it's doable in any other way than Visual Basic.
OS: Windows10
OpenOffice Version: 4.1.3
Most Used Program: Base
Post Reply