Apache OpenOffice Community Forum

Hello
Newbie here to Base and databases in general but have already learned a lot from this forum and various tutorials, however have hit a stumbling block!

In summary I have a boarding kennel and am trying to create a database that will allow a combination of different sized pets into a specific kennel.
For example I have small, medium and large dogs in my Dog Type table, and I have standard, large and family kennels in my Kennel table.

My family kennels can accommodate all types of dogs (small, medium and large dogs)
My large and standard kennels can only accommodate small and medium dogs (i.e excluding large dogs)

So 2 questions.....how can I restrict the kennels in the kennel table to only accept the certain type of dog?
and On the form I have managed to find the list boxes and check boxes, but can't seem to find a way to select multiple choices when creating a form for Kennel runs to select one or more type of pet for that particular kennel.

Any advice would be much appreciated and thank you in advance

If you upload a sample of your database we have a better chance of offering usable help
How to attach a document here

So, for any other newbies out there like me, who have NEVER worked with databases before, I strongly recommend reading the tutorial https://wikinew.openoffice.org/w/images ... O41GS8.pdf and doing the exercises as you go along. I did this after posting my request and whilst waiting for a reply and managed to learn quite a lot from it!!

I'm pretty sure I have answered my own question, but would be grateful for someone more knowledgeable to check my example database to see if it 'works'. In summary I have learnt that you can't choose multiple options in a list dropdown (despite there being a multiple selection option in the general tab) as the forms are simply a way of gathering the data and inputting it into the tables that have been created, allowing only one section of data per table field. So a list that has, for example, 12 options and you want the user to choose say 4 of them, won't work as it can only input 1 option into the field. The answer seems to be to create extra 'option' fields in the table that you want the list to refer to...Option 1, Option 2, Option 3 etc. and link it that way, thereby ensuring that the various options will be input into separate fields.

So, here is my dummy database for someone to check, but hoping it's all ok, and now asking a big favour....I have no idea about SQL or queries. Would someone be kind enough to write a query to give me the results of:
"Show all available kennels and apply filters to show kennels only suitable for small dogs, or for medium dogs or for large dogs"
Eventually the query will have date boundaries (from date to date) but I haven't set them up on my tables yet. Thanking you in advance!

i'm sorry that no one has responded but most contributors are kind, considerate types who won't want to hurt your feelings.
your upload is a typical newbie effort, more like a spreadsheet than a database with zero consideration given to basic normalisation.
a detailed analysis and elucidation would be time consuming, i don't have the time, skill or inclination to do that.

i have uploaded a small db (hsqldb embedded) which i hope provides answers to most of your questions.
the critical information you require is visible in the form 'fGuest_Admit_Locate'.
query names which begin with 'qF' are the datasource for inner forms, queries beginning with 'qLB' are the datasource for list boxes.

hsqldb embedded is a very old database, we really need to be using a modern database and triggers to ensure that users don't house large animals in small runs.

chrisb wrote: ↑Sun Jun 23, 2024 8:46 pm
your upload is a typical newbie effort, more like a spreadsheet than a database with zero consideration given to basic normalisation.

Yes, you're absolutely right....my original, much larger and more detailed database did follow the normalisation rules and each table had it's own auto-increment unique id as the PK and correct relationships but when I started designing the list on the form using the SQL, although the field contents displayed correctly in the drop-down list, it wouldn't accept the chosen entry as 'dog' 'cat' etc...only a numerical value which I guess was the ID number for that description. That's why I tried the example database and chose to use the descriptions as the PK, which resolved the list display /entry issue but obviously isn't best practice either! Having thought about it, and looking at the database you have so kindly provided, I guess I should have been using a query instead of SQL.

hsqldb embedded is a very old database, we really need to be using a modern database and triggers to ensure that users don't house large animals in small runs.

Yes, I agree...however this is the default one that my OpenOffice refers to and until I learn more about databases from scratch I didn't want to explore or invest in anything more modern as it's more than likely that I will have to forget the whole project!

Here is the problem: I live in France and have been using a commercial UK kennel software since 2008 which I believe is written using MSAccess, and until now has served me well. However, there are a couple of issues - one of them being that it's PC based so unable to integrate it with various apps like online payments, appointment calendar, customer online booking etc. These are little niggles and aren't the be-all and end-all. However my main problem is that since France is tightening up it's anti-fraud measures, the invoicing element of the software is no longer compliant with the new rules (the law states that once an invoice is created it can never be modified, amended or deleted and the software provider has to provide certification that the software complies with that rule). My software provider is aware and has offered a workaround in that by changing user access rights, the user can't modify or delete invoices, but their solution still isn't compliant or certified. At the moment I am having to manually duplicate the customer database on my kennel software onto my invoice accounting software program (similar to Sage) and manually input each invoice and payment...which takes time and is open to errors. I have asked my kennel software provider if it's possible to export the invoicing elements...his initial programme in 2008 had the option to export to Quickbooks but I no longer see it in the options and also Quickbooks and Xero are no longer available or supported in France.

It doesn't end there though, because in 2026 the laws are getting even more complex and a company will no longer be able to simply hand over a printed invoice or email a pdf invoice to a customer....all invoices will have to go through a third party government approved encryption platform so they are encrypted pdf files and all relevant tax information is collected immediately by the tax department. Clearly my current software provider (based in the UK) will not be willing to adapt his well-known software just to meet my needs!!

So the obvious answer is to look at French kennel software solutions which are currently compliant and are ready for the change in 2026...there aren't many and of the ones I have been able to try they lack in flexibility from a kennel management point of view. The newest one which is being flogged like a dead horse, appears to have been written by someone with very little background of kennel management and has been based on a hotel-style app - it lacks the function to charge different prices according to different sized dogs or run sizes, it won't allow an overlap on the last day of one booking and the first day of another, it groups all pets from one booking into the same 'room' (where they need separate 'rooms') etc.etc.

It's clear that my skillset is seriously lacking and I would never be able to create a modern software that could be integrated into other apps such as an approved invoicing platform (to comply with the French law), calendar appointments, payment integrations etc. so my choices are:
1) to get a professional to write a software programme €€€€€€€
2) use the French compliant software and change my whole way of kennel management / pricing etc.
3) knock down my kennels and rebuild them so they are all the same size to be able to use the French kennel software
4) give up and retire

The last option is the most tempting right now because it seems that the French government is going to squeeze every last small self-employed person out of business...I've been doing this for almost 20 years and it's getting harder and harder to comply with new rules and regulations.

Sorry for the essay and I appreciate the help you have given so far. I will study your database and hopefully learn more from it, if not to progress with my project, then at least to satisfy my curiosity about how things work!
Thanks again

although the field contents displayed correctly in the drop-down list, it wouldn't accept the chosen entry as 'dog' 'cat' etc...only a numerical value which I guess was the ID number for that description

Yes, that is how all the ListBoxes work in the excellent example provided by chrisb.
Look at this Tutorial Combo vs List Box
There are a number of Tutorials and Examples that you might find helpful. The links are near the top of the Base area of this forum.
Unless you are a longtime user of OpenOffice, I recommend switching to it's close cousin LibreOffice which is under more active development.
https://www.libreoffice.org/download/do ... breoffice/