CODEWITHSUNDEEP
filemaker
user2315766
user2315766

Reputation: 1

Filemaker - Summing a field based on another field

In Filemaker Pro 12, I am trying to write a formula for a calculation field that will sum a field in a related table based on another field in that same related table. The normal Filemaker sum equation would look like this:

Sum (Assets::Asset Quantity)

However, I need to specify that only quantities that are related to a field named Asset Type with a value of "Building" will be used to filter the values in Asset Quantity that will be used in the sum.

Upvotes: 0

Views: 9544

Answers (1)

pft221
pft221

Reputation: 1739

There are a couple of ways that you could do this:

A new Calculated field

First, you could add a new Calculation field to your Assets table called, say, Building Quantity, with a Calculated Value of:

If (Asset Type = "Building" ; Asset Quantity ; 0)

And then you can use the sum of this new Building Quantity just like you were using Sum(Assets::Asset Quantity) before.

A new relationship

Second, you could add a new Calculated field to your main table with the value always equal to "Building" and then add a new table occurrence of the Assets table. We'll call it "BuildingAssets" and set the relationship so that your IDs match and also your new "Building" field matches the Asset Type

Summary ID     \____________/ BuildingAssets::Summary ID
BuildingText   /            \ BuildingAssets::Asset Type

Then you will use 

Sum (BuildingAssets::Asset Quantity)

instead of Sum (Assets::Asset Quantity) so that you only pull the Building types through.

ExecuteSQL

Finally, FileMaker 12 introduced the ExecuteSQL step. This may be the most elegant way to do the above because it doesn't involve changing any schema. The statement would be somethign like:

SELECT
  SUM (Asset Quantity)
FROM
  Assets
WHERE
  Summary ID = ID AND
  Asset Type = Building

For more information check out FileMaker's page: http://www.filemaker.com/12help/html/func_ref3.33.6.html

Also check out the FileMaker SQL Sugar ("@") Module for help building queries: http://www.modularfilemaker.org/2013/03/filemaker-sql-sugar/

Upvotes: 1

Related Questions