5 Replies Latest reply on May 12, 2009 1:52 PM by philmodjunk

    Calculations across Related Tables

    keycoachjohn

      Title

      Calculations across Related Tables

      Post

      Hi,

      I'm slooooowly starting to get the hang of related tables.  One current challenge that I cannot find a solution in the current resources (books, forums etc) involves calculating data found across a one-to-many relationship.  I don't think a summary report will work since the calculation is required in tables used further down in the whole DB. 

       

      For example, two tables represent the core of the ER, Table A and B:

      Table A- "Log" with Fields: primary key, serial number (a value list with several serial numbers in inventory), and monthly utilization per serial number.  Each record represents a new utilization input per serial number.  This should be the "many" side of the relationship, but how to create a working aggregate utilization field based upon a serial number active in Table B?

       

      Table B-"Product Status":  Fields for Foreign key from Table A, Starting hours, Total Hours- a calculated field using "starting hours+ (aggregate utilization)

        • 1. Re: Calculations across Related Tables
          philmodjunk
            

          Look up Aggregate functions in the on line help file for a complete list of functions you can use in this type of situation.

           

          In your case, Sum(Table B::Total Hours) will return the sum of the Total Hours field across all the records related to a given record in Table A.

           

          Just place this calculation in the definition of a calculation field in Table A.

          • 2. Re: Calculations across Related Tables
            keycoachjohn
              

            Thanks, your solution works to add the active record in A "monthly utilization" with "starting hours" from Table B. 

            Turns out I didn't ask the original question very well.  Sorry about that.

             

            While in Table B and sitting on a current record tied to one serial number, I'd like to link a summary from Table A of the total "monthy utilization" hours relating to that serial number only and then add it to "starting hours" from B. 

             

             

            • 3. Re: Calculations across Related Tables
              philmodjunk
                

              That can be done, but you may not have to.

               

              Let's say the calc field that uses the sum function in Table A is called "SumHours"

               

              Then you could define a caculation field in Table B with

               

              Table A::SumHours

               

              as its expression.

               

              If you only need to display this total (not use it in other calculations in the related table), you can simply place the SumHours field from Table A on your Table B layout or in a portal that displays records from Table B.

              • 4. Re: Calculations across Related Tables
                keycoachjohn
                  

                You've been quite helpful, thanks.

                Almost done with this thread, but I'd like to better understand your note that I cannot use TableA::Sumhours in a calculation within a related table.  It presently works as an argument of a calculation field in TableB; so are you suggesting that another related one-to-many table downstream of table B cannot use this calculated field?

                • 5. Re: Calculations across Related Tables
                  philmodjunk
                    

                  keycoachjohn wrote:

                  Almost done with this thread, but I'd like to better understand your note that I cannot use TableA::Sumhours in a calculation within a related table.  It presently works as an argument of a calculation field in TableB; so are you suggesting that another related one-to-many table downstream of table B cannot use this calculated field?


                  I thought I said the opposite. You can create a calculation field in Table B that refers to the SumHours field in Table A.

                   

                  I did say that you might not need to. It depends on what you need. If you're just going to display the value on a layout, take the original field, TableA::SumHours and put it on your layout and you won't need to create an extra calculation field in Table B. You need to put a calculation field in Table B that refers back to the parent table only if you need to create a new calculation within Table B.

                   

                  Example 1: If you want a record in Table B that computes Percent of total hours for the current related record, you might use:

                   

                  TotalHours/TableA::SumHours

                   

                  In a calculation field in Table B

                   

                  Example 2:  If you want to simply display the SumHours value on your layout, use the field tool to place a new field on your layout and choose TableA::SumHours. You can even place this field in a portal set to display records from Table B.

                   

                  Hope that clears things up.