1 Reply Latest reply on Oct 14, 2014 10:02 AM by philmodjunk

    ExecuteSQL or Count Calculation Issue, PLEASE HELP!



      ExecuteSQL or Count Calculation Issue, PLEASE HELP!


      I'm trying to create a function that will count the number of records with a specific value in a related table, but only for records in the related table that are related to the record the calculation is in. I've tried using the ExecuteSQL function but I can't successfully create a relationship between the two tables within the SQL code that works. I've managed to count the number of records with the specific value, but it is always for all of them and not solely the ones that are related. Below is the function that I think should work based I what I've found online, but the result is always " ? "

      ExecuteSQL ("select count (a.Indication) from Training_Aids a JOIN Sessions b. on a.fk_Session_Serial# = b.pk_Session_Serial# where a.Indication = ? " ; "" ; "" ; 1)

      I have also tried creating a SelfJoin within the table that holds the records that needs to be counted and then doing a count within the SelfJoin table for the field I want. However, with the Count function there is no way (that I know of) to distinguish a specific value to count (like the CountIf function in Excel).

      If anyone could provide any sort of help, I would be most appreciative! Thanks!

        • 1. Re: ExecuteSQL or Count Calculation Issue, PLEASE HELP!

          You probably have a name in your SQL that needs to be enclosed in "Quotes". Best guess is the fields with # as SQL is much more picky about what characters can be used in field and table occurrence names. Many developers just quote everything:

          ExecuteSQL ("select count (a.\"Indication\") from \"Training_Aids\" a JOIN \"Sessions\" b. on a.\"fk_Session_Serial#\" = b.\"pk_Session_Serial#\" where a.\"Indication\" = ? " ; "" ; "" ; 1)

          Of course a simple typo that misnames something could also be at fault.

          To use the count function instead of SQL, you'd need a relationship that only matches to the records you want to count such as this relationship:

          Sessions::pk_Session_Serial# = Training_Aids|value1::fk_Session_Serial# AND
          Sessions::constOne = Training_Aids|value1::Indication

          constOne would be a calculation field that always evaluates as the number 1 and Training_Aids|value1 would be a new Tutorial: What are Table Occurrences? of the Training_Aids table.

          Then Count ( Training_Aids|value1::fk_Session_Serial# ) would return your value.

          If you only need to display the count and not use it in other calculations there are ways to set up one row filtered portal with a summary field to get the same selective count.

          will return the desired count.