Why do you need one record for each individual product sold? What problem does that solve for you?
Such might be necessary for "large ticket" items such as selling cars--where you'd want to record the VIN or other manufacturer supplied serial number for each and every item sold, but when this is necessary, one normally already has the items listed as separate records in the invoice that documents the sale of that item and then there is no need to split a line item for 3 products into 3 separate records.
Some of your products in our product range (let's call them products A) need to be managed on an item per item basis as all they are customed to the need of each customers, much like a car with different options etc.
The rest of our products don't need to be managed at the line item basis.
So you are right, for products A, the items are already listed as separate record in the invoice for their sale and in the purchase order for the supplier.
Im still unsure how to go about this.
I keep working on my database and I think I am nearly there :)
I would love a gentle push in the right direction though. My table inventory is trying to reflect how many items of each products I have depending on the status (Ordered, In Transit, In stock etc).
It is based on the Purchase Order Line Items table. One of the calculation in the inventory table is not returning the result expecting.
Basically I want the calculation to sum the total submitted quantity field of the Purchase Order Line Items tables where the record shows a "Submitted" Inventory Status.
I have tried the calculation " Evaluate ( Case (Purchase Order Line Items::Inventory Status) = "submitted" ; Sum (Purchase Order Line Items:Total Quantity Submitted)) But it does not solve every record where the first condition is true, it seems to only return the value of the first record found where the condition is true.
I can't find the right formula.
Screen shot attached.