1 Reply Latest reply on Jan 30, 2015 2:05 PM by philmodjunk

    How do you make calculations from specific field values?



      How do you make calculations from specific field values?


      TWO questions from a newbie to Filemaker Pro.


      Question 1:


      How do I write a formula that performs a calculation on only one type of field content? For instance, in a field that I call, "unit_type," I have many text values (pool, riffle, glide, rapid, cascade). I only want to make calculations from the "pool" data. The text values are not from a value list that I created. They are what I've entered into the "unit_type" field over time. 


      I already know how to use the "find" button to find all of the records with "pools" in that field, but I want to write it into a formula (preferred) or a script so I can create a more complex calculation for a summary page. 


      Question 2: 


      This is a question about using the Summary function when setting up fields. How do I set it up so that I can find running totals of data of a field in a set of records while simultaneously finding running totals of only a specific value in that same field in the same set of records? For example, I want to find the total unit area of all of the stream units I monitored while at the same time finding the total unit area of only the pools. I have a field called "unit_area" and a field called "unit_type." I created a field called "Total_Survey_Area," which is a running total of all of the unit areas, but how do I create another summary field that only grabs that same info from pools (without having to to "find" all the "Unit_type" fields that say "pool")? I need this pool subtotal to complete another calculation, and ultimately, for a report. That being said, it doesn't need to be a field that is shown on my layout. 


      Thanks for the help!


        • 1. Re: How do you make calculations from specific field values?

          The fact that you did not use a value list may create issues with your data if you have accidentally introduced some data entry errors into this field.

          Some types of aggregate calculations, Sum, Total, average, Standard deviation, Minimum, Max... can be applied to a group of such records by defining a summary field and setting up a summary report where you sort records on this field to group all the records by this value and use a sub summary layout part to show the sub total, average, etc for that group of records. Depending on your layout design, you can list the individual records that make up that value or just have one row of data for each possible value in this field with a sub total for each.

          Here's a tutorial: Creating Filemaker Pro summary reports--Tutorial

          Another approach is to use a relationship that matches to all records with a specific value in this field. That relationship can be a self join that matches the current record to all other records with the same value in this field. Or you can set up a separate field where you select a value and then the relationship matches to all records of the specified value. Either approach enables you to set up calculations (in fields, conditional formats, hide object when, script steps...) that reference data in the related group of records.

          A third approach is essentially the same as the second, but you manage the relationships via SQL in an ExecuteSQL() function call.