1 of 1 people found this helpful
What about a context free solution?
ExecuteSQL("SELECT COUNT(*) FROM Recaptures WHERE kf_IndividualID = ? AND Distance_Moved > 0" ; "" ; "" ; kp_IndividualID)
The other way you could do it would be to add a filter to the relationship so that you only count records where Distance_Moved is > 0. This would require you to add a calculated global to the individuals table that was a zero constant. Then you could count via the relationship.
Another solution would be to add a calc field to the Recaptures table, say, cHasMoved, defined as Distance_Moved > 0.
Then either create a summary field in Recaptures as sCountOfcHasMoved and display that (related) field in Animals, or create a calc field in Animals as Count ( Recaptures::cHasMoved ).
If you need to not only display the value, but also use it programmatically, consider using a “normal” number field and a trigger to update that field whenever you add/delete a Recapture record or edit a Distance_Moved value. This would also speed up your solution, since an unstored field needs to be recalculated each time it is displayed, while this number field can be stored.
As you can see, lots of options to choose from …
If you take Mike's suggestion and use eSQL(), don't forget to set the field to unstored.
I hadn't thought of approaching it with multiple steps -- thanks!
Thanks for the suggestion. I'll admit I'm not competent in SQL yet but this sounds like a solid approach.
Good ideas, one and all. There's another, much faster option that I'd like to share, compliments of Daniel Wood at Digital Fusion — posted on their Weetbicks Blog back in December, 2010, "A Lightning Fast Alternative to the Count() Function".
If you want to count the number of records by the id(Recaptures)::id_related (Movements) relationship, then add a new unstored calculation field in Movements with "Get ( FoundCount )" as the only thing in the calculation window — no target fields, not the "Count ( )" function… just "Get ( FoundCount )".
Now, in the main table of Recaptures, you can go into Layout Mode, then choose to replace or duplicate an existing field, or add a new field to your layout, but it's this "Get ( FoundCount )" field form the Movements table. It will render the count of all movement records that share the id of the parent Recapture record, upon which the relationship was created. You can also use this calc in most places to replace "Count ( )" fields, and it will greatly speed up your development time, reduce context errors and calc fields, and give you a common way to reach down into your relationships to count related records…but…it's super fast!
- - Scott
Oh…So if you have a parent table related down to Recpatures, say "Animals", and you wanted to track the number of recaptures, as well as the number of Movements — all displayed quickly on the Animal's record, just add the related "Get ( FoundCount )" fields from Recpatures (if you don't have one, then copy and paste the one from the other table) and Movements!