AnsweredAssumed Answered

Updating or creating join table records as needed

Question asked by PeterThorton on Jun 6, 2012
Latest reply on Jun 12, 2012 by philmodjunk


Updating or creating join table records as needed

Your post


my question for today involves join tables, but in a way I have not used them before. My situation:

The company I work for is a distributor, so it has shipments going in and out at all times.

I have a table called Product, which has an InStock field, that keeps the current stock amount for each individual product. When a shipment comes in, InStock value is increased for the relevant products. Likewise, when a shipment leaves the company, InStock values are decreased.

However as it turns out, the company has multiple warehouses. Other than keeping track of the total amount in stock, I need to keep track of how much stock is in which warehouse. I've made a simple data structure for this (pictured)

It is basically a simple join table, that joins Product with Warehouse and specifies how much stock of the given product is in the given warehouse. When a shipment comes, the user will specify which warehouse it will be stored at. Likewise, when a shipment is about to leave the company, the user will specify which warehouse it will be taken from. Should be easy enough.

I've worked with join tables before, I had the classic Product >---< OrderItem >---< Order situation. But this is a bit different. With orders, when a new order came, I just created the OrderItems, set their amount and price values, and then left them lying in the database so that reports could be printed based on them.

However, when a certain amount of a product arrives in a certain warehouse, I can't just create a new StockInWarehouse record. What if there already is one? In that case, I should just increase the amount value for that record. Otherwise I would end up with multiple entries, each stating something different about the actual amount of stock in a given warehouse.

I can't quite figure out how to write a script that would do this. Any advice would be appreciated.