Custom Rollup fields

Microsoft introduced Rollup Fields to the Dynamics CRM platform in 2015, and this added significant positive functionality to the platform. Rollup fields contain aggregated values that have been calculated from child records for a parent record. Typical examples of this would be total value of Open Opportunities for an Account, or the total number of Emails received for a Contact.

Whilst there are benefits to Rollup Fields, there are also some “gotchas” to be aware of before you implement them in your environment. Jukka Niiranen has outlined some of these on his blog, and some of the other considerations are outlined on the Microsoft site. For me, the two main considerations are:

  1. Rollup fields are asynchronous, so they don’t update in real-time
  2. You can only have a maximum of 100 rollup fields across your organisation, and 10 per entity

With these drawbacks in mind, you may wish to explore alternatives to creating rollup fields.

The Scenario

In my environment, we define our Opportunities in two ways:

  1. Is the Opportunity from a New Client, or is it Account Development with an existing Client?
  2. Which Segment of our business is the Opportunity associated with?

In order to capture this information, we have a custom entity for the Segments with a lookup field on the Opportunity, and an Option Set field for the Opportunity Type.

For each Segment, we wanted to be able to see:

  1. What is the total value of the Estimated Revenue of All Open Opportunities?
  2. What is the total value of the Weighted Revenue of All Open Opportunities?
  3. How many Open Opportunities are there?
  4. What is the total value of the Estimated Revenue of all Open Account Development Opportunities ?
  5. What is the total value of the Weighted Revenue of all Open Account Development Opportunities?
  6. How many Account Development Open Opportunities are there?
  7. What is the total value of the Estimated Revenue of all New Client Open Opportunities?
  8. What is the total value of the Weighted Revenue of all New Client Open Opportunities?
  9. How many New Client Open Opportunities are there?

This is easily achievable using standard Rollup Fields, however it would require 9 rollup fields which is a significant proportion of the available rollup fields on the entity and the organisation.

The Solution

One of my favourite things about the Dynamics community is the amount of free content that contributors have made available to help others solve problems. In this case, I’m going to be using the Dynamics 365 Workflow Tools created by Demian Raschkovan

Dynamics 365 Workflow Tools – List of Functions

In this set of Custom Workflow Activities there is an option called Rollup Functions that we will be using. This allows you to use a FetchXML query to define the records we’ll be summing up.

Setting up the Entity

On the entity that you’d like to add the “rollup” fields to (in this case the Segment entity), create Simple fields to capture the data you’ll be summarising. In my case, I have added six Currency fields (for the Estimated Revenue and Weighted Revenue calculations) and three Whole Number fields (for the Quantity calculations)

Creating the Workflow

Create a real-time workflow on the entity you’ll be summarising the data from (in this case the Opportunity entity), and set the trigger to run on Create, or on Update of the Segment, Estimated Revenue or Probability fields.

The first step is to get the GUID of the referenced Segment on the Opportunity, and Demian has a function for that too – Get Record ID. We need the GUID to reference in the FetchXML we’ll be using.

Note: in the description for the Rollup Functions activity it suggests you can use a {PARENT_GUID} tag to pass in dynamic data, but I couldn’t get it to work consistently, so I use the Get Record ID activity to bypass that requirement.

The next step is to add a Rollup Functions step to your workflow, and then define the FetchXML you’ll be using. There are two methods to get your FetchXML, either using the Advanced Find, or using the FetchXML Builder by Jonas Rapp.

I want to find all Open Opportunities against a specific segment so I add two conditions, and ensure the field I will be aggregating is first in the list, which gives me a FetchXML query that looks like this:

We’ll paste this into the Workflow Step properties for the Fetch XML and replace the GUID with the output from the Get Record ID step

We repeat this step for each FetchXML query we want to run. In my case, I’ve ended up with six Rollup Function steps in my workflow. The Rollup Functions can output the Average, Count, Max, Min or Sum of the query.

The final step of the Workflow is to update the Segment and update the values with the Rollup Function outputs

The workflow is now ready and will provide you with real-time rollup functions to count the values in your pipeline.

Updating the Previous Segment

Ah, what’s that I hear you say, we’re not done yet? Well, you’re right of course. The above is all well and good, but changing the Segment means the Opportunity is double-counted in the pipeline analysis, unless we change the previous segment too, so how do we do that?

You need to create a second lookup to the Segment entity on the Opportunity, to hold the Previous Segment. Then create another real-time workflow on the Opportunity that is triggered Before the Segment field changes, and copy the value in the Segment field to the Previous Segment, and then trigger a Child Workflow to update the Previous Segment. The Child Workflow will be a copy of the one we created above, however it will need to be a background workflow to ensure it captures the data correctly.

Notes

This offers a viable alternative to the OOTB rollup fields. It should be noted that using real-time workflows can add performance overhead to your environment, so consider what is best for your particular deployment. As ever, using third-party tools is at your own risk, so ensure it has been tested thoroughly before you consider deploying it.

Excel Project Plan

This isn’t strictly a CRM/D365 post, but I think it could provide some assistance for planning CRM related projects, so I thought I’d share.

Any good CRM project, whether that be a new deployment or a small change, requires planning to ensure it is effective; the 5 P’s cliché “Proper Preparation Prevents Poor Performance” exists for a reason.  I am aware that plenty of people use Microsoft Project or use D365 Project Service Automation (if you want to learn more about this I’d highly recommend reading Antti Pajunen‘s excellent blog posts about PSA), however I am also aware, from my experience of working in small companies, that the licence costs for these products can be prohibitive.

A Simple* Solution

Any company that utilises the Microsoft Office technology stack as part of their business will have access to Excel, and therefore they’ll be able to utilise the vast array of templates that Microsoft have made available to help them in their business.  I’ve used many of them in the past, and continue to do so today.

There are many Project related templates available for Excel, and I recently saw the Agile Gantt Chart template.  This template is great because it provides a decent foundation for a Gantt chart, but there a number of areas I felt it was lacking, so I’ve modified it to try and make it more suitable for my purposes.

My Template

My concerns with the template available from Microsoft are:

  1. There is no ability to automatically schedule task completion dates
  2. There is no ability to include predecessors for tasks
  3. There is no ability to effectively resource manage tasks

With all of this in mind, I thought it would be a fun task to see if I could implement some improvements.

Project Plan Template

I’ve included a link below to download my version of the template.  The key features I’ve added are as follows:

Activities are added by:

  1. Selecting a Component from the drop-down selector in the Component column
    1. The Component drop-down is populated from the Component Column in the High-Level Summary Dates table on the Project Summary worksheet
  2. Manually inputting an Activity description in the Activity Column
  3. Selecting a Task from the drop-down selector in the Task column
    1. The Task drop-down is populated from the Task Column in the Mid-Level Summary Dates table on the Project Summary worksheet
  4. Selecting a Category from the drop-down selector in the Category Column
    1. Goal marks the Activity with a Goal marker on the Gantt chart
    2. Milestone marks the Activity with an Activity flag on the Gantt chart
    3. On-Track, Low Risk, Med Risk and High Risk format the cells on the Gantt chart in accordance with the format on the Legend at the top of the sheet

 

Start Dates are calculated as follows:

  1. Each Activity starts on the End Date of the preceding Activity in the list, unless:
    1. A Predecessor is selected by inputting the ID of the predecessor in the Predecessor column; and/or
    2. A number of “Lag Days” in working days is input in the Lag Days column
    3. An Actual End Date is entered for either the preceding task or the predecessor

 

End Dates are calculated as follows:

  1. The estimated effort in Working Days is input into the Effort (Working Days) column
  2. Responsibility for the task is allocated to a person using the drop-down selector in the Responsible column
    1. The Responsible drop-down is populated from Name column in the table on the Project Personnel worksheet
  3. The Task Duration is automatically calculated as the Estimated Effort / Effort Profile (from the Profile column in the Project Personnel Sheet), and is rounded up to the nearest ¼ day
    1. E.g. a task with an estimated effort of 1 day, allocated to a person with an Effort Profile of 50%, would have a Task Duration of 2 days
  4. Any holidays to be accounted for are documented in the Holidays table on the calcs worksheet
  5. The End Date is therefore Start Date + Task Duration (in working days), and ignores any holidays

 

If you want to use this template you can Project Plan Template

Let me know if you find it useful!

Update an Account in CRM with the latest Stock Price using North52

I recently installed North52 in my CRM environment and I’ve been having great fun exploring the capabilities of the solution to replace some existing plugins, and to develop some new functionality.  North52 is an amazing solution that enables functional consultants like me to develop technical solutions using a simple point-and-click editor.  North52 is probably worth several blog posts all by itself, but for the sake of brevity I’m going to focus on the WebFusion service.

What is WebFusion?

WebFusion allows you to integrate your CRM system with any services that expose a REST API.  It’s really straightforward to develop an integration and there are a number of cool features:

WebFusion Features.PNG

Getting Stock Prices

I work for a financial services company, and we have a number of publicly listed clients and prospects.  In order to equip the staff with as much information as possible I thought it would be useful to provide them with the latest stock prices for those companies.  A bit of google-fu led me to Alpha Vantage, which has a free API to return real-time and historical stock data in JSON and CSV format.  I was interested in using the Time Series Data, which offers a number of different data points.  The Alpha Vantage website explains this better than I can:

Time Series Data provides realtime and historical equity data in 4 different temporal resolutions: (1) intraday, (2) daily, (3) weekly, and (4) monthly. Daily, weekly, and monthly time series contain up to 20 years of historical data. The intraday time series typically spans the last 10 to 15 trading days.

I wanted to get the daily close price for any of the clients/prospects that had been identified, so I was using the Time Series Daily API call.  For an example JSON file, see this link.  I’ve included a sample in the screenshot below:

Sample JSON

Now that the first step is out of the way, the challenge was to see how I could get this information into CRM.

Creating the North52 Formula

One of the things that I really like about North52 is the simplicity of creating formulas.  For this formula I only wanted it to be fired if there was a Stock Code on the record so I initiated the formula with an If(ContainsData) function. If this was successful then I wanted to use the CallRestAPI function.

Getting the CallRestAPI function to return the information I needed in the formula was pretty straightforward, however I did run into a little bit of trouble trying to convert the returned data into a format I needed to be able to add it to my record.  Luckily for me, the guys at North52 are super helpful; John Grace responded to me really quickly with the updates I needed and the resultant formula looks like this:


if( ContainsData([account.stockexchange]) ,

CallRestAPI(
SetRequestBaseURL('https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol='+[account.stockexchange]+'&apikey='+xCacheGet('Alpha Vantage API Key')),
SetRequestResource(),
SetRequestDetails('GET'), 
SetRequestHeaders(),
SetRequestParams(), 
SetRequestAuthenticationBasic(),
SetRequestFiles(),
SetRequestExpected('OK'),
SetRequestActionPass(
 SmartFlow(
 SetVar('responsecontent', Replace(GetVar('responsecontent'), '. ', '_ ') ), 
 SetVar('responsecontent', Replace(GetVar('responsecontent'), '(', '') ),
 SetVar('responsecontent', Replace(GetVar('responsecontent'), ')', '') ),
 SetVar('LastRefreshed', GetVarJsonValue('Meta Data.3_ Last Refreshed') ),

 UpdateRecord('account', [account.accountid], 
 SetAttribute('hr_lateststockprice', GetVarJsonValue('Time Series Daily.' + GetVar('LastRefreshed') + '.4_ close') ),
 SetAttribute('hr_lateststockretrievaldate', GetVar('LastRefreshed') ) 
 ) 
 )
 ),
SetRequestActionFail(ThrowError('Fail')
)
)
, 'NoOp')

The main steps that John added were as follows:

  1. Change periods (“.”) to underscores (“_”) in the JSON results
  2. Remove open parentheses (“(“) and close parenthese (“)”) from the JSON results
  3. Create a variable to hold the Last Refreshed date for use later in the formula

Once we had these updates, all that was left to do was use an UpdateRecord step to add the output to my CRM records.

Whilst the Formula code above can look a little bit daunting to a non-developer, its actually really straightforward to add, and there are wizards for most formulas to make it even easier.

Extending the Functionality

As the Stock Price changes every day, I wanted to ensure that it was updated daily on the CRM record, and North52 has a great Scheduler that makes this easy to achieve.

I also wanted to make it possible for Users to trigger this on-demand if needed (e.g. if they had just added the Stock Code but didn’t want to wait for the formula to run at 2am).  This was achieved through the use of a North52 Quick Button.

Update Stock Quick Button

Additional Considerations

If the stock you are searching for is listed on the London Stock Exchange, you need to suffix the stock code with ‘.l’ (without the inverted commas).  I believe the syntax for the stock code symbols follows the Yahoo Finance system, so it’s worth checking on there to ensure you’re using the correct code if you’re having issues.

It’s probably also worth noting that this is a free API, and therefore the service could change at any time.

Conclusions

This was a fun project for me to work on, and I’m pleased with the results.  It shows the power of the North52 solution and it’s enabled me to develop an integration that would otherwise have been extremely difficult and time-consuming to achieve as a non-developer.  My next challenge is to see if I can achieve the same kind of functionality with the Companies House API…

 

Add Team Members to another Team

I’ve been working on a problem that’s been plaguing me for months and now, thanks to Microsoft Business Solutions MVP Aiden Kaskela and his Workflow Elements solution, I’ve finally managed to get it sorted.

The Problem

I wanted to be able to conditionally add members of one Team to another with a workflow, without hard coding the specific Users into the workflow.

My specific scenario was as follows:

If an Opportunity meets certain criteria, an Owner Team is automatically created and linked to the record.  The new Owner Team should then be updated to include Users who are in another Owner Team (the Proposals Team in my scenario).

Dynamically Add Users to Opportunity Team

Why couldn’t we just link the Proposals Team to the Opportunity I hear you ask?  Good question, it is because the Proposals Team are the minimum members of the New Opportunity Team and each Opportunity Team may have multiple other Users added to it from across the business.

We use the Teams as part of our custom integration with SharePoint; adding a User to the Team automatically assigns them specific permissions in SharePoint so we needed specific Teams per Opportunity.

I went round in circles for a long time trying to work out the most efficient solution for this, but I kept running into issues.  I was able to achieve steps 1-4 from the image above, but could never quite complete the process.  The closest I came was using the “Add User to Record Team” N:N associate step from Andrii Butenko’s Ultimate Workflow Toolkit, however this still required me to add a step per user and hard-code their name into the Workflow, which meant I would also then have to deactivate the workflow and update it if the composition of the Proposals Team changed.

I asked in the CRM Community Forum to see if anyone else could help but still ran into the same issues.  I reached out to Aiden Kaskela about a month ago to see if he could help and today he’s delivered in spectacular style

The Solution

Aiden has updated his Workflow Elements solution to include a new step – “Relationship – Associate From Query” (available from V2.1.0) which makes my scenario really simple to solve

Kaskela Workflow Solutions

Getting this step to work couldn’t be easier.  You add it to your workflow, then select the N:N Relationship Name, and then you have the option of using a System View, Personal View or FetchXML query to select the records to be associated.  For my scenario, this was triggered on the Team entity, and used a Personal View on the System User entity to find the members of the Proposals Team

Relationship - Associate from Query

I love the simplicity of this workflow step, and I can envisage a number of additional scenarios that this could be used for in my environment.  It makes it really easy to develop complex, dynamic association workflows.

Conclusion

Solving this problem has demonstrated two things to me:

  1. Dynamics CRM/365 is an amazing platform, and the flexibility it offers developers and customisers to deliver on much-needed functionality is so useful.  The system gets better with every release, and it makes it a pleasure to work with
  2. More importantly, the CRM/365 Community is incredible.  There are so many developers who create tools and plugins that they make available for free for us all to use, and they make my job so much easier.  Their creativity and generosity astounds me, and I am so grateful to them for everything they provide.

I could not recommend the Kaskela Workflow Elements solution enough.  I use it for so many applications, and this latest release makes it even better.  Please go and visit his website, download the solution and try it out; I guarantee you’ll love it.

 

Clone a Record with a Notification Message

Trying to find the right words for a title to convey succinctly what I am trying to say is not my strong suit…

Within my organisation we occasionally have the need to create repeating records on an annual (or more frequent) basis, e.g. SWOT Analysis, Anti-Money Laundering checks, etc.  In order to preserve the original record we will lock it after a certain period of time to prevent changes being made, and therefore encourage the users to create a new record instead to record any updates.

To make it as easy as possible for the users to maintain the records, we wanted to add a simple notification to the record with an option to Clone the existing record.

SWOT Clone

The functionality above was really easy to implement using the Notify.js solution created by Paul Nieuwelaar of Magnetism Solutions Limited. (Incidentally, Paul also created the Process.js and Alert.js solutions which I would also highly recommend).

Paul’s documentation is really clear, so even a coding novice like me could put it to use really quickly.  In order to add the Clone function, I found a script posted by Neeraj Agrawal on the Dynamics 365 blogs; the script uses entity mappings from a 1:N entity relationship to make cloning the record simple.

All that was left to do was to combine the code and add it to my form.  The code I used is below (though please excuse the wordpress formatting!):


function addLockedNotification()
{
var daysSinceCreation = Xrm.Page.getAttribute("hr_dayssincecreation").getValue();
var recordStatus = Xrm.Page.getAttribute("statecode").getValue();

if (recordStatus == 1){Notify.remove("locked");}

else if (daysSinceCreation > 5)
{
Notify.add ("This record is locked. To update the SWOT Analysis, please Clone this Record", 
"INFO", 
"locked", 
[ 
{type: "button", text: "Create Clone", callback: function clone() {
var entityId = Xrm.Page.data.entity.getId();
var entityName = Xrm.Page.data.entity.getEntityName();
var clone_params = {};
var options = { openInNewWindow: true }; // to open record in new window
clone_params["hr_previousswotanalysis"] = entityId 
clone_params["_CreateFromId"] = entityId;
clone_params["_CreateFromType"] = Xrm.Page.context.getQueryStringParameters().etc;
Xrm.Utility.openEntityForm(entityName, null, clone_params, options);
} 
}, 
{type: "link", text: "Not now", callback: function () {Notify.remove("locked");}}
]
);
}
}
}

As you’d expect with code from a random blog on the internet, no warranty is expressed or implied, use at your own risk, etc.

Multi Select Checkboxes

For as long as I have been using Dynamics CRM, users have been asking for Multi-select checkboxes.  I know that they are now available in Dynamics 365 but for those of us on the earlier versions, they are still frustratingly difficult to achieve.

The best solution I have found for this problem, to date, has been to add two-option fields to the entity, and then ensure the control formatting is set to checkbox, which leads to a Form section that looks a little bit like this:

This solution works pretty well, for the most part, but visually I don’t think it’s anything special so I decided to consult my friend Google to see what other options people have developed.

XRM.Multiselect

My searching led me to XRM.Multiselect by Ambrozy Rybicki.  This is a great solution built on Knockout.js (i’m pretending I know what that means) which allows you to create clickable “tile” buttons for your two-option fields, and load them into your form in a web resource.

V1.1.0.0 of XRM.Multiselect allowed you to add a set of tiles to your form that looked like the example below:

I loved this solution, and it’s super easy to install following the manual on Ambrozy’s website.  One of my favourite things about this solution is how easy it is to modify the CSS file to create your own incredible looking tiles.

Within my environment, I wanted bigger tiles, with Hover effects, so that the tiles changed as you moused over them, and this was really easy to add:

For my example, hovering over a field causes it to become white with a coloured outline (grey for unclicked fields, blue for clicked fields), as you can see in the HR field above.

I also like the ability to add different highlight colours for Business Recommended (Orange Underline) and Business Required (Red Underline), as it is so much more visually striking than the blue + or red * that come out of the box.

 

Preventing Users from updating locked fields

As I was experimenting with this solution, I wondered if it would be possible to restrict access to click the buttons if a field was read-only/locked.  I am not a coder by background and my grasp on JavaScript is limited so this was a bit of a challenge for me.

My first port of call was the SDK to see if there were any controls or attributes that would let me know if a field had been disabled, and lo and behold, you can use the getDisabled() control to return a Boolean value to determine if a field has been locked.

I got very excited at learning this, though it still took me a while to work out how to use it properly.  After a bit of trial and error I was able to add this to the JS library for XRM.multiselect and now if a field is locked (either set as read-only, or locked by JS/business rules) then you can add styling to avoid it being updated by Users:

The Trustee tile in the image above has been locked and, by adding “pointer-events: none;” to the CSS style sheet, it is not possible for Users to update the field.  Ambrozy has very kindly merged my fork with his repository and it is now available for download.

 

Using Field Security to lock fields from Users

After achieving the ability to lock disabled fields in the web resource my mind was abuzz and I thought about what other functionality I would like to see in this solution.  We make use of field security in my organisation, and I was concerned about the user experience if they tried to click fields that they weren’t able to.

While the JS won’t overcome the Field Security, this is not obvious to a User and I think it leads to confusion.  Of course, you could just avoid adding a field with Field Security to the web resource, but where’s the fun in that?

To figure out this problem, off I went back to the SDK to see what attributes covered Field Security.  With a bit of google-fu I figured out the getUserPrivilege() could be used to return a Boolean value if a User was able to update a field or not.

A bit more fiddling with the JavaScript assembly allowed me to add the ability to set a class for Secured fields, and a CSS update allowed me to add a different bit of styling to the form:

Conclusions/Next Steps

I think XRM.Multiselect is an excellent tool that adds a bit of real visual flair to CRM forms and enhances the user experience greatly.  Ambrozy has done a great job developing the solution, and his coding made it really straightforward for someone as inexperienced as me to develop a couple of updates that I hope provide useful additional functionality.

I plan on refining the functionality for field security to ensure the styling works as intended then I’ll update GitHub with it so that other people can hopefully make use of it.  I’m constantly amazed at the ingenuity and generosity of the CRM community who develop solutions that make my life so much easier, so I hope that this goes a very small way back to repaying them.

In terms of next steps, I’d like to explore some additional styling options to make it possible to add multiple button types to a form, so any ideas/recommendations are welcomed.