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…

 

Long Date Formatting for Date fields – A Different Way

I recently read a great blog post by Megan Walker regarding the formatting of a long date field in CRM.  Megan has explained really simply how to achieve this using JavaScript, and I’d thoroughly recommend reading her blog to see how easy it is.

I know that some people are uncomfortable even with using simple scripts so I wanted to highlight an alternative way of achieving this without using JavaScript, for those who may wish to avoid it.

I’ve previously discussed the Workflow Elements solution by Aiden Kaskela in my blog about adding team members to another team, and this is another perfect opportunity to utilise it.

Overview of date formatting workflow

One of the options in Workflow Elements is Date – Convert to Custom Text, and this allows you to build a custom text version of any date field in your entity using up to 20 components.  The components available comprise options for Year, Month, Day, Hour and Minute, as well as options for AM/PM designators, time zone display and separators.

Kaskela Date Format Options

Building your date

Creating a custom text date couldn’t be easier, you simply add the Date – Convert to Custom Text step to your workflow, then open it and select the date you’d like to convert, pick the time zone for display and then build it from the options identified above:

Buidling the Date

Once you’re happy with the custom date options you’ve selected, save and close the window then add an Update Record step to your workflow.  In the Update Record step, find the field you’d like to update with the Custom Date, then in the Form Assistant select the previous step output under local values, then select the Formatted Date option and add it to your field.

Add to workflow.png

The output of the workflow looks great and is easy to configure:

Date

 

I still wouldn’t recommend using this for every date field in your environment, but this is a handy and simple alternative to JavaScript and it doesn’t require you to open and save the record for it be triggered.  The configuration options available give you significant control over the output and can cater to most requirements.

CRM Power Pane – Review

One of my favourite tools in my CRM Administrator Toolkit (*not an actual thing) is the Dynamics CRM Power Pane extension for Google Chrome and Mozilla Firefox.

The Power Pane add-on has been developed to help developers, testers and power users to accomplish tasks that they may require in their day-to-day role.  Installation of the add-on is as simple as installing the extension in your browser – no need to install any solutions.

Once you have the extension installed an orange lightning bolt icon will appear in the top-left corner of your Nav bar whenever you open CRM, and clicking this will enable the actions for you.

I should caveat at the top that this tool is very much not for end-users, and I’d caution against even making them aware that it exists.  It can undo the security settings you have put in place, and render your customisations meaningless.

With that out of the way, let’s have a look at what it does.   The CRM Power-Pane add-on is comprised of 3 sets of tools:

  • Record Actions
  • Form Actions
  • Navigation Tools

I’ll give an overview of each of these below:

Record Actions

There are 5 options under the Record Actions section

  1. Entity Info – opens a pop-up window with the Entity logical name and the Entity Type Code
  2. Record ID – opens a pop-up window with the GUID of the current record
  3. Record URL – opens a pop-up window with the direct URL of the current record
  4. Clone Record – creates an exact clone of the current record
  5. Record Properties – opens the record properties window

I particularly like the Record URL options – no more clicking “Email a Link” and then copying the link so I can send it to someone in Skype.  I also get a lot of use out of the Record ID option, it’s come in handy more times than I can count.

 

Form Actions

The Form Actions are where this tool really comes into it’s own, and where I think most other developers/administrators will get use out of it.  There are 12 options under Form Actions:

  1. Enable All Fields – makes all read-only fields editable
  2. Show Hidden Fields – unhides any fields that have been set as hidden
  3. Disable Field Requirement – removes any business recommended or business required options on fields, enabling you to save it without filling them in
  4. Schema Names as Label – changes the field labels to show the logical/schema name
  5. Scheme Name Copy Mode – enables the option to copy the schema name for a field by clicking on the field name
  6. Show Optionset Values – prefixes the optionset options with their value
  7. Show Field Value – opens a pop-up window to allow you to input the schema name for a field, and returns the value in the field, and the field type.  Depending on the type of field additional values will also be returned, e.g. for a lookup field you will get the text value, the record GUID, the lookup record Entity Name and Entity Type Code.
  8. Find Field in Form – opens a pop-up window where you can input the schema name for a field, and then moves the focus to that field on the form and highlights the field
  9. Highlight Dirty Fields – adds a highlight to any fields that have been changed since the form was loaded
  10. Refresh Ribbon – refreshes the command ribbon on the form.  This can be useful if you’re testing the visibility of buttons that appear/disappear based on field values
  11. Refresh Form – refreshes the CRM form, without the need to refresh the whole window.

I use a lot of these tools on almost a daily basis, in particular the Schema Names as Label  and Scheme Name Copy Mode.  If you’re writing any code then I’m sure you’ll find these useful too.  Similarly, being able to unhide and unlock fields has saved me numerous times when I’ve been carrying out testing.

As I said at the start, if your Users find this tool it could cause no end of headaches for you, but it also serves as a stark reminder that CRM is just a series of web pages, so if you want a field to be completely secure don’t put it on a Form that is accessible by Users. Reece Campbell wrote a great blog about CRM Forms and security recently, so I’d recommend it for more reading.

Navigations

The last set of tools in CRM Power Pane are the Navigations tools. There are 6 tools here:

  1. Go to record by ID – enables you to open any record by specifying the entity name and the record GUID
  2. Entity Editor – makes it really easy to open the Entity customisation in the default solution.  This defaults to the entity you’re currently in, but you can specify which one you’d like to open
  3.  CRM Diagnostics – opens the CRM diagnostics page to allow you to evaluate network performance
  4. Performance Center – opens the CRM Performance Center to allow you to evaluate the performance of form loading in CRM
  5. Mobile Express – opens the Mobile Express version of your CRM environment
  6. Mobile Client – opens the mobile version of your CRM environment

Conclusion

The Dynamics CRM Power Pane is an incredible useful tool, and I find myself using it pretty much every day.  The array of tools it offers are varied, and they deliver some much needed added abilities for me as a system developer/administrator.  I’d recommend installing it and seeing how you get on with it, just remember not to let your Users know about it.

Some of you may be aware of another Chrome extension called Level Up for Dynamics CRM/365, developed by Natraj Yegnaraman.  Level Up does a number of similar funcitons to CRM Power Pane, and has some additional options.  I have both installed in my browser, and have used them both extensively.  If you’d like to read more about Level Up, Kylie Kiser recently wrote a review of it and I’d recommend you have a look at her blog

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.

 

Customisation Tips – Entities

Following on from my last post I wanted to share some of the tips I’ve picked up along the way related to developing new entities in Dynamics CRM/365.

The first major step in any development is the creation of a new system entity, and there are plenty of opportunities for missteps here.

Per my last post, ensure any new Entity is created from within a solution, and NOT by selecting “Customize the System”!

Ownership

Entity Ownership

The initial decision you need to make is to decide whether you need the Entity to be owned by a User or Team, or by the Organization.  This decision can be influenced by the following factors:

  • Does the entity need specific security, or will it be open to all Users?
  • Do you need to have a specific named Owner of the records in the Entity?
  • Is the entity going to be used for a reference record (e.g. preferred language, region, etc.)?

This decision cannot be changed after the entity is created, so it is important to make the right decision.  If you’re not sure, I’d always recommend selecting User or Team ownership, as you can work around this to emulate Organizational ownership, this isn’t possible the other way around.

For more reading, see this article on MSDN.

Activity Entity

The next decision you need to make is whether the entity is an Activity or not.  This is a fairly straightforward decision

If the records will have a Start Time and/or an End Time, and will be Completed by Users, then it is probably an Activity.

It’s important to remember that defining an Entity as an Activity Entity cannot be undone, and that Activity Entities don’t have security, all Users can see all Activities, so factor this into your thinking.

Primary Field

Primary Field

A simple mistake I have seen made by many developers is to overlook the Primary Field.  It’s on a separate tab, so it’s really easy to click Save and forget about it, only to regret it later.

It’s important to give it a relevant name for your entity.  The Primary Field can only be a Single Line of Text field, formatted as Text.  Update the Display Name and Name to something relevant for your new entity, also decide if you want the Field to be required or not, and update the Maximum Length. I’d also recommend adding a proper description here.

The Primary Field is used for lookups to the entity, and is the default field included in a View when you create an entity, so forgetting about it can lead to search results that look like:

NoNameEntity.PNG

Even if you’re not going to use the Name (for example if you’re creating a manual intersect entity), I’d always recommend setting it up properly.  In situations where I’m not using the Name I will always set up a Workflow or Business Rule to set the Name automatically.

Entity Options

The last thing to decide before you save your new entity is which of the options you want to select. The best advice I can give here is to untick everything, unless you are ABSOLUTELY sure that you need to keep a field ticked.  This is particularly important for the Business Process Flow, Feedback, Notes, Activities, Connections, Sending Email, Queues, and Enable for SLA fields, which cannot be disabled once they are selected.  You can always enable them later if you need them, but they create fields on your entity, and add to clutter if they’re then unused.

No Ticks

The guys over at CRM Tip of the Day have turned entity creation into a handy illustrated guide that you should definitely print out and hang up on your wall to remind you of these rules!

Once you’re happy with all of the entity options, click Save and now you can move on to adding Fields.  I’ll share my tips for Fields in my next post.

Customisation Tips – Solution Management

When I first started using CRM I made plenty of mistakes that cause me to physically cringe when I reflect on them, so I hope that by sharing some tips that I’ve picked up on my journey, I may be able to help others avoid them.

I’ve been discussing customisation best practices internally in my organisation and, since I’ve been documenting them anyway, I thought it would be worth sharing my thoughts.

Use solutions

This is probably my number one tip. If you’re doing any sort of system customisation, please put it in a solution container; NEVER make changes directly to the base solution – just pretend the “Customize the System” button doesnt exist.

Ignore the Customize the System button

By using a Solution you can avoid the dreaded new_ prefix on newly created fields/entities/web resources, etc. and use your own publisher prefix.  A solution also operates as a container for all the customisations you are making to the system, which makes it significantly easier to understand what changes have been implemented for you and anyone else who may be working on your system.

One of the biggest debates on the use of Solutions is whether you should Managed or Unmanaged. I have used both in different systems and I don’t think there is a single right answer; it all depends what is right for you in your system (though Microsoft officially recommend the use of Managed Solutions (see https://msdn.microsoft.com/en-gb/library/gg334576.aspx#BKMK_UnmanagedandManagedSolutions)

Managed or Unmanaged

There are plenty of posts by more experienced heads than I discussing the differences between the two solution types, and weighing up the pros and cons of each, so I’d recommend seeking them out for further reading.

The only real piece of advice I’d like to add is that Unmanaged Solutions are easier to manage (pun intended), and you can always convert an Unmanaged Solution to a Managed Solution in future if needs be; just be aware of the potential for unintended changes to be added to the solution by over-zealous developers during UAT or while it is live in the Production environment, leading to solution disparity.  You should weigh up the control of Managed Solutions versus the flexibility of Unmanaged Solutions in any decision-making.

Version Numbering

Aligned with the use of Solutions is to ensure you have a rigorous and consistent approach to versioning. Using version numbers makes it a lot easier to manage your solutions, and to identify issues if and when they arise.

My approach is to use the standard of Major.Minor.Release (#.##.####), as defined below:

Version Numbering

Major – this should be incremented every time you introduce some significant functionality, change the phase of the project, or if you upgrade to a new version of CRM

Minor – this should be incremented every time you release an update to the solution that introduces minor features or changes that are building on existing functionality.

Build – this should be incremented with every single release, and should cover bug fixes, etc.

There are lots of different version numbering schemes available (e.g.  Major.CRMRelease.Minor.Release, Year.Month.Day.Revision, etc.), and it doesn’t really matter which one you use –  the important thing is that you use a consistent version numbering scheme in your development, and that everyone working on your system understands the numbering scheme.  I’ve previously inherited a system developed by a Microsoft Partner who had used 4 different numbering schemes when deploying solutions which gave me no end of headaches.

Documentation

I’m sure I’m not alone in not particularly enjoying writing documentation, but also cursing out any other developers who dare to release a solution without documentation.  As important as it is to develop system updates, writing appropriate documentation is equally important.  I’m not suggesting that you include War & Peace with your releases, but adding a few notes on the release history to the solution description field will go a long way to helping both future you, and anyone else who may be working on your system

Release Notes

Having these little notes provides a perfect aide-mémoire when you come back to work on the solution in six months time, and taking the two minutes to do it now will save you hours down the line.  I like to keep a more detailed release note history to go with my solutions too; for a great example of detailed release notes you could look at ClickDimensions.

Quick Tip: To make your life a lot easier when writing release notes, the MetaData Document Generator plugin for the XRMToolBox is a lifesaver

My next post will outline my tips related to new entities, fields, views, etc.