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.