AnsweredAssumed Answered

Accomplishing a countif type function (my struggles and solution

Question asked by craig5005 on Mar 2, 2014
Latest reply on Apr 14, 2014 by craig5005


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.