3 Replies Latest reply on Dec 14, 2010 2:01 PM by philmodjunk

    Script

    vccfresno

      Title

      Script

      Post

      I'm trying to create a script to get a donation report omitting all the people with less than $1000 total in the summary field.  How do I create a script?

        • 1. Re: Script
          philmodjunk

          Can you describe your report in more detail? The basic problem usually encountered is that find criteria are specific to a given record where the data in your summary field is not--so you can't specify criteria in the summary field to get it to work.

          You may need to set up a calculation that uses a relationship and an aggregate function to filter out people who have donated less than $1000 dollars.

          Or you might want to sort your records using the "reorder based on summary field" option to list all the smaller donation totals at the end of your report and then omit them. This can be scripted if you use getSummary to compute the subtotals for each group of records.

          • 2. Re: Script
            vccfresno

            My report has sub summary sorted by ID# & category (what type of donations). When I try to sort records using the "reorder based on summary field" it takes forever to sort. How do I set up a calculation that uses a relationshiop and an aggregate function to filter our people?

            • 3. Re: Script
              philmodjunk

              Sum ( RelatedTable::NumberField ) will add up all the values of Number field that are related to the current record of the table where you define this calculation.

              Thus you need to define a self join relationship that matches a given record in your table to all other records of the same group that are in your table. This may be easy or hard to implement depending on the structure of your table and your summary report.

              Let's assume you have a donorID number field that uniquely identifies each donor in the table.

              ReportTable::DonorID = ReportTableSameDonor::DonorID

              Where ReportTableSameDonor is new table occurrence of ReportTable. To create this, Open Manage | Database | Relationships and drag from DonorID to outside the box and then back to DonorID before releasing the mouse button. You then rename this "instance" of ReportTable to get ReportTableSameDonor.

              Now you can define a calculation field in ReportTable as: Sum ( ReportTableSameDonor::Donation Amount ) and as this field will report the same total for every field with the same DonorID, you can perform your find on this field. You aren't going to get blazing speed here as you are performing a find on an unstored calculation. If you are performing a find on other criteria besides donation totals, you may want to perform a find on all indexed fields first, then use constrain found set while specifying criteria in unindexed fields to get your final found set needed for your report.

              Warning, if you are performing a find to pull up donation records that will exclude some but not all donation records for a given donor, (Say a find for all donations made in the third quarter), this field won't produce the correct total. If that's the case, your relationship would have to be modified to include the same selection logic as your find.