1 Reply Latest reply on Jun 11, 2010 4:50 PM by philmodjunk

    Sum field where another field = x

    awooles

      Title

      Sum field where another field = x

      Post

      Hi,

       

      I'm an intermediate level user, with Filemaker Pro 11, running on Windows Vista.

       

      I'm trying to create a calculated field, which is the sum of instances in another field where a third field equals a defined value. It's in a Budget table (named Budget), where I've got different 'pots' of money that I need to keep track of, but I want to sum it all up so I can tell how much I've got left.

       

      I've got a value list for Categories, with values Category1, Category2, up to Category 4. These are the different pots of money. Every time I assign a cost to a task, I allocate it to the appropriate Category. This is stored in a field called Budget_Location.

       

      I also keep track of what the Estimate is, versus the Actual cost once we get an invoice.

       

      I'd like the summary field (which should total the Estimated Spend in a given Budget Location)  to be something like this, but my programming language is failing me (I'm defaulting to Basic!):

       

      Estimate_Category1 = sum (Budget::Estimate) where Budget_Location = "Category1"

       

      I've tried an IF expression, but it returned nothing at all. Any help would be much appreciated!

       

      Andrea

        • 1. Re: Sum field where another field = x
          philmodjunk

          Instead of a calculation field, you might want ot set up a summary report that groups your records by category and computes a sub-total for each. If you haven't set up summary reports before, you might want ot look at this link: Here's a link to a simple tutorial on setting up summary reports that you may find useful:

          Creating Filemaker Pro summary reports--Tutorial

           

          That approach may or may not be applicable depending on how you've set up your database.

           

          How many tables do you have and how are they related? Your example of sum ( budget::Estimate ) suggests you might have two tables linked by a relationship but I would need to know more before I can suggest anything more.