Okey, sorry, if I made you complicated.
First you have parts table, which stands as absolutely MASTER table to store the unique list of the parts you have.
Then if I were you, I will generate the child table to record the stock.
I m not sure if you are happy with just ADDING the stock level, but i assume sometime you need to decrease the number of the stock when it is consumed.
So for the child table to manage the in/out for the stock, I will place the column of
PartID : Ref type to lookup the parts table
In or out : Enum type if IN the stock is added, if OUT the stock is subtracted.
Amount : record the number of stock in or out
Then on the parts master table, you place the physical column to record the INITIAL number of the stock you have for each items. This is hard coded.
Then you create the virtual column to calculate the current stock level.
[initialStock] + sum (select(history[newStockNumber],and([partsID]=[_thisrow].[partsID],[In or Out]=“In”))) - sum (select(history[newStockNumber],and([partsID]=[_thisrow].[partsID],[In or Out]=Out")))
This is super simple math. For each parts items, you have initial stock level as numeric number. Then when you add the new child row which is record of IN/OUT of your stock, then virtual column will dynamically calculate. Initial value to sum of the set or row which is flagged with IN. In means the stock level is going up. So add.
Then when the flag is “out” then subtract.
Above expression is combining those and dynamically calculate the current stock level for each item all the time.