AnsweredAssumed Answered

Sum of field in join table depending on both parent tables

Question asked by ErikO on Mar 31, 2009
Latest reply on Mar 31, 2009 by comment_1

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?



Outcomes