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

    ErikO

      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
          comment_1
             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
            ErikO
              

            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
              comment_1
                

              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).