Questions? Feedback? powered by Olark live chat software

Problem:

Your organization needs to track total annual deductible amounts on donations for all members of a household made in the calendar year.

Solution:

Not For Profits can easily rollup this information across their objects using the Free Edition of Rollup Helper.

Recipe:

Step 1

First create a formula field on your opportunity (donation) object. The formula must have a return type of “Text” to filter for donations that happened in a calendar year. You can see the formula created to take the CloseDate of a closed opportunity, convert it to a number representing the year, and then comparing if that value is the same as the year before.

Step 2

You will now need to create your target field on Account. This step is optional if you already have the field created on your target objects. Here is a currency field that stores the Total Deductible Last Year value after Rollup Helper runs.

Step 3

You will now need to create the source field on Opportunity from which all your rollups will depend on. The figure below shows the currency field that stores the Deductible Amount.

Using the formula field we created in Step 1 as a filter, Rollup Helper will sum all Deductible Amounts across all donations tied to the Account and aggregate to the target field created in Step 1. For Rollup Helper to do this we;ll need to create a Rollup Setting in Rollup Helper, but first, let’s complete the setup of all our remaining field.

Step 4

You will now need to create your target field on Household, the ultimate destination for your rollups. In the image below, you will see the currency field created that stores the Total Deductible Last Year value after Rollup Helper runs.

Step 5

Now that we’ve completed the setup of all source and target fields, we can use Rollup Helper to create the two rollup helper settings.

The following steps will guide you through creating the first rollup between the source (Opportunity) object and the target (Account) object.

Select your target object. In this case “Account”.

Select the Account field where you want Rollup Helper to place the calculated field. In this case “Total Deductible Last Year”.

Now comes the fun part! Select the child object that is related to Account and choose the field you want to source information from. Before choosing a field, you need to select the relationship field. We will select “Opportunity” (your exact relationship label may be renamed). Next, we’ll pick the field. In this case, we want the “Deductible Amount” field created in Step 3. The last two options are really important. First, you must select the option of the type of rollup to perform. In this case, we want a SUM. We don’t want to sum all donations (opportunities), but just the ones from last year. So, the final step is to add a filter criteria where we select the “Donation Last Year field with the operator of equals and lastly with the value of “Yes”.

The following steps will guide you through creating the second rollup between the source Account object and the target (Household) object.

With Rollup Helper, we can now easily create another rollup settings that grabs the Total Deductible Last Year field values on the Account object via the relationship with the Contact (Donor) and roll the values up to the Household level. There is no need to create a cross object formula field from the Account to the Contact Object. See the screen shots below for further details.

Select your target object. In this case “Household”.

Select your target field, “Total Deductible Last Year”.

The last step, you’ll select a source value just as in the prior rollup, but this time, you’ll select a source value through another relationship. In this case, we’ll grab “Total Deductible Last Year” value off the Account object via the Contact relationship.

Step 6

Now, all you need to do is click the Run Now button in Rollup Helper and your information will be updated. Voila!

Get Rollup Helper for free today here. Visit the Rollup Helper Community to post questions, recipe ideas, or other uses for Rollup Helper.