When a record can play dual roles
Found myself in an interesting situation:
Context - Inventory management
Current setup (the pertinent tables best I can figure):
Inventory prediction and allocation table = "MatManagement"
Inventory summary table = "MatInv" includes summary of all related weight sheets
Weight sheets per lot = "weight sheets" Includes Qty of material left per lot
Transactions (Line Items) = "WSLI"
MatManagement MatInv WeightSheets WSLI
MaterialID ----------- MaterialID WSID---------------<WSID
Now a vendor sent material in that meets the specs for two different raw materials. Didn't see that one coming (shame on me).
When I enter an order, the MatManagement looks through the formula, calculates that I need 20Kg of GlassA, compares to the quantity of GlassA in stock (MatInv), and flags whether or not the order should begin manufacture, and if so creates a material request which allocates the material and lowers the available stock level (MatInv). That works fine. But I have 300Kg of material that meets spec for GlassA and also GlassA_milled.
What suggestions are there for how to count the 300Kg of glass toward both GlassA and GlassA_milled without creating erroneous flags saying that I can use 600Kg of glass? I could stock 150Kg in each type, but that would allow a stock outage and reorder when it wasn't really out...how to create a functional alias without a full redesign of structure (which would not be tolerated by the users at this point)?
Thanks for whatever thoughts you can offer.