Organisations often request what is called a ‘Customer 360’ view within their CRM systems. This is unfortunately a term which has become overused, misused and actually quite misleading. Feel free to listen to why in one of my YouTube videos (this one) but in short, human beings are not owls, and we cannot consume a large amount of information at any one time. There is a whole subject discipline on visualisation of information and a reason why info-graphics and representing complex data easily is not only a highly sought after skill, they help us digest a large amount of information at any one time. Dynamics 365 is not a system for info-graphics to be embedded into it though so we need to ensure we disseminate the information available to use from a CRM system (which is a lot) in Customer records into high level data, relating to your business. It must be available to be searchable and also there for further analysis of the records which create such high level views.

Real-Life Problem

I’ve done some decent recent reading into customer satisfaction and surveys and find this whole area fascinating. However one of the complexities with any sort of survey tool and design is your likely to use different scales of scoring. This means you can have one survey rating an experience out of 10, another out of 7. Where this information ends up rolling up to the Contact record, how do users know if the customer is happy or sad? When a customer service agent picks up the phone, can they get a quick indication in the space of 5 seconds of it being routed to them if this customer is happy? How can you get a snapshot picture of their current sentiment? Standard survey tools don’t have this functionality – unless you drill into recent surveys and start reading the survey responses over a number of related records.

The solution that i’ll be walking you through in this blog post will show you how to make a solution that will very easily convert a scale type survey question into a common scale and then rolled up to the contact to give an average on the collection of /10 scores. (Averages shouldn’t be used on data which is scaled differently as it must be normalised first). I cannot take credit for the calculation, I found this during the research for such solution here – fascinating if you want to give it a read. If lightweight algebra isn’t your thing, feel free to skip, as you’ll still benefit from the solution.

This solution is also practising ignorance of calculating the average on a set of ordinal data, when there is subjectivity within this because the difference is not known between the different answers in the surveys e.g. from ‘Not Happy to the next scale of ‘Marginally Happy’ for example. This response type also differs between individual. The reason it’s ignorant is because it’s a basic solution to give an idea of how the customer is feeling at any moment in time, and for that, it solves the real life problem we have described above if you can look past this subjectivity.


This diagram shows the challenge of different types of surveys and them rolling up to provide an overall view of the results at customer level


Solution Concept and Design

The solution for this is fairly simple. It contains;

  • A calculated field which contains the conversion calculation (which is /10 in this solution but can be modified to your needs)
  • A roll-up field containing the average of related converted data
  • An option-set for the scale from the survey on the conversion entity detailing which conversion to do (as you can have many e.g. out of 7, out of 5)
  • A new entity called ‘Survey Conversion’
  • A workflow which performs the creation of the conversion entity

The great thing about the solution is it would work with any survey tool your currently using or hope to use providing you get a survey response linked to a Contact.

Business Logic of the Solution – On create of a Survey Response, the Conversion record is created and then automatically rolls up to the Contact

The calculated field logic is simply;

“If Scale equals X then set Converted Response to X” – the condition is always mapped based on the survey response so you need to know what the scale from which survey your working with, and then the calculated field can convert it from that, to the common response of X (which in this case, is out of 10).

  • The calculations for where the Scale is 1-7, the action is (((10 – 1) * (cat_surveyresponse – 1)) / (7 – 1)) + 1
  • The calculations for where the Scale is 1-4, the action is (((10 – 1) * (cat_surveyresponse – 1)) / (4 – 1)) + 1

Use the above two examples to work out the calculation you require going from your scale, and change it to suit your requirements.

Creating the solution within Dynamics 365

Please remember to create a new solution with a new publisher to ensure you do not use the ‘new’ prefix if your doing this in project or intend to use it in any way in a live environment.

  1. Create your new custom entity which I called ‘Survey Converter’ in the example. The entity definition is below.
Field Name Type
Scale OptionSet
Survey Response Decimal (Create it with options above or more relevant options)
Converted Response Decimal (Calculated Field)
Survey Record Lookup (to your Survey Entity)
Contact Lookup (Customer)

2. Create your Calculated Business Logic as described in the design section above by adding the conditional operator based on your Scale, and the action will be your calculation. You can see mine below.

3. Create your roll-up field on the Contact entity of type Decimal using your related entity, Survey Converter (the entity you just made) and the aggregation is AVG of Converted Responses. Feel free to add more conditions on the records too if you wish e.g. Active status. Secondly, create a new field called ‘Sentiment’ and create an option-set of Positive, Neutral, Negative.


4. Now create the new workflow which creates the records for conversion. This is going to largely be related to which survey tool your using. My example is set up using Voice of the Customer on the entity ‘Question Response’ and I wanted it to operate on a real time basis when I was testing, but in reality a background workflow would be fine for this as it’s not using any real-time specific features. I set up a condition check to ensure the Question Type was of Type ‘Ranking’ and the Number of Items were 7. This then creates my Survey Converter record, populating the option-set scale of ‘1-7’ and I dynamically add the Survey Response in from the Question Response record, populate the lookup and also the Contact.

5. Create your business rule on the Contact record to set your option-set ‘Sentiment’ based on the value from your Rollup Field. The one you see below is set up so if the Current Sentiment is < 6, it’s negative, if it is less than or equal to 8 but greater than or equal to 6, it’s neutral, else it’s positive.

6. Publish all your changes and you should be ready to go to test your solution. You can test in isolation by creating Survey Converter Records, or test using your Survey end to end tool.

Testing the Results

I created three ‘Survey Converter’ records and entered various values using two different scales that I set up.

I then  loaded the Contact record to see the Current Sentiment and the Sentiment in text form being automatically calculated for me. That’s it!

As a User, I can see both fields available on views, dashboards, charts even. I can see this in quick view forms. With this information I can also go to a sub-grid and see the related Survey Converter record which has created that score if i needed. As a Customiser, you can create lots of things such as visual view icons based on the ‘Sentiment’ option-set, a happy/neutral/sad web resource images which are displayed to improve the user experience even further. There are loads of possibilities and extensions to this solution, it is intended as a place to get started and I hope it does that!

Any questions please leave them in the comments below and I’ll do my best to help you.