I've been using Filemaker since 2011 and it has done a great job for us, but the business has changed a lot in that time and increasingly I'm having a lot of problems, especially with inventory. I've recently decided to move my solution from a shared-over-network Pro file to a FM server model, and I'm using that excuse to start over from the ground up, but there's so many complications I'm having a hard time seeing past the solution I've been applying band-aids to for 7 years. I was hoping someone could help me think this sucker through.
As a side note -- we are very closely regulated by the FDA, so when I use the word "must" below, take that as a non-negotiable, government-mandated feature.
My business is a raw milk cheese plant. We produce multiple types of cheese and multiple batches per day; batches contain a variable number of cheese wheels. Data is entered on an iPad kept in the cheeseroom. All ingredients going into the cheese must be tracked by lot number.
Once made the cheeses move through a brine and dry room setup into the aging cellars, where they will stay for 2 months to a year. Currently there are three cellars and data there is also tracked on an iPad. Cheeses are handled multiple times weekly and cannot be ID'd by the wheel at this stage, only by batch. They are stacked on wheeled racks (stacks may contain multiple batches; batches may be on multiple stacks; and batches may move between stacks during aging) and these stacks are moved between cellars multiple times during the aging process; I need to track which cellars they were in, when, and for how long.
Once the cheeses are sufficiently aged they are brought up, weighed, and wrapped for sale (at this point they can be ID'd by the wheel), and may be stored for up to a month in one of our two walk-in coolers before being sold to customers. Our customers order by the wheel but are charged by weight. I must track the packaging they're wrapped in by lot number, and I must track the end state of each wheel -- whether it's discarded, sampled, or sold, and if sold, how much to which customer. Sometimes wheels are cut and broken into smaller pieces for sampling or sale; this must also be properly recorded. Because these are raw milk cheeses, I must be able to prove that no wheel of cheese is ever sold under 60 days of age.
Non-ingredient and non-contact packaging stock items -- boxes, office supplies, cleaning chemicals, et cetera -- I don't have to track by lot, but they have their own complications. I have divided items roughly into "consumables", which by nature get used up, and equipment which we use until it breaks. Some suppliers sell us ingredients and stock items; some items have multiple suppliers.
In practice this has ended up being... kind of insane. My main sticking points have been the ingredients -- tracking lot numbers has been, for various reasons, a really big struggle -- tracking the movement of cheese between cellar locations, and getting a system for ordering and inventorying stock items that stays up to date and does not result in "surprise! we're out" moments. There have also been problems with cutting cheese into smaller-than-wheel increments. At the moment this isn't a big deal to fix by hand, but we may be drastically increasing the amount of wheels we cut in the future and I want to future-proof this if possible.
A final note -- this is a farm, and the level of computer literacy of my coworkers varies highly. At the moment, my system works (at least, most of the time)... as long as I'm the one working it. That's not good enough. Suggestions on making this easy to use are gratefully welcomed.
I apologize for the insanely long message, but to be honest, it's helped me just laying this out like this. Any suggestions, previous experience, resources, or tips would be most gratefully welcomed!