Objective:
Orders will automatically update stock levels accordingly.
Stock levels do not require an order to change (eg, withdrawing stock for internal use, or adding stock when new stock arrives at a warehouse) ad so manual entry needs to be possible to update stock levels
Products can exist without stock (stock levels)
Stock movement can exist without orders (manual adjustments) but cannot exist without Products
One stock movement record can only be linked to 1 order, and 1 product
A graph view is required to show all products current stock QTY (thinking a graphic view in products, with a formula field that calls on info from stock movement to show the current stock levels - not sure how to calculate this though as it would need to sum all records for that SKU, and those records could be + or - )
There are 2 types of product: bags and resin.
Resin is sold by the kg, and each resin product is made up of 25kg bags.
Bags are sold by the bag, pack, or carton - the number of bags per pack/carton varies by product.
So! Questions:
Is this my best data model to achieve what I want?
If so, how can I calculate current stock based on all incoming/outgoing stock movement per SKU?
Keeping in mind that once this is done I want to try and create a pricing and inventory table where there are various price variations per product (retail, wholesale, distributor, custom pricing for certain customers) which orders would call on (based on select fields for pricing type and the product) and I will also be working on a quoting tool that will call on products and pricing unless its creating a new price sheet which would be determined by a select field
Forgot the inventory table!
Good idea on the interactions! I'll do that now :)
Looks like you're on the right track - how are you representing general stock movements in the new model though (internal use for example)?
When I do a CRM, I normally link what you've called Interactions to other tables - in this case I guess quotes, orders etc. In this way you can add related interactions to any part of the app you need - and I would always link them to the Company as well. See how many links there are to Activities in my KasCur CRM app:
Okay I've made some changes to my data model after taking another look at the Inventory template... based on that I think I might be able to figure out the formula's too
Just realized that model is missing some links:
- one CRM(customer) can be linked to many orders
- 1 person(people) can be linked with 1 address (in the case of shipping address contact)