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.
~~~~~~~~~~~~~~~~~~
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
1
Summary ID \____________/ BuildingAssets::Summary ID
BuildingText / \ BuildingAssets::Asset Type
Then you will use
1
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:
1
SELECT
SUM (Asset Quantity)
FROM
Assets
WHERE
Summary ID = ID AND
Asset Type = Building
