1 Reply Latest reply on Apr 9, 2012 11:20 AM by philmodjunk

    Calculation Based on Certain Group

    DominickReda

      Title

      Calculation Based on Certain Group

      Post

      Hi everyone,

      I have a table, to keep it simple lets say these are the field:

      Table Name - Payments

      Fields:

      • Contractor
      • Payment #
      • Amount This Payment
      Note that there are four different contractors, each with multiple payments.
      I've created a new layout (let's call it "Payment Snapshot") which groups by Contractor and sorts each group by Payment #. I have a total at the end of each group which gives me the total paid for all Payment #'s for each contractor. Up to this point, everything works great. What I'm trying to do is, on the "Payment Snapshot" layout, show a percentage paid to date based on each of their overall contract amounts.
      I'm not sure of where I would specify each of the contractors Total Contract Amounts (as they each differ) in order to make another calculation field to show the percentage. I'm sure this is really basic stuff so I apologize in advance for the question. I've tried for an hour with no luck.
      Thanks,
      Dominick

        • 1. Re: Calculation Based on Certain Group
          philmodjunk

          You need one or more additional tables and you'd store the contracted amount data there.

          The simplest setup (which probably won't work in the long run, but helps me explain what you need and why), is to use this:

          Contractors::ContractorID = Payments::ContractorID

          You'd store the Overall amount contracted in the contractor's table instead of payments and then you can access different figures for each contractor. A calculation defined in payments can use GetSummary to access the payment subtotal so that you can then compute either a percentage or a total figure for that contractor.

          Where this may fail is that the same contractor may contract for more than one project. If so, you'll need a table for your contractors and a table for the contractor's projects or contracts. In the payments table, you'd then need to specify not just the contractor but the correct contract or project record...