Switching sources when value hits 0
So we own inventory. When we don't have enough to cover a job we rent. I've set up a database with 3 tables: inventory, orders and lineitems. People can pick inventory and compile orders. It's all working fine but I guess we didn't think it through enough and instead of the solution I'm now aiming to create we did the following: each item we own has an entry in the database, and so do all items that are rentals, even if they are duplicates of something we already own.
E.g. we own a broom and if we need more then we order from a Vendor. So there are 2 entries for brooms in the inventory DB, one as a broom that is owned by us, and a second broom that can be order from the Vendor.
What I'd like to do is have a single entry and create a mechanism that automatically adds items to the order according to what we have in stock. So if we have 1 broom but need to then the lineitem record would show how many was available in stock and how many were rented from where.
So my question is this: how do I set up a calculation that will only grab as much as is in stock and add the remaining part of the order to a field indicating rentals? I'm sure it's simple but I can't quite figure it out.
Also, would I need to restructure my tables to make this work? I think I can leave everything as is but maybe I'm wrong.
Thanks all, your help is always greatly appreciated.