Available Functions > Sum

Syntax

Sum_In( <Table>, <Condition>, <NumericExp> )

Purpose

The "Sum_In()" function was designed mainly for use with Custom selections. However, the numerical value it returns may be used with both Template selections, and within Report Format Definitions.

During a Custom selection, this function will "look" at all the donations, pledges, memos, or extra addresses for each donor. As it looks at each donation, pledge, memo, or extra address for the current donor in a selection, it will evaluate some condition defined by you. If that condition is true for the current donation, pledge, memo, or extra address, then "Sum_In()" will evaluate the Numeric expression defined by you, and add the resulting quantity to an ongoing sum which "Sum_In()" maintains internally as it is looking at each donation, pledge, memo, or extra address record for the current donor.

When "Sum_In()" is finished looking at all the donations, pledges, memos, or extra addresses for the current donor, it will return as its value the internally maintained sum.

Parameters

<Table> is the name of the table file (donation, pledge, memo, extra address, linkage, etc.) you wish "Sum_In()" to operate within for each donor during a selection. You must use one of the following names, exactly as they appear here:

.Donations.

.Pledges.

.Memos.

.Extra_Addresses.

.Links.

<Condition> is any expression that evaluates to be true or false. This expression is normally used to direct "Sum_In()" to only pay attention to those donations, pledges, memos, or extra addresses you are interested in. For example, if you wanted to ignore all donations that were made more than a year ago, you could use the following condition:

.Donation_Date. >= ( DATE() - 365 )

<NumericExp> is any expression which evaluates to a numeric value. Each time the <Condition> evaluates to true for the current donation, pledge, memo, or extra address, "Sum_In()" will increment its internal sum by the amount derived by evaluating <NumericExp>. With the above example using donations, if you specified:

.Donation_Amount.

for <NumericExp>, then for each donor during a selection, the "Sum_In()" function would return the total amount that each donor gave during the past year. This return value would then be compared against some specific numeric value provided by you, and each donor would qualify for the selection only if that condition were met.

Returns

A Numeric value.

Usage

You can use this function to create selection conditions which will select donors based on donation, pledge, memo, and extra address history.

EXAMPLE 1

Suppose you want to know all donors who have five or more donations of any amount in their donation history. The following Custom selection condition using "Sum_In()" would do it:

Sum_In( .Donations., .T., 1 ) >= 5

The first parameter ".Donations." told Sum_In() that we want to look at the donation history for each donor.

The second parameter is usually a condition, but may be a specific value. Whichever the case, the second parameter must evaluate to a logical (true/false) value. Since we want to look at ALL donations for each donor, we want this parameter to always evaluate to true. In DonorQuest, the symbol ".T." means true, and ".F." means false. The Sum_In() function doesn't really care whether it gets an expression or not for the second parameter, all it's concerned about is getting something that is either true or false.

The third parameter is usually an expression, but may also be a specific value. The only proviso is that the third parameter evaluates to a numeric type. This number is added to the internal sum which is finally returned by "Sum_In()". As with the second parameter, "Sum_In()" doesn't care whether you really pass an expression or not. All "Sum_In()" cares about is that the third parameter evaluates to a number.

Given these parameters, for each donor the Sum_In() function will return the total number of donations in the donor's history. If that number is greater than five, then the donor will qualify for the selection.

EXAMPLE 2

Suppose you want to know all donors who have five or more donations of $50.00 or more each in their donation history. The following Custom selection condition using "Sum_In()" would do it:

Sum_In( .Donations., .Donation_Amount. >= 50.00, 1 ) >= 5

The first parameter ".Donations." told Sum_In() that we want to look at the donation history for each donor.

In this example the second parameter is an expression instead of a specific value. The expression is only true for donations of $50.00 or more, so the "Sum_In()" function will ignore all donations less than $50.00.

Since we want to count the number of donations of $50.00 or more, we will simply use "1" for the third parameter. When looking at the donation history of each donor, "Sum_In()" will add "1" to its internal sum each time it encounters a donation of $50.00 or more.

Given these parameters, for each donor the Sum_In() function will return the total number of donations of $50.00 or more in the donor's history. If that number is greater than or equal to five, then the donor will qualify for the selection.

EXAMPLE 3

Suppose you want to know all donors who have five or more donations of any amount, but only count those donations given in the past year. The selection condition is:

Sum_In( .Donations., .Donation_Date. >= (Date() - 365), 1 ) >= 5

The first parameter ".Donations." told Sum_In() that we want to look at the donation history for each donor.

The second parameter is an expression which is only true for donations which were made during the past year, so the "Sum_In()" function will ignore any donations older than that.

Since we want to count the number of donations for each donor made during the past year, we will again simply use "1" for the third parameter. When looking at the donation history of each donor, "Sum_In()" will add "1" to its internal sum each time it encounters a donation with a date equal to or more recent than today's date minus 365 days.

So, given these parameters, for each donor the Sum_In() function will return the total number of donations made during the past year in the donor's history. If that number is greater than five, then the donor will qualify for the selection.

EXAMPLE 4

Suppose you want to know all donors who have given a total of $500.00 or more during the past year. The selection condition would be:

Sum_In( .Donations., .Donation_Date. >= (Date() - 365),

.Donation_Amount ) >= 500.00

The first parameter ".Donations." told Sum_In() that we want to look at the donation history for each donor.

The second parameter is an expression which is only true for donations which were made during the past year, so the "Sum_In()" function will ignore any donations older than that.

Since we want to total the actual Amounts for those donations satisfying the date condition in the second parameter, the third parameter is now an expression instead of the specific value "1" we have been using in previous examples. "Sum_In()" doesn't care what it gets for the third parameter, provided it evaluates to a number. "Sum_In()" will dutifully sum the numeric value it gets from evaluating the third parameter for each donation which allows the second parameter to evaluate to true.

So, given these parameters, for each donor the Sum_In() function will return the dollar total for all donations made during the past year in the donor's history. If that number is greater than 500.00, then the donor will qualify for the selection.

EXAMPLE 5

Suppose you want to know all donors who have the key phrase "Phonathon III" in their memo history. You could tell DonorQuest to actually select donors based on the letters "Phonathon III" occurring anywhere in a donor's memo. Here's the "Sum_In()" expression to do it:

Sum_In( .Memos., "Phonathon III" $ .Comment_Text., 1 ) > 0

The first parameter ".Memos." told Sum_In() that we want to look at the Memo history for each donor.

The second parameter is an expression which is only true for memo lines which contain the character value "Phonathon III", so the "Sum_In()" function will ignore any memo lines which do not contain the value.

Since a donor will qualify if at least one of their memo lines contains the value "Phonathon III", we can simply let "Sum_In()" count the number of memo lines that do contain the value for each donor, and allow the donor to qualify for the selection if the number of memo lines "Phonathon III" occurred in is greater than zero. Using "1" as the third parameter will work nicely for allowing "Sum_In()" to simply count the number of memo lines for each donor containing the value.