3 Replies Latest reply on Apr 14, 2014 11:21 AM by craig5005

    Accomplishing a countif type function (my struggles and solution



      Accomplishing a countif type function (my struggles and solution


           I've been reading many forum posts about countif type functions.  Seems like it causes quite a few problems, and I was stuck on this until just recently.  Figured I would explain my situation and how I got it to work.

           FM Pro Ad 13, Win 7

           Multiple tables, but only two are important for this.  Donor and Donation with a donorID - donorID relationship.

           Donors hold the personal demographic information about the donor, and the donation table holds the financial donation information (value$, data received, form, status etc).

           What I would like to accomplish is to count how many donations have been "received" and how many are "pending" based on the donation::status field.  For example, display that there are 19 donations received and 4 pending (usually promised donations that are recorded, or cheques waiting to be cleared/deposited).

           You will see in other forum posts that the solution is to define a calculation field as follows.

      ValueCount ( FilterValues ( List ( Donations::status ) ; "pending" ) )

           But this was returning 0/1 for me (0 if no records matched pending, and 1 if any number of records matched pending).


      The problem was that I was defining this field in the wrong table!!!  I was putting this field in the donations table and placing the field in a layout for donors.  This did not work.  When I finally realized that this field needed to be defined in DONORS table it finally worked!


           I hope this can help other people, I struggled for far too long.  Been out of FM since 11 (or maybe 10) and skills are rusty or just gone.

        • 1. Re: Accomplishing a countif type function (my struggles and solution

               Ok nevermind.... just realised that doesn't necessarily work.


               When these new count calculation fields are placed on a layout from donor table, they display the info for that donor only.  I'm looking to get an overall count for all donors.

               Any help?

          • 2. Re: Accomplishing a countif type function (my struggles and solution

                 There are several different ways to selectively count, sum, average, etc. different groups of records.

                 1) In a summary report, you can sort by the "category" field (your "received" or "pending" field). Put a summary field that counts/sums or whatever you need in a sub summary layout part "when sorted by category", sort your records by that field and you have your sub totals.

            Creating Filemaker Pro summary reports--Tutorial

                 2) ExecuteSQL can produce the same aggregate value for a single category or a list of all categories.

            FMP 12 Tip: Summary Recaps (Portal Subtotals)

                 3) Sorted one row portals can refer to that same summary field with a portal filter that limits the records to just those of the category.

            Sum_Calculation based on condition

            • 3. Re: Accomplishing a countif type function (my struggles and solution

                   Thanks again for your help Phil, always a pleasure.


                   I tried the recommended solutions without much success.  Perhaps the relationships are not set up correctly in my db to properly implement your suggestions.  I also don't have much experience with summary reports and since have started to read The Missing Manual to learn a bit more.


                   The solution I came up with, that works well for my db is to place a button on the main page which starts a script.  The script performs multiple finds for the categories of interest.  I then throw the Get (foundcount) result into global fields and display these numbers in a comment box.  This option introduces an extra click, but I think it should be sufficient for a pseudo "inventory" of various categories.


                   Thanks again for your help.