How manage inventory discrepancies due to measurement errors in warehouse management systems

Question

I'm developing an enterprise-grade warehouse management application for a chemistry laboratory. A critical feature involves mixing multiple stock solutions to create new solutions based on predefined formulas. However, due to inherent measurement errors during the mixing process, discrepancies arise between real-world measurements and digital data., I am facing some challenges that I do not exactly how to solve.

Real use case

One of the key use cases is creating a new solution by utilizing existing stock materials. Specifically, users select one of the predefined solutions from our database and specify the desired quantity to produce. The system then calculates the required materials automatically with a 1% of tolerance. For example, to create 10 L of Dissolution A, the system displays:

NaCl      10.00  ±  0.1 g
Glucose   10.00  ±  0.1 g
NaOH 1M   100.00 ±  1 mL

Users then anotates how much of this materials (NaCl, Glucose and NaOH) it uses for the creation and confirm the creation of this new Dissolution A, so, the quantity of this materials is substracted and the new one is added. However, due to measurement inaccuracies and human errors, some of this scenarios can happen:

Scenario 1: The system indicates 10 mL of NaOH 1M is available. The user measures and uses 9.98 mL. The system records a remaining stock of 0.02 mL, which in reality should be considered as 0 mL (i.e., the solution is over).

Scenario 2: The system shows 10 mL of a solution available, but the user measures and uses 10.06 mL. The system attempts to save a negative stock value (-0.06 mL), which is not feasible in reality.

What are the best architectural practices and engineering techniques to manage this types of situations? Any strategies, design patterns, or validation techniques that can address these challenges effectively or that you know is been applyed in some real world scenarios would be appreciated.

EDIT

I marked the question as answered by the first response since it provided the most immediate solution. However, I highly recommend that anyone with a similar doubt or out of pure curiosity read all the answers and comments on the post, as they contain a lot of valuable information.

Answer 1

Inventory systems of all kinds need to cope with the fact that their numbers and the physical reality sometimes disagree. As long as the difference does not hinder the intended use of the inventoried materials it can be mostly ignored. However, there may be "checkpoints" when you have a chance to fix up the numbers to better match reality.

One common checkpoint is the annual (or otherwise regular) inventory, i.e. measuring the reality and updating the data.

Other checkpoints could occur when the amount of material (as measured or as known to the system) approaches some minimal threshold. Your scenarios would fall under this category (actually, an inventory check would likely be triggered a while before you actually empty the storage).

The simplest case is that of underflow. If someone took more from storage than the system assumed is there, the system now knows that its previous assumption was false, and that the storage is now empty (it might confirm with the user that they really took the last bit).

If the remaining amount according to the system is very low, it might also ask the user whether they took the last bit or had some leftover amount, and note that accordingly.

You might also want to provide a way for users to let the system know that what they found in storage is different from what the system assumes. For example, if the storage should contain a full 100ml bottle but the user finds that the bottle is only half full, they could enter an adjustment into the system, and this might trigger a check for unauthorized or undocumented access investigation.

Answer 2

Before we can offer suggestions - and before you write another character of code - I strongly believe that you need to know the answers to these questions and any that follow on from them as you're gathering requirements. In no particular order:

What questions does this system need to answer?

Is it an early warning system for Purchasing so they know when to order more stuff?

Is it an aid to workers so they know how to make things (eg., OP's example of creating 10 L of Dissolution A) that should warn them when components are low?

Does it need to alert someone if a container of something has been there too long (eg., because it'll go bad in the bottle)?

Is it going to track who uses how much of each tracked thing for (internal) billing, QA, performance review, and/or other purposes?

Is this going to be used for product tracing - do you need to be able to answer "which vials of Dissolution A contain glucose from jar X"?

Which questions are most important?

If you're tracking consumption - especially of high cost-per-unit product - for billing purposes, I suspect that error bars are going to be a lot more important than if the primary purpose to let Purchasing know when to order more salt.

If the primary purpose is to aid workers in making things, error bars may be wholly irrelevant.

How often is inventory taken? How large is the inventory?

Is this system meant to track the usage by an office of 3-5 people for a day, or for a lab with thousands of users for a year?

How granular do you need to be?

If you get in ten 1-pound bags of salt, do you record "bag 3246, 1 lb", "bag 6987659, 1 lb", etc., or do you record "+ 10 lbs salt"? If the former, what are the rules for combining salt from multiple bags?

How seriously will users take making any modifications?

If I'm making Dissolution A and drop the glucose, how likely am I to note in the system that I took a second 10g from storage?

Will users take "a bit of glucose" from storage, use the 10g they need, then put the rest back? Does the system need to track that?

How much does the company care about compliance, and how is compliance enforced? No, really: how much?

How much shrinkage is expected?

Do people swipe salt from inventory to season their lunch? Do they sneak more-valuable substances out to sell on the black market?

There may also be implementation questions around who enters the incoming product; there are almost certainly compliance questions here.

What problems arise if the system is wrong?

If Purchasing orders a ton of salt early because the system thinks you're running low, does somebody get a talking to or does it just go into the warehouse for later? What if they don't order in time because the system thinks there's plenty?

What happens if it turns out Bob routinely uses 11g of glucose when making Dissolution A? What happens if he does and the system doesn't catch it? What happens if the system thinks he does and reports it, but he doesn't?

What other systems will this interact with?

What are those systems' requirements? How are they used now, and how will this tool augment the value they provide to the business/process?

Are there other systems you can query to get additional insight into quantities (eg., scales under specific products)? How accurate and precise are they?

What do The Powers That Be say?

Does the boss want "what's the minimum we have on-hand, assuming people enter spills" or would they prefer "what would we have if everyone was perfect"?

Or, perhaps both! Maybe Purchasing wants the former number so they know when to re-order, but Compliance wants the latter so they can compare expected vs. actual quantity remaining.

Does the system feed into itself?

When I make a gallon of Dissolution A, does the system add that gallon to the inventory on-hand?

---

Find the answers to these questions. Most answers should spawn at least one follow-up (and likely more). The answers you get from the business should give you enough to start building the system and come back here when you run into issues.

Answer 3

The general concept here is about tolerances.

No manufacturing or laboratory process is perfect or fully stable, and it should certainly be assumed that every event introduces a cumulative margin of tolerance/uncertainty into the stock accounting. A cumulative margin that can only be reset by physically re-measuring the stock, and adjusting the recorded stock level.

The amount of tolerance to accomodate routinely is a characteristic of a particular system, and there will still be exceptional events too, such as when a chemist trips and throws the beaker of acid over the floor, requiring another lot to be drawn to get on with the work at hand.

Liquid dispensing systems also need careful monitoring and occasional calibration. Room temperatures can alter the physical volume of liquid reported. So can the temperature of fresh liquid when added to storage. You can put 1000ml in and get 900ml out, and the only difference is that the temperature has changed in the meantime.

Also, action is not typically first triggered upon a storage vessel falling empty, but upon a low level threshold being reached.

The simplest record-keeping system is simply to record nominal amounts at an appropriate granularity, and reconcile them regularly to the physical stock.

The granularity of stock-keeping records doesn't need to match that of the measurement equipment - if a person draws 1.01ml, there can be a standing rule in the lab that this is recorded as 1ml, until a tolerance is reached where it steps up to 2ml. Or if a tank says 0.02ml remains, this would be recorded as 0ml. If the tank is automatically monitored, there can be a rule that measurements are always rounded down to an appropriate granularity.

More sophisticated approaches would depend on exactly what you're trying to achieve.

It must always be remembered that software development is expensive with labour, not just in the initial development but in the ongoing maintenance (including maintaining the capability of the staff function which understands the application and can readapt the software over time to new or changing demands).

The recording of measurements and the production of data, so as to input them into a computer, can also be a costly activity in its own right, separately from the work of the software developer who designs how the computer will handle those records.

So it's not an area where gratuitous complexity should be tolerated.

Answer 4

In addition to the simple underflow logics described in the answers, there's a few alternative things.

One is to acquire more information. If you use the underflow logic described in other answers, you may want to reorder early. However, if you intentionally do not start "consuming" the new reagents until the previous one is depleted, you can keep the errors within some fixed tolerance that cannot grow. This approach relies on the humans using the materials to let you know when they're "done" with the previous shipment and opened the new shipment.

The other answer is to really understand what your problem is. If you can model the way people use a reagent as a random variable then Bayesian Analysis can be used to determine when the best time to act is. The answer to this depends on the task at hand.

The final end all answer is called uncertainty quantification. It's an entire mathematical field that you can get a PhD in. Uncertainty quantification would give you the correct action to take in light of any particular sources of uncertainty.

In the end, though, the usual answer is KISS. There will be a business logic which weighs the cost of the item not being there against the cost of having more inventory than one wished. This is the kind of logic that has to be used in the presence of shoplifting, where every now and then one's inventory numbers are simply wrong.

Answer 5

as mentioned, this has nothing to do with design patterns and everything with tolerances and procedures.

For everything in storage a tolerance will need to be provided (possibly different tolerances for different products, so keep that in mind, and those tolerances may change over time as well). Then also, different tolerances may be needed for each CHANGE of inventory as well.

Take your example for salt. If you have 10.000kg storage capacity you're not going to have that measured to the milligram. More likely your tolerance will be a percentage (say 0.1%) of the total. Same with withdrawals. If I withdraw 1 gram I'm going to withdraw it with a precision of a few milligram probably. If I withdraw 10kg I'm probably content to be within 10 grams if not more imprecise. So there too you're talking about percentages.

Don't be fooled, this all adds up over time. You get resupplied with 1000kg according to the load sheet of the supplier, but in reality it's only 999.5kg. That's 500 grams difference, half a percent. Depending on the contract with the supplier that's likely within tolerances, but according to your 0.1% tolerance for your own system it's out of bounds so you need to correct the amount coming in from 1000 to 999.5. Now 1000 withdrawals are made, each within a 0.1% tolerance. But those 1000 withdrawals total 2500kg. That's 2.5kg difference.

It gets even worse if you take floating point rounding errors into account. And keep in mind as well the different (and floating) tolerances of your measuring equipment. Can the scales that measure the amount in your storage tank even detect a 1kg discrepancy on 10.000kg? The scales used in the chemical lab may go down to less than a microgram +/- the displayed value, but the kitchen scales used in the test kitchen only go down to a few grams +/-! And the scales used by the production machinery that makes larger batches of product for packaging and shipment to customers may only go down to 25-50 grams on a hundred kilos.

That's the reality of bulk storage, nothing is exact except within defined limits and you're going to have to account for those limits when keeping track of what you have in storage, what is coming in and what is going out, all with different precision depending on the channel.