How to auto update records when change is made from another table via Portal?
This question is based on a Simple POS (Point of Sale) System that I'm trying to design for my small shop.
Current Setup: (Table and Fields)
- Date Table: (User, Date)
- Sales Table: (Item, Code, Price, Qty, Total)
- Products Table: (Item, Code, Price, Qty, Total)
In Date Layout mode there is a Sales Portal that links to Sales Table so a user can enter date and start the sale that day. I'm currently stuck with the Products table: see my goals below.
- Users can only sell Items from the Products table.
- When an Item is entered in Sales Portal the other fields (Code, Price, etc...) will auto enter based on what product that is in the Products Table.
- When an Item has entered in Sales Portal, that Item in Products table will automatically updated (reduced quantity, total, etc…) based on the sale.
My Questions: (How to do list below?)
- In Sales Portal, user can only sell items from Products table.
- When an item is added in Sales Portal, that item in Products Table will auto calculate the remaining quantity, etc…
- When new date is created, Products will stay the same but Sales Portal will reset.
I hope what I mean is clear, please me to make things more clear if you’re confused.