4 Replies Latest reply on Sep 14, 2011 12:28 PM by philmodjunk

    Sum of fields based on secondary field

    TimothySlinks

      Title

      Sum of fields based on secondary field

      Post

      I have CLIENTS and PROJECTS tables. Each project is related back to a clientID.

      Each Project contains three fields in question: 
      proj_status
      proj_billable_total
      proj_gratis_total.

      I want the CLIENTS table to have the following calculations:

      Sum(PROJECTS::proj_billable_total) when proj_status="ACTIVE"
      Sum(PROJECTS::proj_billable_total) when proj_status="INACTIVE"
      Sum(PROJECTS::proj_gratis_total) when proj_status="ACTIVE"
      Sum(PROJECTS::proj_gratis_total) when proj_status="INACTIVE" 

       

      I can't find anything online that I can make sense of. Is this the job for a self-join? Do I need two new tables: Active Projects & Inactive Projects?

      Can you get me started? THANKS!

        • 1. Re: Sum of fields based on secondary field
          philmodjunk

          You can't set up the sum function to be "conditional" on a value in the list of related records that it sums. You'll need to select and implement a method that separates these values by their project status.

          1. Define two calculation fields in Projects: proj_active_billable_total and proj_inactive_billable_total
            Here's the calculation for the first field:

            If ( proj_status = "Active" ; proj_billable_total )

            Then Sum ( Projects::proj_active_billable_total ) will compute the total only of the active projects.

          2. Add two new occurrences of Projects to Manage | Database | Relationships.
            Define two calculation fields in Clients, constActive, and constInactive. These will be calculation fields that return "Active" and "inactive" respectively.
            Then you can use your added occurrences to produce these two relationships:

            Clients::clientID = ActiveProjects::clientID AND
            Clients::constActive = ActiveProjects::proj_status

            Clients::clientID = InactiveProjects::clientID AND
            Clients::constInactive = InactiveProjects::proj_status

            Now Sum ( ActiveProjects::proj_billable_total ) will compute just the total of active projects.

          3. You can also use filtered, one row portals (requires filemaker 11) and a summary field defined in projects to report your active and inactive portals. In this case your portal filter expressions filter for active or inactive status and your summary field is a "total of" field you define in the projects table. This approach limits your change to just one added field and no relationship changes, but is best used for displaying the total, it's not necessarily the best option if you intend to use these values in calculations.

           

          • 2. Re: Sum of fields based on secondary field
            TimothySlinks

            Thanks Phil!

            Creating the two occurrences seem to be less klugey than the four if-statement calculations, so I did that and it's not entirely working for me: The Clients::constActive = ActiveProjects::proj_status relationship doesn't seem to work because proj_status is a calculation. Is that right? When I make the proj_status just a normal text field and manually input "ACTIVE" or "INACTIVE", it's fine.

            What's the deal?

            • 3. Re: Sum of fields based on secondary field
              TimothySlinks

              OH... i need to store it, right? How do I do that?

              • 4. Re: Sum of fields based on secondary field
                philmodjunk

                It needs to be an indexed field. If it's a calculation that refers to related records, it will be an unstored calculation and this will not have the needed index.

                I suggest using the third alternative if you are using FileMaker 11.