Macro for Inventory Reduction When Running a Recipe

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
shoebocks
Posts: 1
Joined: Tue Aug 13, 2019 5:50 pm

Macro for Inventory Reduction When Running a Recipe

Post by shoebocks »

Hello
First post here. I run a small spice business and I am currently looking to improve my internal inventory management system. One task that I do a lot is making blends of different spices. I have sheets that have recipes, which I use to determine the cost of making each recipe and currently I manually reduce inventory when I make a recipe. What I would like to do is click a button that executes the recipe and automatically reduces the inventory of each individual spice used in the recipe. I have attached a basic version of the table. It has one sheet that is the master inventory and a second sheet that is a recipe. Any and all help is greatly appreciated.
Attachments
inventoryexample.ods
Example of my inventory and costing table with recipe and master inventory
(20.9 KiB) Downloaded 205 times
Open Office 4.1.6
Windows 8
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Macro for Inventory Reduction When Running a Recipe

Post by UnklDonald418 »

Macro coding requires some knowledge of the OpenOffice API. Even for experienced programmers learning to use the API is difficult.
To get a feel for the API see Andrew Pitonyak's book "OpenOffice.org Macros Explained", which can be downloaded from
http://www.pitonyak.org/oo.php
Writing coded macros that don't break every time you make a change to your spreadsheet can be challenging so generally speaking writing coded macros is usually considered to be a last resort.
In theory the macro would step through each item in a recipe.
For each item it would need to step through the Inventory looking for a match.
When a match is found then a quantity from the recipe would be subtracted from the quantity in the Inventory.
Sounds simple enough, but the devil is in the details.
What happens if a match isn't found?
Looking at your example recipe the first component is Cinnamon, but looking at your Inventory there are 5 types of Cinnamon, how will the macro know which one to use? In fact all the ingredients in your sample recipe are ambiguous!
There are other potential problems that will also need to be addressed, for instance what happens if the recipe quantity is greater than the quantity in Inventory?

What you have is a classic example of attempting to coerce a spreadsheet to do the job of a database.
But database design is an advanced topic all on its own so, there is no simple solution.

When the database is simple, a spreadsheet can sometimes be successfully used . What you have shown here might be simple enough, but it definitely needs to be designed more like a database. Another question is how many recipes will you ultimately have. A dozen or so might be practical in a spreadsheet, but much more than that and the spreadsheet becomes too unwieldy, leaving a database as the only practical solution.

The unit cost/price and any other characteristics of an ingredient should appear with the inventory item on the Inventory sheet.
On the Recipe sheet a formula can multiply the quantity required for the recipe times the unit cost on the Inventory sheet to generate an extended cost for each ingredient in a recipe.
Also on the Recipe sheet, instead of typing the name of each ingredient use a formula. Press = then select the Inventory sheet and then the name of the ingredient and finally press Enter. The formula will display the name of the selected ingredient, removing the possibility of ambiguous or non existent ingredients. As a bonus it will simplify the macro code because it won't need to search the inventory list for a matching entry.

I guess the question at this point for you to consider is what is your primary goal. If your goal is to learn more about macro coding, then proceed down the current path and see if you can succeed with a spreadsheet.
If you only want a way to track your ingredients and save your recipes, then either continue what you have been doing, or look for a recipe database that has the features you desire.
I suppose a third option would be to embark on learning about database design. For an example of how that is done see
https://wiki.documentfoundation.org/ima ... torial.pdf
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro for Inventory Reduction When Running a Recipe

Post by Villeroy »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro for Inventory Reduction When Running a Recipe

Post by Villeroy »

In addition to the database of recipes I can offer a simple inventory: download/file.php?id=28761
It stores availlable items in one table, gains and losses of items in another table. One tiny input form lets you add items, another tiny form lets you take away items. A third form allows you to edit the inventory. One report shows the current inventory. 2 more reports show the history of items.
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
Post Reply