Sunday, 26 July 2015

5 tricks in using calculated and rollup fields for membership organisations

21:32 Posted by Benitez Here ,
This is a subject that's been covered in other CRM blogs however in my vlog I show you five ways in applying calculated and rollup fields in Microsoft Dynamics CRM 2015, specifically for membership organisations. I've worked on several projects where the clients use Microsoft Dynamics CRM for managing memberships. Each client organisation operates differently however there's common data being examined by membership organisations. I help these clients understand their data better by presenting it to them in a more meaningful way. Previously, some of these value adding items I've come up with could not be achieved using out-of-the-box customisations. It would be a mixture of custmosation and development. However calculated and rollup fields rock in Microsoft Dynamics CRM 2015. This is what you'll learn from me in this vlog post and hopefully I inspire you to get creative with fields in Microsoft Dynamics CRM.

Recap of what was possible

CRM 2013 introduced Business Rules and one of the actions that you can do is create a simple formula. My screenshot below shows a simple formula of calculating the total expense cost by multiplying the expense cost by the quantity entered.

This was an improvement to producing formulas in Dynamics CRM. CRM 2015 then came along where calculated and rollup fields were introduced.

Breaking it down Benitez styles

The remainder of of this post breaks down five ways to use calculated and rollup fiends in the context of membership organisations. As mentioned earlier, these scenarios that you're about to read is commonly found in membership organisations.

1. Approval of Order for Corporate memberships

As a membership manager, I want to approve the amount on the corporate membership order

Some membership organisations require approval of corporate memberships before invoices are to be paid (usually these types of memberships are treated differently from retail memberships). Often it's because of managers wanting to approve the rate the membership salesperson sold. Part of this approval process requires a policy statement to be displayed in the CRM record that is being approved. This can be in the form of something like "Policy 10.1.4 - I approve the Corporate membership in accordance to the company guidelines of Corporate memberships v.12" This is achieved through a CONCAT expression in a calculated field. What I did was simply grab the total amount value and used in the CONCAT expressions of the calculated field using text to form the approval statement.

2. Periodic Invoices

As a membership administrator I would like to see the sum of paid invoices for a monthly membership

If the CRM solution handles periodic invoices - monthly, quarterly or annually for payment of memberships, typically the membership administrator would like to know what is the total that has been paid to date and what is the remaining balance. How is this reflected in CRM? Well, an Order can contain many invoices where a payment transaction is associated to the invoice. Therefore invoices that have a status of "Paid," indicates what has been paid to date. The invoice sub-grid provides an overview of what has been paid but there's no quick indication of what the total is. Even if you navigate to the invoice associated view, you still can't see a total.

This is where you can create a rollup field on the Order that displays the sum of invoices Paid to date. The criteria here is displaying a sum only if the invoices have been paid (completed).

3. Number of years as a member

As a memberships administrator I would like to know how long a member has been with us 

For promotional activity, it is important to understand how long members have been a member of the organisation so that these members can be rewarded. Some organisations provide offers to their loyal members who have been with them for 10+ years. To allow the visibility of how many years a member has been associated to the organisation, I created a rollup field using the COUNT function. The criteria here is displaying a count of memberships based on expired memberships.

4. Revenue contribution

As a membership administrator I would like to know who are our top revenue contributors

OK, if you're a current membership administrator this handy trick is for you. Can you currently see who your top contributing members are? Can you do this quickly right now without asking your finance team? Would you like to reach out to your top corporate membership customers to invite them to an annual event you plan to host next month? This I can help you with :)

This method is similar to the periodic invoices breakdown I provided earlier. The payment transactions related to an invoice will help you see who are your top memberships customers. So the key here is creating a rollup field in the Contact entity that aggregates the sum of all paid invoices.

5. Reaching out to newly joined members

As a membership administrator I would like to send an email 6 months after a brand new member has joined to say thank you for being with us

Keeping new members engaged is usually a priority. Typically an offer is presented to the new member as an incentive to remain as a member or to buy more products from the organisation. This is achieved using a calculated field by applying the ADDMONTHS expression to be able see who the newly joined members are after a certain period of time to send out an offer. In my example below, I am adding 6 months from the start date so that a membership administrator will be able to send an offer to these group of members.

Taking this data to the next level

Now that all this data is being displayed in a more meaningful way, the next cool thing that can be done is simply creating views that reference the calculated and/or rollup fields. Either use the view as is, or apply them to marketing lists and charts. Membership administrators can now create marketing lists that outline

  • Corporate memberships that require approval
  • Paid invoices to date of Active memberships
  • Members by revenue contribution
  • Loyal members based on years as member
  • Newly joined members
Below is an example of a dashboard using the five views.


There are limitations that you need to be aware of which I mentioned in my vlog.
Workflows and plugins cannot call calculated or rollup fields. For example, you can’t create an email workflow to send a reminder if the date value in the “Review Date” field is equal to today. For a detailed list of limitations navigate to the following two Microsoft links,
The following are limitations I have found so far that were not listed in the Microsoft links

1. You can’t retrieve a Calculated or Rollup Field when using the “Set Field Value” action in a Business Rule. It will not show in the Actions.

2. You cannot reference an Option Set or Two Option fields in an Action.

3. When setting the condition of a Calculated Field, you can set a condition regarding a related N:1 entity. However it is not possible to set a condition regarding a related 1:N entity.

For other limitations check out Andre's and Jukka's blog post.


Fields rock in Microsoft Dynamics CRM 2015, they give you nuggets of information from a different angle in CRM. You can improve how organisations see their data by being creative with calculated and rollup fields. Think about what else you (as a customer) or what your client (as a service provider) could get out of CRM fields. Try using these fields too in views for system views, marketing lists or charts. Membership administrators take calls on a daily basis and it's crucial the data is more smartly organized in CRM.