Calculated fields that can be included on custom reports

Contents - Index

Calculated fields that can be included on custom reports:

Include a calculated field by typing one of the following names in curly braces { } (in upper or lower case). For example {total} shows the total amount of the donations.

Field Name: Description:

total Total receiptable amount of the tax receipt/letter in the date range.

total nr Total of any non-receiptable amounts in the date range of the receipt/letter

total all Total of receiptable + non-receiptable amounts in the date range of the receipt/letter

total WHICH When the date range includes several years, use this format to indicate the total receiptable amount for only one of the years. eg: {total 0} means the total for the last year in the range. {total -1} means the total for the year before the last.

anyzero When the date range includes several years, do any of the years have no donations?
Note: this cannot be used to ignore donors who had no donations in a year. Instead use the next command.

zeroyear ACTION YEND When ACTION=omit then the report will omit any donor that has no donations in a year of the date range. (Assumes the date range includes several years.)
When ACTION=only then the report will include only donors that have no donations in some of the years of the date range. (and will omit those that donated each year.)
YEND=T uses the taxation year-end to group the donations. YEND=F uses the fiscal year-end. If it is missing it will use the taxation year-end.
e.g. to do a letter only for regular donors who donated every year put {zeroyear omit T} somewhere in it.

fulfill When doing letters or emails, this will show the amount the donor must give to fulfill their pledge if they are falling short. When doing tax receipts, this will show the message To fulfill your pledge, please give: $___. (To omit the message before the amount, add noheading as part of the command.) The amount shown will be either the amount needed to fulfill the entire pledge for the year or the amount needed to reach the fraction expected so far. To indicate which way to show the amount add pledge=E or pledge=F. If neither of those are present then it will show it the way you showed it in the last 'By Date' or 'By Donor' report. If a donor did not make any pledges then nothing will be shown. For example, to show a message on a letter use this: {if {fulfill}} To fulfill your pledge, please give: {fulfill}{/if} To show it on a tax receipt just put: {fulfill}

fulfill given This shows how much was given toward fulfilling a pledge.

pledge DETAILS How much the donor pledged to give. You can show or omit various details of the pledge by using any of the following:
freq shows how often they will give
period shows how long the pledge period is
total shows the total amount pledged for the entire period (when it is missing it shows one year)
enddate or periodend shows the date the pledge period ends.
any text in quotes will show the category they gave to, with the text before it
cat="category name" is used when more than one category has pledges and you want to show the pledge info for only one category.
pstart date is used when the actual pledge start date is different from the date you want to report for. Example: you want quarterly statements to show pledges for the calendar year so you enter them on Jan 1st. However, the pledges really started on Feb 1, 2016 and you want some reports to show that date, so you would use the command {pledge pstart "Feb 1, 2016"}
eachyear shows how much has been pledged in each year of the pledge period going into future years or back to when the pledge first started.
Pledges can be different in each year (or the same) depending on what each donor wants to do. For example, if $500 is pledged per year for three years it would be entered as one pledge for a period of three years. If the pledge was $500 for the first year, $600 the 2nd and $700 the 3rd, you would enter three separate one year pledges for each year with a different amount for each year.
catonly show only the category pledged to, not the amount

Here are some examples:
a donor pledges to give $100.00 per year for 3 years to the General category, then the command {pledge} would show only the amount pledged per year, $100.00, while the command
{pledge freq period "for "} would show: $100.00/year for 3 years for General (When the date range you select is more than one year, the command {pledge} will show the total amount pledged for that date range.)

a donor pledges to give $20.00 per month for 2 years to the Building category, then the command {pledge} would show only the amount pledged per year, $240.00, while the command {pledge freq period "to "} would show: $20.00/month for 2 years to Building

a donor pledges $2,400/year for 3 years, starting on Jan 1, 2015 then {pledge period total enddate} will show 3 year pledge: $7,200.00 ends Dec 31, 2017

pledge CHART A pledge reminder chart which shows (for each category pledged to) how much a donor: a) pledged per year, month, week, etc., b) should have given from the start of the pledge, c) needs to give to fulfill their pledge so far, d) pledged up to the end of the year and e) needs to give to fulfill the pledge for the entire year. Click here for a sample

maxcategory The name of the category with the most donations or pledges.

category The name of the category donated to in the date range of the receipt. If several categories were donated to, it will show each category and the amount given to each. For example, General (25.00) + Building (10.00)

amount CATNAME The amount donated to the category with this name, in the date range. For example, {amount "Building Fund"} is the amount donated to the building fund.

prepaid CATNAME The amount donated before the start of a pledge period, to the category with this name. For example, {prepaid "Building Fund"} is the amount donated to the building fund before the start of the pledge.

paidby The method of payment

note The first note found in the date range. For example, if there are several donations with a note, only the first one will be shown.

motive The first motivation code found in the date range. For example, if there are several donations with a motivation code, only the first one will be shown.

when or reason The name of the service when a donation was received. To get the abbrevation add the word code i.e. {when code}

gikpdf The names of all the PDF receipts for 'Gifts in Kind' and 'Gifts with Advantage' for this donor. For example, if the donor gave two 'Gifts in Kind' in 2015, with receipt numbers 753 and 978 it would return:
Receipt_31Dec2015_753.pdf;Receipt_31Dec2015_978.pdf
This is used when sending PDF receipts by email.

anygik What is the first 'Gifts in Kind' or 'Gifts with Advantage' in the date range? Returns G for 'Gift in Kind', A for 'Gift with Advantage' and nothing if there are none.
anygik G Does this donor have any 'Gifts in Kind' in the date range? Returns G if yes.
anygik A Does this donor have any 'Gifts with Advantage' in the date range? Returns A if yes.

giklist This shows a list of all the 'Gifts in Kind' or the 'Gifts with Advantage' during the date range.
gwalist Use this to do a yearly receipt that shows all the 'Gifts in Kind' and/or 'Gifts with Advantage'. All the information about the donation is shown in separate columns. If you want to change the headings for each column, add noheading to the command then display the heading above the list.

#cat How many categories did this donor donate to? R or NR can be added to count only the receiptable or non-receiptable categories.

ytdtotal The year-to-date total donations. If the donations have both receiptable and non-receiptable amounts the total will include both amounts. To show the total of receiptable donations add an R. (eg: {ytdtotal R}) To show the total of non-receipt donations add NR. (eg: {ytdtotal NR})

ytdsummary MIN#CAT A year-to-date summary of the donations by quarter and category of the donations from the start of the year to the end of the date range given. At least MIN#CAT categories will be shown even if the donor donated to fewer categories. The categories not donated to will have zeroes in the summary. (When MIN#CAT is missing, only the categories donated to will be shown.) For example: {ytdsummary 5} shows a summary with at least 5 categories.

If a donor made a pledge, then this summary also shows how much they pledged in each category. The pledge will be either the entire pledge for the year or the fraction expected so far. To indicate which way to show the pledge add pledge=E or pledge=F. If neither of those are present then it will show the pledge the way you showed it in the last 'By Date' or 'By Donor' report. If you use pledge=EY it will change the heading for the pledge column to say Pledged/Year. This pledge info can be omitted by adding nopledge. For example: {ytdsummary nopledge} will not show any pledge info.

SCTM will show all the sub-category totals and main categories, not any sub-categories. (This will ignore your selection of categories.) R or NR can be added to show only the receiptable or non-receiptable categories.
A standard heading is shown for the summary. If you want a different heading, put it on the line before the command then add noheading as part of the command.
subcat or sub-cat shows only the sub-category name instead of the entire name

When a {ytdsummary} command is inside of an {if} it will return the number of lines required to display it. This enables you to put it on a separate page if it is too long. Eg: {if {ytdsummary}>26}{newpage}{/if} You should include the same options or parameters you will use when you display it.

ytdsummary monthly MIN#CAT A year-to-date summary of the total donations for each month and category, during the date range. It will also show a total for the entire year. As explained above, you have the options: nopledge, pledge=, R or NR.

ytdsummary qtotal A year-to-date summary of the total donations for each quarter without a category breakdown. As explained above, you have the options: nopledge, pledge=, R or NR. (you can also use the word total to indicate this type of summary)

ytdsummary ctotal MIN#CAT A year-to-date summary of the donations for each category without a quarterly breakdown. As explained above, you have the options: nopledge, pledge=, R or NR. (you can also use the word short to indicate this type of summary)

ytdsummary line A year-to-date summary showing the donations for each category on one line, without a grand total and without any pledge info. For example: (General $340.00 + Building $241.00). If someone gave to only one category, it will only show the category name.

details MINAMT STYLE OPTIONS A detailed listing of donations of MINAMT or more that are in the date range given. When MINAMT is not given, all donations are shown. For example: {details 250.00} shows a listing of donations of $250.00 or more. STYLE determines the format of the listing and can be one of:

rows - the donations and pledges are shown with one category per row. Any note that was entered with the donation will also be shown. (If STYLE is missing the donations will be shown in rows.)

sum - when showing in rows: add a column for the total on each date (used when most donors donate to 2 or more categories on each date)

rows nopledge - the donations are shown but not the pledges

columns - the different categories that were donated to are shown in columns. (Pledges are not shown) To fit all the columns on a sheet of paper, each donor should give to 4 different categories or less. If some donors give to more than 4 categories, some columns may be missing. Any notes entered will be shown if they fit on the page.

columns=nn - show a minimum # of columns even if there were no donations to them all. For example columns=6 will show 6 or more columns with zeroes in those with no donations.

columns A-CAT NHEADING and A-CAT matches a category name: (Upper/lower case is ignored when looking for a match)
The categories are shown in columns with two extra columns: A-CAT and NHEADING. The A-CAT column will have the amount donated to the A-CAT category, the NHEADING column will have the 'Note' entered with the donation and the last column the total. eg: {details columns "Designated" "Comment"}

columns AHEADING CHEADING but AHEADING does not match any category name:
The categories are shown in columns and when there are more than 4 categories donated to, AHEADING is the heading for the 4th column and CHEADING is the heading for the 5th column. The 4th column will have the amount, the 5th the category name and the 6th the total. If the amount in the 4th column is from 2 or more categories then only the first category name is shown with a + (plus sign) after it to indicate that the amount comes from several categories. eg: {details columns "Other" "Category"}

There are several options you can add:
nocat - the categories are not shown

nopaidby - don't show the method of payment

nonote - don't show the note

totalpaidby - at the end of the listing, the total of each payment method will be shown. (i.e. checks, cash, etc.)

noheading - don't show the standard heading for the list. (Use this if you want a different heading, by puting it on the line before the command.)

ytd - show a detailed listing of all donations in the year. If it is missing only the donations in the date range are included.

dates lastquarter or dates lastq - show the dates in the last quarter of the date range. eg: when the date range is Jan 1 - Sept 30 {details lastq} will show the donations from July 1 - Sept 30.

dates FROM TO - shows only the dates in the date range. eg: {details dates "Oct 1" "Oct 31"} shows the donations in the month of October. (Year can be omitted.)

SCTM - shows all the sub-category totals and main categories, not any sub-categories (this will ignore your selection of categories)

subcat or sub-cat - shows only the sub-category name instead of the entire name

R or NR - shows only the receiptable or non-receiptable categories.

pstart date is used when the actual pledge start date is different from the date you want to report for. Example: you want quarterly statements to show pledges for the calendar year so you enter them on Jan 1st. However, the pledge period really started on Feb 1, 2016 and you want some reports to show that date, so you would use the command {details pstart "Feb 1, 2016"}

When a {details} command is inside of an {if} it will return the number of lines required to display it. This enables you to put it on a separate page if it is too long. Eg: {if {details}>26}{newpage}{/if} You should include the same options or parameters you will use when you display it.

details total alldates A detailed listing of donations, showing only the total amount given. (no category or note is shown) It has up to four columns of dates and amounts in a chart form. When the date range is all in the same year, only the month and day will be shown. If the word alldates is included it will show every date on which a donation should have been received. This way the donor can see which weeks they missed giving. You can have lines to divide each row and column using the grid command. For example: {grid}{details total alldates}{/grid}

details total+p alldates This is similar to {details total} except that it includes the payment method of the donations. This make items wider so it will have up to three columns.

list CAT_L DESC_L AMT_R MESSAGE A detailed listing of donations included on the receipt, using three columns in any order. One column has the category names. Another has a description of each donation. Then there is a column with the amounts. CAT_L gives where to put the category column. DESC_L indicates where the description column starts. AMT_R specifies where to line up the right hand side of the amount column. The description of the donation has three parts: a) the date if the donations have different dates, b) any Note given for the donation or the MESSAGE if there is no Note (if the MESSAGE is not given and there was no Note then this part will be blank) c) the payment method of the donation. For example: {list 0 1.5 6} shows a listing of donations with the category shown at the left margin, the description 1.5" from the left margin and the amount is right-justifed 6" from the left margin. No MESSAGE is given to be shown when the Note is blank.

Add subcat or sub-cat to show only the sub-category name instead of the entire name

list c=CAT_L a=AMT_R b=BALANCE_R d=DATE_L p=PAIDBY_L n=NOTE_L MESSAGE r=NR_L h=MAX_HEIGHT_L
Gives a detailed listing of donations included on the receipt. This is similar to the previous LIST command except that up to seven columns can be shown.
DATE_L gives the position of the date.
AMT_R gives the right hand position of the amount.
BALANCE_R gives the right hand position of the amount owing on pledges.
CAT_L gives the position of the category name.
PAIDBY_L gives the position of the payment method.
NOTE_L gives the position of the donation note.
NR_L gives the position of a column which indicates that a category is non-receiptable and what to show for it.
The columns can be in any order. For example: {list p=1.0 c=2.5 a=4.3} shows the payment method at 1.0", the category name at 2.5" and the amount right-justified at 4.3". If you change it to: {list p=1.0 c=2.5 a=4.3 r=4.5 *} it will also add a column that has a * when the donation category is non-receiptable. You may have a limited space in which to show the list. If there are too many donations to fit, then you can show only those that will fit. You do this by indicating the height available using MAX_HEIGHT_L. For example: {list p=1.0 c=2.5 a=4.3 h=2.0} will show as much of the list as possible in a space that is 2" high. On the last line it will show how many more there are. e.g. if there are 15 donations, but there are only 12 lines, it will show the first 11 donations, then on the 12th line it will say there are 4 more.

Add subcat or sub-cat to show only the sub-category name instead of the entire name

export FIELDS WHAT Use this in a letter to create a custom export of each donation in a date range. WHAT indicates what donation data to export and is any combination of:
   date - the date of the donation
   category or cat - the category name for the donation
   note - the note for the donation
   amount - the amount donated
The fields will be exported in the order they are given in the command. FIELDS has what donor fields to export, enclosed in curly brackets: ie. { and }. For example:
{export {{donors:name};{donors:status};} date note amount} might produce this output:
John Doe;M;Nov 4, 2012;In Memory of Albert Smith;50.00
John Doe;M;Dec 2, 2012;;40.00
John Doe;M;Dec 23, 2012;Christmas;75.00

categories MAX CAT_L NR_L Lists the donation categories the donor donated to in the last 2 years. It will search for donations from: TODATE - 2 years + 1 day to TODATE. MAX indicates the maximum number of categories to show. CAT_L gives where to put the category column. NR_L gives the position of a column which indicates that a category is non-receiptable and what to show for it. For example: {categories 5 3.0} showns a maximum of 5 categories with the category name 3.0" from the left margin and without any column to indicate a non-receiptable category. If you change it to {categories 5 3.0 4.5 *} it will show a * 4.5" from the left margin when the category is non-receiptable.

stats ITEM will get one of the following items by looking at the entire donation history
first_date Date of the first donation
first_dntn Amount of the first donation
first_motive Motivation code of first donation
last_date Date of the last donation
last_dntn Amount of the last donation
last_paidby Payment method for the last donation
last_motive Motivation code of the last donation
max_date Date of the largest donation
max_dntn Amount of the largest donation
num_dntn Cumulative # of donations
total_dntn Cumulative total of all donations
For example, {stats max_dntn} gives the amount of the largest donation.
Any item that is a date can have a date format command after it. For example, {stats last_date YYYYMMDD} will show the date June 20, 2015 as 20150620. You can also add the word DATE after the item and it will use the donations in the date range rather than the entire donation history.

setvar NAME RESULT Store the result of some calculation or other field to a variable named NAME to use later on and also display it.
addvar NAME RESULT Add a result to a variable and display it.
getvar NAME Get the value of a variable.
gstrate PROVINCE Get the GST/HST rate of a Canadian province. eg: {gstrate {donors:prov}}

© 1998-2023 Nuverb Systems Inc.          "Software Tailored for You"