4 Replies Latest reply on Jan 9, 2015 7:49 AM by BorisKamp

    Calculation using summary fields from different tables

    BorisKamp

      Title

      Calculation using summary fields from different tables

      Post

      Hi!

      I have two tables:

             
      1. Monthly Financials
      2.      
      3. Property taxes

      Both have a summary field that with a total of the amounts (Monthly Financials::Total NOI & Property Taxes::Total)

      Both tables are displayed in a bigger picture with portals next to each other, so both summary fields are on the same layout as well.

      Now I need another field on this layout that will do this 'Monthly Financials::Total NOI - Property Taxes::Total'

      In other words, substract the total of taxes (one table) from the total of income (other table).

      But I can't seem do do that! it gives me wrong weird numbers I can't trackback at all the whole time! what am I missing?

       

        • 1. Re: Calculation using summary fields from different tables
          philmodjunk

          From the context of your layout, the summary fields show totals based on the group of records displayed in your total. When you define your calculation, the table in which you define the calculation field and the context you select for it in the context drop down found in specify calculation determine how the totals calculate. In addition, if your portals are filtered, this affects the totals shown in any summary field displayed inside the portal, but the filter has no effect on the total computed in the calculation field.

          So in what table did you define this calculation? What context did you select from the context menu?

          Or is it a step in a script?

          And two portals side by side indicates that you have three table occurrences linked in two relatioships--one for each portal. What relationships have you set up?

          • 2. Re: Calculation using summary fields from different tables
            BorisKamp

            Ah sounds difficult to me at first but I think I get it after reading it a few times (-:

            However, Im not able to resolve it by myself, here's more info:

            The DB exists of 25 tables in total, where only the following three matter for this question:

                   
            1. Properties (with __pkPropertyID)
            2.      
            3. Monthly Financials (with _fkPropertyID)
            4.      
            5. Property taxes (with _fkPropertyID)

            - They are linked trough their pk's and fk's. and set up to create and delete records in table 2 & 3 trough this relationship.

            - Tables 2 & 3 appear in separate portals on a layout based on table 1 (a property).

            I think this clarifies your question towards me? please let me know if you need anything else! Thanks Phil! Really appreciate your help as usual!

            • 3. Re: Calculation using summary fields from different tables
              philmodjunk

              A calculation field defined in Properties with Properties selected in the Context menu at the top of Specify Calculation dialog should then compute the correct result with one of the following two options:

              Monthly Financials::Total NOI - Property Taxes::Total

              or you can use:

              Sum ( Monthly Financials::FieldSummedBYTotal NOI here ) - Sum ( Property Taxes::FieldSummedByTotalHere )

              But both of these calculations assume that you are not filtering your portal with a portal filter expression as they directly access the related data without any interaction with your portals. (You could delete the portals from your layout and these calculations would still evaluate the same.)

              So if you are using a filtered portal, you will have to use a different calculation that replicates the "logic" of your portal filter expression.

               

              • 4. Re: Calculation using summary fields from different tables
                BorisKamp

                Ahh like that!

                Thanks! all is resolved nowlaugh