5 Replies Latest reply on Jul 24, 2014 10:26 PM by mr_scott

    Creating a Conditional Count

    benchwrm11

      I have a database which contains animal movement data. I would like to create a field which calculates the number of moves each animal makes. The database has an Individual table (450 records), where this calculation would be, and is related to a Recapture table (2500 records) by individual ID. The Recapture table has a record for each time an animal is captured and the distance moved from the last capture. I would like to calculate a field in the Individual Table which has the number of times an animal moved (Recaptures::Distance Moved > 0). I've been trying to find a way of doing a conditional count (for example: Count(Recaptures::Distance_Moved>0), but that doesn't appear to be fair game. This seems like a fairly simple question, but I've been unable to find a straight forward solution. Any help would be much appreciated.

        • 1. Re: Creating a Conditional Count
          mikebeargie

          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.

          1 of 1 people found this helpful
          • 2. Re: Creating a Conditional Count
            erolst

            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.

            • 3. Re: Creating a Conditional Count
              benchwrm11

              I hadn't thought of approaching it with multiple steps -- thanks!

              • 4. Re: Creating a Conditional Count
                benchwrm11

                Thanks for the suggestion. I'll admit I'm not competent in SQL yet but this sounds like a solid approach.

                • 5. Re: Creating a Conditional Count
                  mr_scott

                  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!

                   

                  Best regards,

                  - - 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!