6 Replies Latest reply on Aug 31, 2016 9:19 AM by pasleeth

    Capturing summary data for a found set

    pasleeth

      Would anyone care to suggest what they think is the most efficient script for capturing summary statistical data (avg, max, min, std dev, count) of a found set in one FMP file and storing it in a related FMP file or table?

       

      In this problem, every record in a found set shares the same value for the sort field (text), so that there's only one value for each of the five statistical summary fields listed above.  In other words, the script's objective is to store the five stats plus the sort field's value rather than export all the records from which those five stats are derived.

       

      -- Andrew

        • 1. Re: Capturing summary data for a found set
          BillisSaved

          Good evening pasleeth,

           

          I hope you had a wonderful weekend. If I understand your goal correctly - copying existing values to a related table - via script, I would do something like the following:

           

          • set a global variable for each of the existing values
          • freeze the window
          • go to a layout based on the target table
          • create a new record
          • set each field value with the corresponding global variable value
          • clear the global variables
          • Return to previous layout

           

          Hopefully this suggestion is helpful. Have a great evening!

           

          God bless,

           

          Bill

          2 of 2 people found this helpful
          • 2. Re: Capturing summary data for a found set
            philmodjunk

            Two minor adjustments that I'd make to Bill's suggested script.

             

            There's no obvious need for the variable to be global. An ordinary script variable will do the job.

            When creating the record in the summary table, you'll probably need to set data to additional tables in order to identify the found set from which the summary data was computed.

             

            Here's an example. For a system that I set up many years ago and that is still in use, every evening a script runs that

            a) finds all data from the day's receipts

            b) generates one record in the summary for each category of line items with sum and average type summary values. A date field identifies the date and a category field identifies the category.

             

            This way, a number of reports can be generated from the summary table by referencing the data by date range and category--including a 5 year monthly comparison cross tab where each cell reports total and average values from the summary table for a specific month and year. (12 rows, one for each month of the calendar and 5 columns, each column a different year.)

            1 of 1 people found this helpful
            • 3. Re: Capturing summary data for a found set
              bigtom

              A slightly faster way with less script steps if the target table only has new records generated through the script.

               

              • Set the target table to Auto Enter the global variable for each field.
              • set a global variable for each of the existing values
              • freeze the window
              • go to a layout based on the target table
              • create a new record
              • clear the global variables
              • Return to previous layout

               

              AVG, MIN, MAX, and COUNT are available with ExecuteSQL and might be faster than native summary fields. I have noticed when using an unstored ExecuteSQL SUM the user does not see the wait dialogs for summarizing the data, which can be troublesome to some users. This can also remove the Summary fields from the table as you only store the summaries you need when you need them. Some ways to make this fast with PSOS as well.

               

              You could also use a custom function to do STD DEV only as needed as well.

              • 4. Re: Capturing summary data for a found set
                beverly

                I _think_ I might export (summary fields only) and import into the other table.

                 

                beverly

                • 5. Re: Capturing summary data for a found set
                  beverly

                  I think I might export (summary fields only) and import into the other table.

                   

                  beverly

                  • 6. Re: Capturing summary data for a found set
                    pasleeth

                    Thanks to BizP and philmod, whose combined suggestions formed the basis of my working solution, which executes perfectly.  Here's the final (almost) script:

                     

                    #Get current program name and take to new Ed Hist window

                    Commit Records/Requests

                    [ No dialog ]

                    Copy [ Ed History::Offname_deg_major ]

                    [ Select ]

                    Freeze Window

                     

                    #Find all ed records under current program name and get the program stats

                    New Window [ Name: "Update program statistics"; Style: Document ; Close: “Yes” ; Minimize: “Yes” ; Maximize: “Yes” ; Zoom Control

                    Area: “Yes” ; Resize: “Yes” ]

                    Go to Layout [ “Export Program Stats” (Ed History) ]

                    Enter Find Mode [ ]

                    Paste [ Ed History::Offname_deg_major ]

                    [ No style ]

                    Insert Text [ Ed History::Checked ; “Yes” ]

                    Perform Find [ ]

                    Sort Records [ Keep records in sorted order ; Specified Sort Order: Ed History::Offname_deg_major ; ascending

                    Ed History::app_term ; descending ]

                    [ Restore; No dialog ]

                    Go to Record/Request/Page

                    [ First ]

                    Set Variable [ $ProgramName ; Value:Ed History::Offname_deg_major ]

                    Set Variable [ $Count ; Value:Ed History::Get Count ]

                    Set Variable [ $MaxGPA; Value:Ed History::GPA_max ]

                    Set Variable [ $MinGPA; Value:Ed History::Get Min ]

                    Set Variable [ $AverageGPA ; Value:Ed History::Get Avg ]

                    Set Variable [ $StdDevGPA ; Value:Ed History::Get Dev ]

                     

                    #Take program stats to All Programs table and update them for the current program. Resume transcript screening.

                    #to do: How to deal with programs w/o related records?

                    Go to Related Record [ From table: “All school programs Converted” ; External; Using layout: “Import program stats” ]

                    [ Show only related records ]

                    Set Field [ All school programs Converted::Count ; $Count ]

                    Set Field [ All school programs Converted::Max ; $MaxGPA ]

                    Set Field [ All school programs Converted::Min ; $MinGPA ]

                    Set Field [ All school programs Converted::Avg ; $AverageGPA ]

                    Set Field [ All school programs Converted::Std Dev ; $StdDevGPA ]

                    Close Window [ Name: "Update program statistics" ]

                    Select Window [ Name: "Ed History" ]

                    Exit Script [ ]

                     

                    Because the script is triggered by a modification of Ed History::Checked (formatted as a checkbox), it was absolutely imperative to commit the record as the first step.  Otherwise, the active record isn't included in the statistics until the next time the script is run.