related.countIf ( )

Function stats

Average user rating
5.0000
29
199
9999
Support
FileMaker 8.5 +
Date posted
01 February 2009
Last updated
28 February 2009
Version
1.2 + View history
Recursive function
No

Author Info
 Genx

18 functions

Average Rating 4.1

author_avatar



 

Function overview

Prototype

related.countIf  ( relatedField;   test;   dataType )


Parameters

relatedField  The related field you would like to perform the test on


test  A test to be evaluated. All strings should be quoted


dataType  Your value data type (one of number, text or date)


Description

Tags:  Simulation   Related   List   Excel   CountIf   Count  

Counts the number of related records that match a given criteria.

See the required List.filter function for more information on the tests that can be performed.

If you want to use this in a version lower than 8.5, see the FM8List function as an alternative to "List".

Note: When evaluating against strings, you must remember to quote the strings, e.g. realted.countIf ( Related::Field ; "value = \"Fail\" OR value = \"UG\"" ; "text" )

Examples

Sample input

realted.countIf ( Assignment_Marks::percentage ; "value >= 70 AND value < 80" ; "number" )


Sample output

6 (returned where 6 of the records in the percentage field in the Assignment_Marks table occurance have a score between 70 and 80)

 

Function code

ValueCount( list.filter( List(relatedField) ; test ; dataType ) )

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/199

    Prototype: related.countIf( relatedField; test; dataType )
    Function Author: Genx (http://www.fmfunctions.com/mid/29)
    Last updated: 28 February 2009
    Version: 1.2

*/
// ===================================

 

Comments

jfxrsch
24 March 2009



I need to count all the records in Field1 till current record. My input is :
related.countIf (Field1;"abs(Field1)>0"; "number" )
I expect a result of total number of records that meat the criteria, but the output is always "1" when criteria meets.
Did I do anything wrong in my input? or this function has to be used to count Fields in other related table? Is there a way to count Fields in current table?
Thanks!
     Feature request
Genx
25 March 2009



Try: realted.countif ( Field1;"abs(value)>0";"number")
  General comment
jfxrsch
25 March 2009



Thanks for reply! I tried your suggestion, the result is still the same-- when meet criteria, it shows 1, when not meet, it shows 0. I tried to count in both current table and related table, either result shows a counter of 1, 2, 3, 4....
Any suggestion?
  General comment
jfxrsch
27 March 2009



Hello Genx, I still can get the function working properly. I also get similar problem with related.sumif. Instead of sum the values that meet the condition, it copies the values that meet the condition. Do I need any special setting on my data to make it working?
  General comment
jfxrsch
27 March 2009



Here's a picture to explain my problem:
http://picasaweb.google.com/jfxrsch/ScreenCaptures?feat=directlink
  General comment
Genx
27 March 2009



Per the function name you're meant to use this on related fields :)
  General comment
jfxrsch
27 March 2009



I see! I changed to count related field, it works perfectly now. Thanks a lot!
  General comment
Ben
07 October 2009



Hi,

I've have been having problems with this function. I think it is me being stupid, here is my problem.

I have a Field called "SAT 1" the only values allowed to be inputted are "E" and "L". I want to do a CountIF like in Excel that counts the number of records with "L" in the Field "SAT 1".

Is it possible with this function, if so how.
  General comment
Genx
07 October 2009



Ben,

It will work but only in a related context. Meaning if Sat 1 was a field in a related table occurrence (e.g. "Related") then you would do as follows:

realted.countIf ( Related::Sat 1 ; "value = \"L\"" ; "text" )
  General comment

 

 


Login or register to comment

Create a new account with fmcustomfunctions.com or login to post a comment.

 

 

 

Related Functions

Required functions
list.filter () Iterates over each value in the input list passing them to the test to be...
FileMaker is a registered Trademark of FileMaker Inc. FMfunctions.com is not affiliated with FileMaker or FileMaker Inc.
Your use of this website is subject to our Terms of Use and Privacy Policy