To create a column that uses a formula, select Column>Add from the Quick Report menu. The formula editor is displayed. The formula editor allows you to combine the values of multiple fields, return certain values such as YES/NO instead of TRUE/FALSE, perform mathematical equations and use pre-written functions.
Concatenation
To combine the values of multiple fields into one column, you can use the plus sign in the formula editor. Common examples of combining field values would be to have the last name, a comma, then the first name, or combining the area code and phone number.
For this example, let’s create a name column. Double click on the Last Name field found under the box Related Tables. The name [People]LastName is placed in the formula editor box. Next click on the plus sign. Next enter a quote mark, a comma, a space then another quote mark. Next click on the plus sign again. Now double click on the First Name in the section below Related Tables.
The formula should read:
[People]LastName+”, “+[People]FirstName
Click the OK button to accept the formula.
Equations
You can perform mathematical equations within the formula editor. You can add, subtract, multiply, divide and create percentages. For this example, let’s say we wanted to add a one to a child’s grade.
Double click on the Grade field found under the box Related Tables. The name [People] Grade is placed in the formula editor box. Next click on the plus sign. Now type in the number 1. The formula should read:
[People]Grade+1
Click the OK button to accept the formula.
NUM function
The num function allows you to return a constant, based on a field value. This comes in very handy for Boolean or check box type fields where the value is either True or False. By using the NUM function you could choose to return an X if the value was True or you could return Yes if it was True and No if it was False.
For this example, let’s assume we have a custom check box called Newsletter. In our report, we want to return an X if the person wants to receive the newsletter. The formula is:
(Num([People]Newsletter)*”X”)
If you wanted to return a Yes or No, the formula would be: ((Num[People]Newsletter=True)”Yes”)+(Num[People]Newsletter=True)”No”)
Roll Call functions
The following functions can be typed into the formula editor, or selected from the commands by theme box to the right. Scroll down to reach the functions in italics. Double click to select the function you need.
DoBuild_Label – this function returns an address block for an individual. It contains the name and current address.
JimSueLabel – returns an address block using the first name of the head of family plus the spouse name and the last name, then the address.
Family_Label – returns “Mr. & Mrs.” Plus the first and last name of the head of family, then the address.
DifferentLastName – returns an address block with the first and last name of the head of family plus the first and last name of the spouse (if the last name is different).
Get Many -> People -> Group Membership – returns a list of all groups that the person is involved in.
Get Many -> People -> Pledge funds – returns a list of all the pledge funds this person has pledged to.
Get Many -> People -> Pledges – returns a list of all the amounts a person has pledged.
List_Phones (“All”) - Produces a list of the “non-standard” individual phone numbers that are stored in the telephone table.
List_Family(“ “) Returns a list of all the family members.
Get Children(0) – Returns a list of the children for a family.
Build_Salutation – Returns the head of the family’s nickname plus the spouse’s first name.
Get DES Function(TRUE) – Returns a list of donations to the current pledge record.
Get DES Function (FALSE) – Returns the total of donations to the current pledge record.
Designation SUM – Returns the total for the designations.