Get count of related records with specific field value

[BrainlessBlock]

Hello,

Here is my scenario. I have two tables. One with dog houses, each record has information about the doghouse, etc. Second I have a table with each individual dog, each dog is linked to a doghouse by a housing ID. Each dog also has a status of "Active" or "Otherwise." What I want is each doghouse to have a count of how many "Active" dogs are linked to it. I can then do searches and calculations based on that.

Is there a way to have a doghouse record value that is a calculation, or will I need to write up a script that is run every time I want a report. 

As a side note I have some 6,000 doghouses and close to 60,000 dogs registered. The faster it works the better.

 

Thanks in advance!

[comment]

You can have a calculation field in the DogHouses table =

ValueCount ( FilterValues ( List ( Dogs::Status ) ; "Active" ) )

However, such calculation will be unstored, so if you plan to "do searches and calculations based on that", you might be unhappy with the speed. If this is for a report, it might be preferable to turn this task over to the script that produces the report.