1 Reply Latest reply on Aug 29, 2014 12:16 PM by philmodjunk

    Calculation / Summary help



      Calculation / Summary help


           I am trying to calculate a field occurrence in conjunction with another field occurrence.

           For instance, I have a "Status" field with a check mark option of "Active"

           and a "Study" field with a check mark option of "yes"

           I want a count of all patients that both have a status of active and a yes to study.

           Every time I do a calculation field and use the count function, it only allows me to enter one field and no parameters.

           Do I need to use ExecuteSQL? and if so, help with the SQL code to write.

           I want to display this ever changing count on an admin layout. help?


        • 1. Re: Calculation / Summary help

               When you use an aggregate function such as Count (or Sum, average, standard deviation...) it computes a total from the set of all related records. If you want a total of all patients with the value of "Active" and "yes", you would need to use a relationship that matches to only the records with those values. Such a relationship is possible to set up if you use a pair of calculation fields as match fields so that you get relationship that only matches to records of those values.

               But that's not the only way you can get that figure. You could also perform a find on your patients table specifying those criteria and your found count would then be the figure that you need.

               And yes, executeSQL is one option for getting a selective count of the records in a table.