3, Creating a new record in Sales with a new date should result in a portal that is initially empty, but you haven't indicated what fields are used as match fields in your relationships so my guess that you are matching records by date may not be correct.
It looks like you are missing a table here.
Most POS systems work from this basic structure (often with many enhancements added:)
Each record in Invoices represents one customer receipt for one customer sale. The LineItems table lists each item purchased on a given receipt and fields in LineItems use auto-enter options to "look up" data from products such as the unit price for each product.
For inventory control, thie LineItems table can be set up as a "transactions ledger" that logs each change in inventory so that a total on hand can be computed. See this thread for more details: Managing Inventory using a Transactions Ledger
Thanks PhilModJunk your relationship solution Invoices----<LineItems>-------Products works great (I re-structured my database and relationship). Now I'm puzzled with another problem. In Products I have fields "Items", "Price", "Quantity", "Total Price". Then I created another field called "Remain" that will show how many Item remains after an Item has been added in LineItems Portal. The formula that I used is just a subtraction of the Quantity in Products table with Quantity in LineItems. So (Products::Quantity - LineItems::Quantity = Productions::Remain). The calculation works but just wouldn't update normally. When an Item is added in the LinesPortal, the remaining items in Productions won't update unless I re-open the calculation formula window or restart filemaker.
How can I solve this problem?
Why would you have fields for Quantity and Total Price in the Products Field.
The method that you are using is not the "inventory ledger" method that I recommended in my first post.
My best guess is that you have taken a number field and defined an auto-enter calculation for it in Field Options. This field should be defined as a field of type calculation not an auto-entered calculation.
Yes Phil, I do have a field with auto-enter calculation, this is so that users can select the field and a list of Items will drop down, select one of the item and the rest of the fields in the portal are automatically filled according to the relationship except the quantity field. Then they will enter the quantity of that item and the Total will be calculated.
The reason why I have fields for Quantity and Total Price in the Products Field is that I want to keep track of how many Items left in my Products after a sale and what is their current value (for easy stock taking in the future).
It might not be the inventory ledger solution but it certainly is for inventory(products) we just might have different requirements.
Thank you very very much Phil, I will try and work around with the auto-enter calculation problem atm.
God Bless you.