A real container table: purchase, sales and expenses.
I would like to ask you your feedback.
As some of you know by now from some past post, I am a newbie and trying to put in place a Filemaker Database for a recycle company. We normally purchase containers from oversea. Each container got different items such as: 10 tons of copper, 8 tons of Aluminum, 5 tons of Stainless and 1 ton of electrical motors.
Normally, people will use excel form, named container, which contains the following columns: Date, Description , Purchase Amount, Sales Amount and Other expenses.
So, I broke a part each of them as follow:
Container table (contianer id, container number, description) has relationship with Puchase table (purchase from supplier) , Sale table ( Sold to customer , so and so)and Other expenses table (Cost of transportation, logistic, banking payments costs ...any cost related to each container) .
My question is: How to create an Inventory table that keep the information update with purchase items and sold items. Sometime the container is totally sold, that is very easy. But , MOST OF TIME, CUSTOMERS JUST WANT TO BUY ONLY 5 TONS OF COPPER, AND NOT 10 TONS OF COPPER, OR JUST 2 TONS OF STAINLESS AND NOT 5 TONS. And the same time, there will be another table or layout that will calculate, if there is a profit or not based on Total amount from sale table - total purchase amount - total cost of container)....then, we will know if we made money or we lost.
HOW TO KEEP AN INVENTORY LIST RECORDS UPDATE, AND REMMENBER WITH ITEM ARE STILL UNSOLD FROM EACH INDIVIDUAL CONTAINER NUMBER, SO AND SO, ETC.
I appreciate if ANYONE ..who has faced similar experience or know about inventory list ( purchase and sold items,etc.) may give me some directions.
Thank you in advance.