3 Replies Latest reply on Mar 31, 2009 2:33 PM by comment_1

# Sum of field in join table depending on both parent tables

### 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?

• ###### 1. Re: Sum of field in join table depending on both parent tables
It will be easy once you lookup the tax rate into Lines - which you should do anyway, because tax rates change.
• ###### 2. Re: Sum of field in join table depending on both parent tables

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?

• ###### 3. Re: Sum of field in join table depending on both parent tables

ErikO wrote:

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

The simplest way would still be to lookup the value. Because you want to group Lines by a common attribute of tax rate - but they have no way of "knowing" that Product A has the same tax rate as Product C (at least not a simple one).

Another option: define a relationship to Products (from Invoices), matching on rate. Get a list of matching ProductID's into a calculation field. Now you can define a relationship to Lines, based on matching both InvoiceID and product.

There are other ways, such as using a custom function, or defining a rather complex set of relationships - but I won't go into that.

ErikO wrote:

a field of type calculated, when is the calculation triggered?

It depends on the type of calculation. A stored calculation recalculates when one of the referenced fields is modified. An unstored one happens when the window is refreshed (or, sometimes, when the join results cache is flushed).