Inventory of plant orders, many: plants, jobs, nurseries

February 14, 2022 7:24 PM

I have 15 planting jobs on the go (at any one time) where where I order plants ahead for planting this year and out to about three years ahead. Covid's disrupted plant supply and I often have shortfalls and need to juggle plant numbers, including substituting plants from other jobs. Would there be a spreadsheet solution for this? it's beyond my ability to think about it but it seems it would be little different from a lot of inventory control solutions.

I have:
MS .accdb (via Office365).

MS excel (via Office365).

My system is Windows 10.0, desktop, plus a local NAS (which has the Excel job files).

I manually clean incoming confirmed orders so can change that to match what Excel/Access need.

My job species lists are in individual excel job files on the NAS. Files are typically Plant name, number of plants needed, nursery name, plant size (two columns for different sizes), plant prices (two columns), and a notes field.

If it matters plants numbers are tens of thousands per year, and about 500 different plants.

I'd like to do is write a plant name in a cell and then see via (a dropdown?) that I still have 700 of xxx species available, or that I have a deficit of yyy species.

And ideally be able to have a worksheet within each job file of a summary of plants allocations to jobs - to be able to keep on eye on the state of allocations.

posted by unearthed to Computers & Internet (4 answers total)

Best answer: You can use Access for this if you're okay with learning basic database set up methods. You can import Excel files, but you will need to deal with import errors if they come up. The more basic your excel data is, the fewer errors you will have. Data types are important, so you are not importing dates into a field that is expecting a dollar amount.

You want one table that lists all of the jobs and gives them either a shortname or an id number. This name or number needs to be unique, so you can make it a Primary Key and that will make it impossible to add a new record to the table with the same id. This Job table will not list plants, it will only list the jobs and the info like contact name, contact number, contact email, date range (can be one field or multiple for start/plant/stop date), quote, address.

Then you want the same table for Plants. ID, name(s), sizes, costs, etc. If you have multiple nurseries, you'll want yet another table for them. Now you need tables that match these tables up. You want a table that relates jobs and plants by listing job Id, Plant id and desired quantity. Maybe add an actual or predicted quantity as well.

If you're using a nursery table, set one up that shows which ones carry which plants (using the id for both).

Now you have the orders that you place, so have an Orders table that gives you the date, vendor id, plant id and ordered quantity. You might want one record for each plant type that you order, even it is the same vendor and order date. Also, a column for amount expected would be good so you can see when it is different than the amount ordered, plus one for amount received if you need that at some point. If you get order numbers from the vendor, that may be your id or you can have that as another column.

Now you can write reports or queries that will relate all of these tables together and sum up the totals, including the desired amounts, expected amounts and actual amounts. If you get fancy enough, you can write a form to add to your jobs+plants table that shows you the inventory (surplus or deficit) when you pick the plant.

If you want to start smaller, limit the number of jobs and plants to 10 or so if you can, even if you have to work with fake orders to learn the methods and ensure your results are correct.
posted by soelo at 7:58 PM on February 14, 2022 [3 favorites]

Best answer: It sounds a little bit like a supply line / manufacturing / Just-in-time problem which is typically solved by a MRP / ERP (manufacturing resource planning / enterprise resource planning) tool. You keep "inventory" of X and different jobs tell you how of of X, Y, and Z are needed.

What you are looking for is basically a look-up tool, which can be coded in either Access or Excel with VBA or whatever Microsoft call it nowadays. It should not be hard for someone to make in a week, if they have some Access or Excel skills, from designing some sort of form or user interface in either Excel or Access, to pulling in the job files, and tell you what's remaining in "inventory" and what has been allocated, and whether you need to reallocate the stuff, if you can verbalize the logic you use for the allocations.
posted by kschang at 10:54 PM on February 14, 2022 [2 favorites]

I handle similar inventory issues for other products, and I use Odoo.
It's free if you only need Inventory + Manufacturing (what you would implement in your case), but if you want to add purchasing, so you can also generate PO to your suppliers, then you need the paid membership. It's a bit fiddly to implement, but after the first hurdle of importing it all in, i think it's paying off.
posted by PardonMyFrench at 3:43 AM on February 15, 2022

Response by poster: Thanks soelo, that helps me frame my issue much better. If I do this myself that's what I'll do, and kschang, your emphasis on being able to verbalise what I need is very useful.
posted by unearthed at 9:55 AM on February 17, 2022