Stock (parts, batches, invoices) returning wrong calculation results
I’ve got a stock and invoicing (implemented from FM template) database.
1st table is for adding parts;
2nd for adding different batches for parts on 1st table;
3rd is invoice data.
Relationships between three tables are made using componentID fields. Since there are different batches (and different stock locations), there comes a problem on calculating correct stock quantities.
When I have 11 (1st batch 5 pc, 2nd batch 6 pc) pieces of bolts of total and:
sell 2 bolts from batch 1,
sell 4 bolts from batch 2,
it returns correct calculation result – 5 bolts left in stock (11-2-4=5).
But when I want to see how many quantities of different batches is left, it shows in batches table:
batch 1 – 3 bolts of 5 left (5-2=3 – correct)
batch 2 – 4 bolts of 6 left (6-4=2 – incorrect, should be 2 bolts of 6 not 4)
according to this I should have 7 bolts left in stock
batches::Qty_left calculation field uses formula = batches::Qty - invoice_data::Qty_SUM
invoice_data::Qty_SUM calculation field uses formula = Sum (invoice_data::Qty) // it should sum up all the quantities sold from the same batch
I understand that using this relationship the calculation is made only using the first related batch (due to relationship via ComponentID not BatchID) so comes the wrong calculation. Maybe there’s another way other than playing with relationships?
I’ve managed changing the relationships so that the calculation goes correct but that messes up the invoicing, never seem to get all of them working correctly at the same time. Also tried with different table occurrences, no acceptable results.
I hope I managed to describe the problem so it is understandable. Any ideas on how to fix the problem?