Title

Post

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

Each Project contains three fields in question: proj_statusproj_billable_totalproj_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?

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.

Define two calculation fields in Projects: proj_active_billable_total and proj_inactive_billable_totalHere'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.

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:

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

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.

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.