### Title

Sum of field in join table depending on both parent tables

### Post

I have the common case of two tables called Invoices and Products.

Invoices and Products have a many-to-many relation. The are connected via a Lines table. Lines have foreign keys from both Products och Invoices.

Lines has a field called LineTotalPrice (which is the sum of the Line fields LineQuantity and LinePrice)

Products has a field called "Tax rate"

Now I want to add a calculated field in Invoice that sums up LineTotalPrice for all Lines that matching a certain Invoice AND matching Products having a certain tax rate (say 25%).

How to do this? I guess I have to add a constant "25" somewhere and at least one more table ocurrance of Lines or Products but I can't get it right... Can someone help me?

Ok, but let's say its another property of Products that won't change, for example a weight. How do yo do then?

Another question, a field of type calculated, when is the calculation triggered? It seems to me that changes on a fields in the same table included in the calculation is reflected immediately, but fields inculded in the calculation from another table will not always trig a new calculation, sometimes you have to click on the filed to get the new value. Why is that? And whats the differnece on the filed type calculation and a numeric field in which you input a calculation in the properties?