2 Replies Latest reply on Mar 5, 2014 2:35 PM by AlarS.

# Stock (parts, batches, invoices) returning wrong calculation results

### Title

Stock (parts, batches, invoices) returning wrong calculation results

### Post

Hello!

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.

Example:

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?

Thanks
Alar

• ###### 1. Re: Stock (parts, batches, invoices) returning wrong calculation results

How's that again?

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)

Text in red shows the correct calculation and results, not the incorrect results. Did you mean to say that the second calculation works out as:

6 - 2 = 4? for a total of 7?

You seem to have a correct analysis of why you are getting an incorrect total--the relationship matches to the wrong record and thus you get the wrong calculation results.

But there's no where near enough information about your tables and relationships to be able to suggest a solution in any kind of detail. Best guess from the description is that you are using one relationship between a pair of tables where you need two different relationships matching by different combinations of match fields so that both parts of your system compute correct results. This can be done by adding additional Tutorial: What are Table Occurrences? in order to get more than one relationship between the same two tables.

• ###### 2. Re: Stock (parts, batches, invoices) returning wrong calculation results

Thanks, PhilModJunk!

Yes I explained a little badly, I'm not bending maths I know that 6-4=2 is correct. Yes what I meant to say was that the second calculation works out as 6-2=4 giving me total of 7 which is incorrect.

I'll look into your table occurrences tutorial and give it another try. If that doesn't help then I'll provide you with more detailed information about my tables and relationships.