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
Limitations
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.
Summary
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.Toodles.