Creating a Gift Management System for Santa and Hanukkah Harry to Demonstrate New Features of CRM 2015/Online
With the holiday season in full force, these seemed like the perfect time to talk about all of the joy that Microsoft has delivered with all of the exciting new features of Dynamics CRM 2015, their newest release!
And to really embrace the spirit of the season, in demonstrating these new features, I decided to create a scenario in CRM Online that involves the management of gifts – in terms of both cost and quantity – for both Christmas and Hanukkah. This gift management system will leverage three of my favorite new features of Dynamics CRM 2015:
- Rollup Calculations
- Calculated Fields
- Hierarchies
And using these new features, I will demonstrate how both Santa and Hanukkah Harry can keep track of:
- The total number of gifts given on each day/night of their respective holiday
- The grand total number of gifts given for the entire duration of the holiday
- The total amount spent on gifts for each day/night
- The grand total amount spent on gifts for the entire duration of the holiday
- The date of the first and last day/night of each holiday
- The total amount of each type of gift given
And finally, allow their manager, to view an aggregated view of these metrics, across both holidays.
An Overview of the Entities Involved
To accommodate this, I created 5 custom entities:
Holiday
This represents the highest level in our data structure. All Gifts and Holiday Days are associated with a Holiday. Furthermore, we leverage Hierarchies in CRM 2015 to create a relationship between the individual Holidays – and the Holiday Season that aggregates data across Holidays.
In my example, I created 3 Holiday records:
- Christmas
- Hanukkah
- Season’s Greetings (which is at the top of the Hierarchy)
Holiday Day/Night
This represents each individual day/night of the Holiday that it is associated with – and captures the actual Date of the Day it represents. Since a Gift is associated with both a Holiday and a Holiday Day, we are able to use Rollup Calculations in CRM 2015, for each Day, to see:
- Total Number of Gifts Given on that Day
- Total Amount Spent on Gifts Given that Day
In this example, I created 12 Holiday Day records for Christmas and 8 (Nights) for Hanukkah
Gift
This represents each Gift that is given, on a given Day of a given Holiday. On this record, we capture the Quantity, as well as the Cost for each Gift. Then, leveraging Calculated Fields, we derive a Grand Total for the Gift, by multiplying Cost by Quantity. Furthermore, we also categorize each Gift with a structured Gift Category, so that we can easily see the total number of Gifts Given – and Amount Spent on each type of gift.
Gift Category
As described above, this record simply categorizes each gift. However, since each Category record is associated with a Gift, we can use Rollup Calculations on this record to view the total number of Gifts Given – and Amount Spent on each type of gift.
The 12 Days of Christmas Reimagined – CRM 2015 Style
For Christmas, I created the following Gift Categories – they make sound a little familiar:
- Partridge in a Peartree
- Turtle Doves
- French Hens
- Calling Birds
- Golden Rings
- Geese a Laying
- Swans a Swimming
- Maids a Milking
- Ladies Dancing
- Lords a Leaping
- Pipers Piping
- Drummers Drumming
So, what does this all look like? Let’s take a deep dive starting at the bottom of the data model – and as we work our way up to the top, we’ll see how we using the Rollup Calculations, Calculated Fields and Hierarchies in CRM 2015.
Configuring the Calculated Fields, Rollup Fields and Hierarchies – Entity by Entity
Gifts Record
Below, we can see one of the many gift records associated with the 10th Day of Christmas. This gift represents 4 Calling Birds, which were purchased for $600 each. We can see that using a Calculated Field, we were able to derive a Grand Total for the Gift.
Here is a look at the configuration of the Calculated Field for Total Gift Cost
Holiday Day Record
If we drill through to the 10th Day – Christmas record, which represents the 10th Day of Christmas, Santa will get a complete summary of the 10th Day of Christmas
- Total Gifts is a Sum of the Quantity field on the Gifts Record
- Total Gift Cost is a Sum of the Total Gift Cost field on the Gifts Records (yes, it’s a Rollup of a Calculated Field!)
Here is a look at the configuration of the Rollup Field for Total Gifts
Holiday Record
Now, we’re going to drill even higher – and take a look at the Holiday record. As I’ll describe below, we use the Rollup Fields here slightly differently than we did on the Holiday Day records, because we include the Hierarchy that exists on the Holiday record in our calculation.
What does this mean? It means that when we perform the calculation on the Christmas and Hanukkah record, the calculation will be performed exactly the same. However, when we perform the calculation for the Season’s Greetings record, which is at the top of the Hierarchy of Christmas and Hanukkah, it will automatically include both of their calculations! None of the Gift records directly references the Season’s Greetings record – but because it’s at the top of the Hierarchy, we can still perform Rollups against it!
Rollups on Date Fields
In this example, we leverage Rollup Fields in a different way – using the same relationships, we can now easily identify either the Oldest – or Newest date in a Rollup of Child Records. What this means is that we were able to calculate the First and Last Day of the Holiday – in this case Christmas. Looking at the screenshot of the Rollup definition for First Day, you’ll understand that by using MIN (or minimum) on the date field of the Holiday Day, CRM was able to identify as the “oldest” date amongst the 12 dates that we captured on each of the Christmas Day records – very powerful!
Imagine applying this to Accounts – and looking at the date of the Most Recent Won and Lost Opportunity. Or date of First Order. Or Most Recent Phone Call!
Understanding Hierarchies in CRM 2015
Now let’s take a look at the impact of selecting YES for Use Hierarchy (which we do for all of the Rollups on the Holiday record). When we go to the Season’s Greetings record, we will see the same set of calculations, but now, it’s including/evaluating all of the records beneath it – which in this case, is Christmas and Hanukkah.
Take a look at these three records side by side and you’ll be able to visualize this behavior:
Hierarchy Visualizations
And Microsoft also includes a nice visualization tool, so the Santa and Hanukkah Harry’s manager can easily view the relationships within a hierarchy, alongside some key metrics. This can be accessed by clicking the View Hierarchy icon on the Holiday record:
Gift Categories
And finally, let’s take a look at how we leverage Rollups in CRM 2015 to analyze the quantity and cost of gifts by category. This possible, since we created a Gift Category entity and associated it with each Gift record that we created – essentially, we created an 1:N relationship between Gift Category and Gifts, which allows us to rollup values from the Gift record to the Gift Category.
And looking beyond the individual record, we can add these Rollup fields to a View – and easily see which gifts were given the most, as well as which one cost the most! Here she is, the 12 Days of Christmas cost analysis:
In Conclusion
For those wondering about the prices – these are not arbitrary! PNC Bank actually releases a Christmas Price Index each year for each gift – which means next year, we can use Microsoft CRM to take our analysis one step further and do a year over year comparison…but I won’t get ahead of myself!
I hope you had as much fun reading this as I had writing it – and more importantly, that you have an understanding for how to apply some of the great new features that CRM 2015 bring to the table. If you have any questions about Microsoft CRM 2015 or CRM Online in the Cloud – or are looking for some help on a project, please contact a CRM expert at MIBAR or give us a call at 212-869-9300 – we would love to work with you.
To you and your families – Happy Holidays..and Happy CRMing!
Additional Microsoft CRM Resources
Microsoft Dynamics CRM Free Consultation
Enabling a Mobile Sales Force with Microsoft CRM, Scribe Online and Resco – Part 1
