2 Replies Latest reply on Feb 16, 2017 5:25 PM by philmodjunk

    Sum of field on filtered records?

    ezeitgeist

      What is the best way to get a sum of a field across records that I want to filter first?

       

      I am creating an expense log. Each record is a different expense. Each Record has a specific User_ID field in it. Each record also has a specific date. Say I wanted to get the sum of all the expenses from a specific user over the course of a specific time. How would I do that?

       

      I was thinking a calculation field. What would I calculate exactly? I'd need to filter by User_ID, then by between a Start_Date and an End_Date (which I do already have fields for), AND THEN sum them.

       

      This may be a simple thing, I've just never done it before. Can I do it in a Calculation or is this something where it is really a single row filtered portal that shows a sum?

       

      Thanks!

        • 1. Re: Sum of field on filtered records?
          Philip_Jaffe

          Option 1:

          Create a Summary field in your Expenses table that is a SUM of your Expenses field.

          Go to a layout based on an "Expenses" table occurrence.

          Place the new summary field on the layout somewhere (likely in a sub-summary part set to show when sorted by User_ID

          Do a find for a User_ID AND within your chosen date range (eg. 1/1/17...2/28/17).

          Sort by User_ID

          Your summary field should give you the total.

           

          Option 2:

          Create a relationship from your user table to your expenses table where:

            (USERS)                                  (users_EXPENSES)

             User_ID (pk)             =         User_ID (fk)

             StartDate (global)     ≤         Expense date

             End Date (global)     ≥          Expense date

           

          Create a calculation field of type number in the USERS table with this formula:

          Sum ( users_EXPENSES::Expenses )

           

          Place this calculation on a layout based on the USERS table occurrence.

          (optional): Place a portal on this layout based on the users_EXPENSES table occurrence.

          Place your global start and end date fields on the layout.

           

          Your calc field will give you the total

          • 2. Re: Sum of field on filtered records?
            philmodjunk

            In addition, ExecuteSQL could be used to calculate such totals or a filtered portal with a summary field in the portal could be used.