Summing values from records that meet certain criteria

[GR2345]

Hello FM Forums guys and girls!

I recently downloaded the trial of FMP and have quickly found how overwhelmingly powerful it is and so I'm having some trouble completing a seemingly simple task. I am trying to get the sum of numbers in a certain field only for records that meet a requirement like having a checkbox checked, radio button selected, or have a field with a certain value. Since just typing that sentence was difficult I've included an image to illustrate an example. In this example, I would like to add all the unpaid records, resulting in one number that is the total unpaid.

Thanks!

[Raybaudi]

You could use a self-relationship by a global field containing the word "unpaid" and the PaidStatus field, than use a calc like:

Sum ( SelfJoin::Cost )

[Chris Cain]

This may be a case for sub-summaries. This would allow you to show, for instance, all the "Paid" and then the count of the paid records, then all the "unpaid" and a count of all the unpaid records.

First, add a summary field to your table to count the records. I usually count the primary key field, since I know that field's not going anywhere.

Next, add a "part" to your layout, below the body that's a sub-summary, when sorted by your paid status field. Put your count field there.

(Optionally, you can add the same part above the body, and put your status field there as text, so before listing the Paid records, it will show "Paid" as a header.)

With those sub-summaries, any time you sort the records by paid status, you'll see a header before and a count after each group.

There's many useful variations on this. For instance, you might also want a summary field of the Cost, which you can display in your sub-summary. You also might want multiple-subsummaries, f'rinstance by customer or by product. The sub-summaries will only show when you're sorted by the relevant field(s) as defined when you inserted the sub-summary part.

HTH

Chris Cain

Extensitech, Inc.

www.extensitech.com

[GR2345]

Thanks for replying raybaudi and Chris. I think I'm going to try your way Chris, however I am still unclear on part of the solution. I have a total cost field and total record count field (easy enough), but I don't get how you are able to count just paid or unpaid records. If it's easier for your to do it yourself rather than explaining, I have included a stripped down version of the file. If you could get the sum and count for both paid and unpaid records I should be able to deduce what's going on and be able to do it myself.

Thanks Chris!

test1.fp7.zip

[comment]

Try it this way. Note that you could delete the body part from the layout and leave only the summaries.

test2.zip

[Raybaudi]

... and this is an example of my way.

test3.zip

[comment]

It should be noted that the two methods do not necessarily produce the same results. A summary field summarizes the found set only, while the self-join relationship will always "see" all matching records in the table.

And see also:

http://fmforums.com/forum/showpost.php?post/364978/

[GR2345]

Thanks to everyone who responded, I should be able to get something working from your examples.