AnsweredAssumed Answered

Using Found Count in One Table in Calculation in Related Table

Question asked by sccardais on Mar 31, 2014
Latest reply on Mar 31, 2014 by philmodjunk


Using Found Count in One Table in Calculation in Related Table


     I want to use the found count of a set of record in one table as part of a calculation in another but the found count from the second table is replacing the found count in the first.

     I've tried several approaches that work sometimes and not others. Can't explain why.

     What I want to do:

     Find a group of records in Table 1. Store the Found Count so it can be used in a calculation on a Sub Summary report in Table 2.

     The field in the Table 2 Sub Summary report is a calculation field based on Summary fields using the Get Summary functions. For each grouping of records in Table 2, I want to divide the found count of records in Table 1.

     For example,

  •           assume my found set in Table 1 has 350 records with at least 1 related record in Table 2.
  •           In Table 2, Issue X is mentioned 100 times.
  •           Therefore, Issue X represents 28.57% of all the records in Table 1 with at least 1 Issue. e.g. "Of all the Surveys from this group in Table 1 that had any issues whatsoever, issue x in Table 2 represents y %"

     I use a calculation field in Table 2 to calculate the percentage. Formula: GetSummary(Summary_Count_Issues;Issues Mentioned)/$$SurveyFoundCount

     A script sets the value of $$SurveyFoundCount

     Set Variable[$$SurveyFoundCount;Value:Get(FoundCount)]

     Refresh Window[]

     I need to hold the correct number of Issues in Table 2 and divide each row in the Sub Summary report by the correct Found Count in Table 1. 

     What is the best approach to this?

     Note: I'd like to make this process generic so it works based on any found set rather than hard coding one specific set.