Looking For Solutions
I am currently building a stock management database for my business using Filemaker Pro. So far I have the following tables:
- Product List
- Order Line Items
- Sales Line Items
- Other Item Transactions
All of the tables are linked together using "Item Name" in the "Product List" table as the relation. Each table has various columns with various data including a column called "Unit Cost". At the moment these columns are just looking up the "Unit Cost" from the "Product List" table using the "Item Name" relation. I have however run into a problem. Many of my stock items are often ordered in at different prices. For example, I may take advantage of a 20% reduction offer from a supplier, or a supplier may introduce price increases. I could have the following situation:
Product A - 20 ordered in @ £6.00 on 4 Jun 2013.
Product A - 80 ordered in @ £4.00 on 9 Nov 2013.
Normally these orders would be entered into the "Order Line Items" table and a "Units Ordered" column in "Product List" table would calculate the sum of the items ordered using "Item" as the relation. There would however be no way of breaking down the figure for units ordered to see how many were ordered at what price. Ideally, I would like to be able to be able to see this breakdown without having to create two variations of Product A in the product list.
When it comes to putting in a sale, I would like to be able to choose which product price to use, and I would like my "Product List" table to show the units on hand of Product A @ £6.00 and Product A @ £4.00.
So if, for example, I sold a Product A which cost me £4.00, the units of product A @ £4.00 would decrease to 79, and once all 79 are sold I would no longer be able to enter any more sales of Product A @ £4.00 in.
Does anyone have any suggestions on how to deal with this problem?