Counting the number of occurrences of a particular value in a field in another table

[Paradigm]

If it possible I need to come up with a calculation that when a new record is created in Table 1 it counts the number of occurrences of a particular value in a field in another Table 2. In other words when a new record is created in Table 1 I get a calculated value that counts the number of times the value "Active" appears in a particular field in Table 2.

How do I go about doing that?

I know I can get this count by drawing a relationship between the two tables and counting the records that way but I wondering if there is a way to write a calculation that counts occurrences without the relationship. Maybe a custom function?

[Ocean West]

Yes you can use Agnes' Custom List Function http://www.briandunning.com/cf/868

CustomList ( 1 ; Count( keyfield_related_table) ; "ValueCount( GetNthRecord ( status ; [n] ) = "active") )" )

You could accomplish getting totals with out using a relationship but you would have to script a process switching layouts getting found sets or show all record etc. There are several methods that could be employed.

[bruceR]

ValueCount( FilterValues( List( Relation::Field); yourTestValue))