OODatabase - Create Pick List And Save Result

[MartynW]

Although I have been working with computers for some years I am new to actually writing a database application. I am looking for guidance for a pricing database for my wife's home-bakery business. The database will eventually contain hundreds of ingredient items and a hundred or more recipes (bakery product items). The database application will be used on a Windows (7 or 10) computer.

The applications will be to:
- add ingredient items to the database table (item, market price, purchase quantity, unit measure, price per unit measure (e.g. $1.25 per 100gm))
- periodically update the current market price for ingredient items in the table
- create & save a list of ingredient items for each bakery product from a drop-down ingredient items list
- display a summary price of an individual bakery product item using current market prices (cost, wholesale, retail) as source based on a query form using bakery product item as source input
- a prompt for my wife to check current market price for ingredient items that have not been updated for (say) 90 days

I know how to create the Tables for ingredient items (although I am not sure if the 'price per unit measure' is best computed at the point of ingredient item 'save/update' or every time a bakery product item is accessed) and for product items.

My major problem at this time is that I do not know how to make the table that ties the pick list of ingredient items to individual bakery product items and then saves them.

For simplicity, imagine 3 bakery product items: product item 1 requires specific quantities of ingredients 1, 2 and 4; product item 2 requires specific quantities of ingredient items 1, 2 and 5; product item 3 requires specific quantities of ingredients 2, 3, 4 and 5. In each case, when my wife selects a bakery product item I would like the database to return the product name, the list of ingredients (to make sure she has not missed anything) and the three summary prices for the individual bakery product item (cost, wholesale, retail). The list of ingredients should flag ingredient items where the ingredient price has not been updated for (say) 90 days, as a prompt for her to check the current market price.

Can someone please guide me? Is this too much to ask? Or, has someone done this complete application before and can send a copy to me in return for a New Year gift?

[Villeroy]

It is a lot to ask. Each and every aspect has been discussed many times in depth with dozends of example documents and tutorials focussed on the backend side of the problem (the database) and the frontend side which is this office suite.
Prominent example: [Example] Invoice Forms (without macros)

What most people do not understand when they create a first database with Base is this:
-- Base is not a database. It is a tool to connect many types of databases with office documents, namely Calc spreadsheets and Writer documents (label printing, serial letters etc). The vast majority of Base documents connects some address list typed into a spreadsheet with a form letter. Creating form letters and label printers from various tabular sources was the first requirement for database connectivity before a Base component even existed (before 2005).
-- Base is the drunken cousin of Writer, Calc, Draw and Impress. It is a lousy database development tool in alpha state since 2005. All those wizards and design tools do not cover more than 5% of the options you actually have. Even the most simple invoicing solution with forms and not so trivial queries needs to be done manually.
-- Base is shipped with a 3rd party database product called "HyperSQL" (HSQL) which allows you to create self contained database in a single file similar to MS Access which can embed a Microsoft JET database in a single container file. Many Access users never realize that you can connect MS Access to professional databases. Base's container format is a simple zip file. Every time you access the embedded database, this database is installed into a temporary directory and it is zipped back into the container when you close the last reference to it. This is a dangerous process. It may destroy all your precious data in case of power outage or sudden system shutdown/hibernation. Our little user community offers some tools to convert an embedded HSQLDB into a stand-alone HSQLDB which is by far more reliable with up to date drivers and multi-user capable if you run HSQL as a service.
-- You are not really free to organize your data. Simple tools follow simple rules. Your database design needs to be as clean as possible in order to make it accessible from a simple tool set. The rules of database normalization are mandatory and this is where non-techies drop out.

[MartynW]

Dear Villeroy,

Thank you for you reply and the detail contained therein.

I conclude that in order to achieve my objective I should be looking beyond Base and identify a true database application in order to achieve my objective, possibly with Base as a front end tool.

Thank you for your time.

[John_Ha]

Search the site with bakery to find I need a Base app to track customer orders and other posts.

Databases are tricky and easy to get wrong which will inevitably end in tears all round. Larry Ellison didn't become a billionaire by chance.

My advice is to concentrate on what you do well - baking things - and leave coding to professionals so buy a simple solution.

See Re: Trying to build an invoicing database for a travel agency and Complete NOOB needing major help! and How to make database design for booking system ? and Using AOO Base for Small Business Sales.

Search the forum with business for much similar advice.

But if you are misguided enough to attempt developing something use a program like MS Access.

[Villeroy]

I'll attach a quick mock up of a database that may fit most of your needs. I created most of the database in a text editor with SQL syntax highlighting. I can type SQL quicker than clicking the table tools and at least one feature (autom. time stamps) is unsupported by the Base GUI.

Converting this embedded HSQLDB into a stand-alone HSQLDB is a matter of a minute. Using it with a recent HSQL driver is a matter of 2 minutes. Converting the backend to something like MySQL is a matter of ... well it is possible.
So far I abstained from any macro code until the database and forms are up and running. It is easy to add a little bit of Python code in order to save some clicking.

The input form "Sales Form" is the one which can be used at a point of sale.
First you have to initiate the creation of a new sale by selecting a cash register (I added one dummy, this could also be a user name) and save this record by hitting Enter or clicking [OK].
Then you can enter quantities of articles to this sale. This should work fine without using the mouse.
The table grid on the right side shows this sale's entered articles and the sum after you clicked the [Refresh] button. This should be automated by a little script.
After you finished this sale, you click the [New] button, select the register/user, hit Enter (or push OK) and start the next sale.

Before you start with your own articles and prices:
Table "ARTICLES" saves short names and longer descriptions of articles. Column H lets you hide articles from the sales form.
There is a form "PRICES" where you enter prices for articles at a given date. I added prices for my articles A,B,C,D at 2019-11-01 and added a price increase by 0.1 at 2019-12-01.
The "Sales Filter" form shows all article sales with prices, quantities and amounts based on filter criteria and based on the date of sale. There could be a lot more of these tools and I did not create a single report.

[MartynW]

Thank you for this. I will be looking at it in detail tomorrow - today out and about the town buying ingredients

[MartynW]

BTW the bakery products that my wife offers are on https://www·somersethomemade·com/