1 Reply Latest reply on Nov 18, 2011 10:30 AM by philmodjunk

    Count of specific values in a field in IWP



      Count of specific values in a field in IWP


      hi, thanks for your time


      i use IWP to distribute my solution, which has a large impact on my question.


      i have a TO with a field ANSWER.  four possible values ABCD

      i want to simply get a total of the count of each of these values for the found set.

      12 records had A, 4 had B, etc


      i am currently doing the following:

      cCase ANSWER A = Case (ANSWER = "A" ; 1 )

      sTotal ANSWER A

      c%ANSWER A = sTotal ANSWER A / FoundCount


      to get total answers A and % of results answering A.

      doing this for each option ABCD


      for each "question" for which there is an ANSWER field.  44 in this case.  yuck.


      is there a better way?


      thanks again!


        • 1. Re: Count of specific values in a field in IWP

          Possibly. The fact that you are using IWP prevents us from using the best option, which is to set up a summary report with sub summary parts containing a summary field. That approach allows us to use one summary field to count up any number of different values and it automatically adjust whenever the need arises to add, remove or change values...

          Since we can't do that, an alternative approach is to use a relationship with or without a filtered portal to count the records with each value.

          OPtion 1:

          Set up a self join that matches on the Answer field and use count() or a "Count of" summary field to count the number of related records:

          YourTable::Answer = YourTable 2::Answer

          Create YourTable 2, a new table occurrence, by selecting YourTable in Manage | Database | relationships and clicking the duplicate button. (Button has two green plus signs.)

          Count ( YourTable 2::answer ) will count the number of records in your table with the same exact value in Answer.

          A summary field that counts the Answer field can be added to your layout (take it from YourTable 2, not YourTable) and it will return the same count.

          Option 2:

          Define a new table where you create one record for each possible answer. YOu might have this already as this is one way to set up a value list of answers. Use it in the above relationship in place of YourTable to get this relationship:

          AnswersTable::Answer = Yourtable::answer

          Both a calculation field using count defined in AnswersTable and a summary field defined in YourTable can count your records

          Option 3:

          Define this self join relationship:

          YourTable::anyfield X YourTable 2::any field  (It does not matter what field you specify as long as you select X instead of = as the operator.)

          Set up a one row, filtered portal for each possible answer value. If "Yes" is a possible answer, the portal expression would look like this:

          YourTable 2::Answer = "Yes".

          Then the same summary field described earlier can be put in this portal to show the count. Once you've created one such portal, you can duplicate it and just update the portal expression to filter for different possible answers.