2 Replies Latest reply on Apr 16, 2017 10:50 AM by philmodjunk

    create a summary table


      i have a donations table and a deposits table


      The donations have records in it


      I need to create a record with the total amount donated and the check count in the deposits table by deposit date


      How can that be done in a script?

        • 1. Re: create a summary table

          What you want is a subsummary report and a couple summary fields. Create a new layout based on your deposits table and add a subsummary when sorted by the date field. No body part will be needed unless you want to see each donation amount. Add a summary field that totals the amount and one that counts your _kp_donations_id field into the subsummary part. To view the report, go to browse or preview and sort by the date field. If in browse mode, make sure you are viewing by list.


          You can also perform a find prior to viewing the report and locate certain records (e.g. find the last 6 months).

          • 2. Re: create a summary table

            Note that you can produce summary totals and sub totals on demand without creating a summary table.


            Howevet, reports from a summary can be much quicker to produce than from the original records.


            There are multiple methods available. This just one approach:


            Add a number field called Summarized to Donations.

            Add summary fields to Donations that compute any of the totals and counts needed in your summary table.


            Go To Layout [ "Donations" ; (Donations) ]

            Set Error Capture [on]


                Enter Find Mode [ Pause:no ]

                Set Field [ Donations::Summarized ; "=" ]

                Perform Find [ ]

                Exit Loop If [ Get ( FoundCount ) = 0 ]

                Set Variable [$Date ; Value:  Donations::Date ]

                Enter Find Mode [ Pause: no ]

                Set Field [ Donations::Date ; $Date ]

                Constrain Found Set [. ]

                *Set Field [ Donations::gMagicKey ; "" ]

                 Set Field [ Summary|MagicKey::Date ; Donations::Date]

                 Set Field [ Summary|MagicKey::CheckCount ; Donations::sCount]

                 Set Field [ Summary|MagicKey::TotalAmt ; Donations::sDonationTotal ]

                 **Replace Field Contents [ no dialogue; Donations::Summarized ; 1 ]

            End Loop


            *MagicKey requires a specific relationship. Without that relationship, your script could set variables to each value, change layouts to a layout based on the summary table where the script creates the new record, sets fields to the values of the variables and returns to the Donations layout.


            **The script requires that none of the records being summarized be open for editing by another user or in a different window. Sometimes, this requires using a server schedule to run such a script after hours.