5 Replies Latest reply on Mar 31, 2014 11:37 AM by philmodjunk

    Using Found Count in One Table in Calculation in Related Table

    sccardais

      Title

      Using Found Count in One Table in Calculation in Related Table

      Post

           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.

            

        • 1. Re: Using Found Count in One Table in Calculation in Related Table
          philmodjunk

               Get ( FoundCount ) returns the found count for the current layout in the current window. As long as your script assigns the value to a global variable while on the layout with the desired found count, the method that you describe should work for you. If I am correct that we have previously discussed this, it does not appear that this function is the source of your trouble. Either your script is not finding the correct set of records or the error lies in another part of your system.

               I'd look at how your script finds the records you want to count with this function and the layout that is current at the time you assign this value to a variable.

          • 2. Re: Using Found Count in One Table in Calculation in Related Table
            sccardais

                 OK. The script now sets $$SurveyFoundCount before leaving the Survey's layout but when I switch to the Issues layout (Ranking of Issues), the count of Issues is based on all records in Join_Issues_Mentioned instead of only those from PriorityMarkets.

                 I tried two ways to switch from Surveys to Ranking Report in Join_Issues_Mentioned.

                 After setting the global variable

                 1. Script step Go To Layout Ranking Report

                 2. New Window, Go To Layout Ranking Repor

                 In both cases, the count for each Issue shown on the Ranking Report was the total for the entire Join_Issues_Mentioned table, not just the count of Issues from Surveys from PriorityMarkets. The good news is that the FoundCount from Surveys was correct. :)

                  

            • 3. Re: Using Found Count in One Table in Calculation in Related Table
              philmodjunk

                   But with a global variable, its value will not change just because you change layouts. Neither will opening a new window change the value. I would check to be sure that the calculations in question actually refer to the global variable and not some other value.

                   I'd also check for any script triggers tripped by the change in layouts that might perform a script that modifies the value of the variable.

              • 4. Re: Using Found Count in One Table in Calculation in Related Table
                sccardais

                     OK. Here's what I'm doing.

                     Starting with all records found in both Surveys and Join_Issues_Mentioned

                       
                1.           Clear the variable $$SurveyFoundCount
                2.      
                3.           Go to Survey Layout with fields from both Surveys and Issues_Mentioned
                4.      
                5.           Enter Find Mode [Restore] Find Surveys from PriorityMarkets AND with anything in Issues in Join_Issues_Mentioned
                6.      
                7.           Perform Find [Restore] Find Surveys from PriorityMarkets AND with anything in Issues in Join_Issues_Mentioned
                8.      
                9.           Set variable $$SurveyFoundCount
                10.      
                11.           Refresh Window
                12.      
                13.           Go to Layout "Ranking Report"
                14.      
                15.           Enter Browse Mode
                16.      
                17.           Sort Records by Sub Summary Criteria

                     When I go to the Layout "Ranking Report" (step 7), the count of Issues for "Complex" is 106 - the total number of records in Join_Issues_mentioned with Issues in Issue field. It should be (95).

                     I checked the formulas and I can make this work manually but I can't get the script steps to work as described above.

                • 5. Re: Using Found Count in One Table in Calculation in Related Table
                  philmodjunk

                       Right at step 6, add in a pause/resume script step and check to see if the correct found count has been pulled up by your scripted find. Normally, one doesn't specify find criteria by entering find mode and then again with perform find. The criteria in enter find mode are being replaced by those specified in Perform Find so it's possible that your script is not finding the records that you expected.

                       PS. I recommend using the finds found in the following link as a model for scripted finds. It's much easier to review the criteria specified for the find when you open up the script in the script editor: Scripted Find Examples