ListView Running Total based on parent table
I have 2 tables.
1)Table A (Parent Table) - This is an Instruction Table
2)Table B (Child) - This is for stock
There is a repationship with between both tables using Table A's order number to Order Number(foreign key).
A user goes into table A the instuction table and enters a Total Order Number Lets say: 15
Then user than goes into Table B which displays the full list of stock, 1000 records, finds the correct one and enters the foreign key into the foreign key field which is also the order number, this is basicially saying this line is now associated to the parent client, this links the total number 15 to this line, and I have a calculation to say there are 14 available for this order number.
User finds another line item and assigns it to the same order number. Currently I have 5 assigned to that order number therefore there are 10 available. The 10 is displaying on every record line in table B.
what I am trying to achieve is for a running total be to displayed so when user assigns a record to the order number it shows 14 on the first one assigned. 13 to the next, 12 to the next and so on and so on.
How can I achieve this?
I think that you would be better off using a portal to list the items ordered and to specify the quantity of each ordered item. This would use three tables where you currently have two.