Time Travel with Workflows: Accessing “Before” and “After” Values

1 comment

Posted on 14th September 2014 by Jukka Niiranen in Tips

, , , ,

Dynamics CRM has had a built-in auditing feature since the 2011 version, which provides a really handy tool for situations where someone needs to investigate the changes that have taken place on field values of a specific record. By default auditing is not enabled, but I recommend you to seriously consider enabling it for all business critical entities like accounts and contacts, since without it there’s not much to go by if you ever need to track down any intentional or unintentional updates made to your CRM data.

CRM_workflow_audit_1

Auditing is a great tool for capturing the “fire hose” of data updates that are taking place in CRM. However, since the audit data is not stored in actual CRM records but rather in a denormalized state inside the audit database tables, it’s not accessible for any type of reporting or business process logic. If you know what record to look for, the related Audit menu will give you the information. If you are an administrator and have access to the Audit Summary View, you can also use filters to narrow down the audit data stream and hunt down the events that are relevant to your investigation. Very useful for resolving issues in the data, but not so practical for simply staying informed about updates that are of interest to your role in the organization.

Workflow processes can also be used for tracking specific changes made on the CRM records, just by setting the workflow to start when a particular field or a set of fields change. You can then perform any notification action you see appropriate, such as sending an email, creating an Activity Feed post, adding a note, appending a description field etc. Almost like auditing, but on a much more granular level, and also something that you can report on if necessary. One limitation compared to auditing, though, is that you can only see the new value of each field but not the previous one. So, you can’t produce a similar view that auditing provides, with the old and new values side by side.

Or can you? With the launch of the CRM 2013 version we gained a whole new category of workflow processes, called real-time workflows. These behave much in the same way as custom plugins, as they are executed synchronously as part of the event pipeline of the CRM platform rather than via the asynchronous service that the traditional workflows use. The extra benefit we gain from this, aside from the fact that the workflow logic is executed immediately, is that we now also have the ability to choose whether the real-time workflow should be started before or after the event. This allows us to actually read the data that was in a field before the update took place. Sounds like a cool little feature? Well why don’t we take it our for a spin then and see what we can achieve with it.

Tracking Account Name Changes

Let’s consider a scenario where we would be interested in tracking the changes performed on the account name field. We have decided to leverage the Activity Feeds feature to post a message on the record wall every time an existing account has its name field updated. As a part of this post, we need to provide both the old name and the new name, so that the users can easily associate this particular account as a customer they’ve previously done business with under a different name.

Since we need a place to store the old name of the account for the purposes of formulating the post’s content, first we’ll add a new custom text field for the account entity, called “Old Name”. Then we’ll open up the workflow editor and create a new real-time workflow process for the account entity, called “Account Name Change: Store Old Name”. The important part here is that we’ll set this workflow to be run when “Record fields change” with a box ticked for the “Account Name” field and change the “Start when” value to “Before”. The actual workflow actions only need to do one thing, which is to copy the value of the standard “Account Name” field to the new “Old Name” field. Nothing else.

CRM_workflow_audit_2

Next we’ll create a second workflow, called “Account Name Change: Post Old & New Name”. We’ll set it to run in real time for the account entity, just like the first one. We’ll even associate it with the very same event, meaning the change of the “Account Name” field. The difference will be that we’ll run this workflow “After” the event. Again, the actions that the workflow will perform are very simple, as we’ll only need it to create a new Activity Feed post record. Here’s how the message will be configured with the dynamic field values from the account record:

CRM_workflow_audit_3

So, we now have two real-time workflows running for the same entity, for the same field change event. Is this a smart thing to do? Will the universe by any chance collapse onto itself as a result of this reckless twin workflow configuration that we’ve built? Well, there’s only one way to find out! Let’s activate these two workflow processes, go to an account record and change it’s name.

CRM_workflow_audit_4

After the name is changed, we can click onto the Activity Feed’s auto posts column to refresh the post view. There we discover a post created by our second workflow process, containing both the “before” and “after” names for this account. Success! If we keep feeding further update events to this workflow duo, we can see that the post message is always updated to contain the last two names for this account in search of its true identity.

CRM_workflow_audit_5

If our second workflow process would have been an asynchronous process instead of real-time, the results might be different, though. As I’ve experimented in a previous post, “Auto-Numbering with CRM Workflows: Real-Time vs. Asynchronous”, the record data and execution order for traditional background workflow processes may not always be consistent, due to their asynchronous nature. By using a real-time workflow we are guaranteed to receive a ticket to the front row seats of CRM’s event execution pipeline. In practice this means the following:

  • “Account Name Change: Store Old Name” – this workflow is executed at the pre-operation stage, which means that it sees the record as it was before the update event took place. Therefore when it reads the account name field it still has the old value stored. Furthermore, because the workflow is completed before the actual platform event for the account update takes place, it can inject a new value into the “Old Name” field and have it committed to the database as a part of the original transaction.
  • “Account Name Change: Post Old & New Name” – this workflow is executed at the post-operation stage, meaning after the update has already taken place, but before the transaction is completely over. It receives an image of the account record where the standard “Account Name” field is populated with the new value the user has entered and the “Old Name” contains the value updated by the first workflow in the pre-operation stage.

As this example scenario demonstrates, while a workflow can’t directly compare the “before” and “after” values of a record, there is actually a workaround available where you could pass the old value from the pre-operation workflow to the post-operation workflow. You could then perform a comparison of the values with the tools that the workflow editor offers (or extend it via custom workflow activities) and alter the outcome of the transaction based on the business logic. If needed, you could even cancel the operation and show an error message to the user if the old & new values are violating the rules of your business processes.

To close things off, it’s important to keep in mind that just because you can do something with a workflow instead of custom code, it doesn’t mean it would always be the right tool for the job. Aside from the greater level of flexibility that a plugin will give you for comparing and manipulating the data during the update event, there are also performance considerations you should be aware of before pushing a ton of real-time workflows into your production CRM system. I recommend reading this post by CRM MVP Scott Durow: Real Time Workflow or Plugin?

Troubleshooting CRM for Tablets Login Issues with ADFS

1 comment

Posted on 28th August 2014 by Jukka Niiranen in Configuration |Tips

, , , , , , , , ,

All CRM 2013 and CRM Online customers have access to the CRM for Tablets app that’s available for Windows 8, iOS and Android devices. Since it’s an app from the new age of mobile computing, the users can simply download it from the respective app store of the platform provider and install it. Configuring the app to connect with your CRM organization can however prove to be a bit trickier task than this.

If you’re using CRM Online hosted by Microsoft in their data centers, connecting the tablet app to the CRM server in the cloud is usually a straightforward process. Just enter the URL for your organization, then punch in the credentials and off you go. Well, to be more precise, upon your first connection the organization you’ll be taken to a screen that asks you to hold on tight while the app is being set up. This is because all of the metadata related to your CRM organization’s customizations first needs to be loaded, so that you’ll see your own CRM instead of some generic, hard coded menus and fields. This step can take quite a while to finish, but just hold on tight, it’s worth it.

If you’ve got your own on-premises CRM server or you’re logging to CRM Online via your on-premises Active Directory credentials, you may need to work a bit harder to achieve connectivity between the CRM for Tablets app and the CRM server. This is because in both of these scenarios you will have ADFS (Active Directory Federation Services) sitting there in the middle, processing your login request and validating your user credentials. Of course the same technology is also used behind the pure cloud CRM Online service, but MS has done the configuration work for you, whereas with on-premises components you’ll be in charge of performing this.

CRM_for_Tablets_Were_Sorry

Recently I was faced with a situation where a customer had deployed Dynamics CRM 2013 SP1 on-premises and done the Internet Facing Deployment via Windows Server 2008 R2 ADFS 2.0, published via Forefront TMG (Threat Management Gateway) 2010. Accessing CRM via the web client through the IFD address was working as expected, so was the CRM Outlook client. CRM for Phones was connecting without issues and I could even connect to the server via tools like XrmToolBox with no issues. There was just one problem: the CRM for Tablets wouldn’t connect to the server, no matter what. In the process of troubleshooting this particular scenario I learned a thing or two about the tablet app connectivity as well as server configuration tasks, so I thought I’d share my findings here on my blog. I’m by no means an expert on anything surrounding ADFS , but I’m stubborn enough to keep searching for answers until I find some from the great wide web.

RTFM – Read The Friendly Manual(s)

First of all, you’ll need to know your ADFS version, since there’s a few new hoops you’ll need to jump through when working with Windows Server 2012 R2 and the latest ADFS 2.2 (sometimes referred to as ADFS 3.0, since official version numbers seem to have been dropped by MS, in favor of just shipping ADFS together with Windows Server releases). The architecture of ADFS has changed considerably from earlier 2.0 and 2.1 versions, with no more IIS in the background, so the configuration process for CRM IFD also differs from the previous experience.

You’ll find the extra steps listed on this article: Configure Windows Server 2012 R2 for CRM mobile clients. On the ADFS 2.2 server you’ll need to enable forms authentication manually, since it’s not enabled by default, like in previous versions. Then you need to run a Powershell script on the CRM server to configure the OAuth provider. Finally, you should register the CRM for Tablets app ID’s with the ADFS server via another Powershell script.

Tablet_AD_login_promptYou may run into an issue with the login process where the user is prompted for their AD credentials via the standard Windows domain dialog window repeatedly. This is because of some incorrect authentication settings that apparently are caused by the CRM IFD configuration process itself. To avoid these issues, you should run a repair installation on the CRM 2013 server with the Web Application Server role deployed, after you’ve done the IFD configuration and before you attempt to log in with the CRM for Tablets app.

Another aspect is the requirements imposed by the new Windows 8.1 version of the tablet app. Because of the changes on the OS layer, it’s no longer possible for Win8 apps to connect to any random server at will, but rather the developer has to specify the URL’s of these servers before publishing the app to the Windows Store. For CRM Online the domains for the service are known in advance, but for an on-premises deployment they could be absolutely anything. To overcome this, you’ll need to add a registry entry onto your device before attempting to connect to your server, otherwise the tablet app will just sit there and do nothing. Go to the page Set up CRM for Tablets, expand the section “what the admin needs to do” and grab the Powershell script from there. Running it on your device will prompt you for the CRM organization URL and create the necessary registry key for you. (more…)

Access Option Set Labels in Dynamics CRM OData Feeds via Power Query

4 comments

Posted on 12th August 2014 by Jukka Niiranen in Tips

, , , , , , , , , ,

If you’re using Dynamics CRM Online without direct access to the underlying SQL Server database, there may have been a few occasions when you’ve wished for more flexible options for reporting and customer data analytics than what is possible via configuring CRM charts or developing Fetch XML based reports in Visual Studio. Late last year Microsoft enabled support for accessing the secured OData feeds from CRM Online via the Excel Power Query tool, which opened up some interesting options for building modern BI solutions on top of the CRM data up in the Microsoft cloud.

CRM_OData_Excel

For a non-developer who hasn’t leveraged OData before it might come as a bit of a surprise that not all of the business data is necessarily readily available via the feeds when examining a particular CRM entity. One crucial thing that’s missing is the option set values. More specifically, it’s the label values that are not accessible via the entity tables retrieved via the OData feed, as all we have access to are the numerical values representing the labels (1, 2, 3, 4… 10001), but not the human readable versions.

CRM_OptionSet_Value_Label

From a reporting perspective, it’s very likely that any chart or table that you wish to build is going to leverage one or more option set fields. Formerly known as picklists, these are basically the dropdown fields on CRM entity forms that allow the user to select one value from a list of predefined values. Much more convenient for reporting purposes than free text fields, as I’m sure you’d agree.

So, what are our options then? We could of course manually create new tables into the Excel workbook that store the mapping of ID values and labels, but that just doesn’t sound like a fun exercise at all. More importantly, that would only give us a static list of option set values that couldn’t adapt to the changes in CRM customizations. Nope, not a good approach from report maintenance perspective, so let’s not go there.

As a bit more efficient workaround we could be adventurous and import a copy of the CRM solution file containing the entity customizations as an XML data source into Power Query and then pick out the necessary mappings from there. Due to the power of Power Query, this would actually technically work, and we could even set it to reference a file location from where the latest customizations would dynamically be imported upon workbook refresh. Still, that would leave us the burden of setting up an automated export system that would produce the customizations.xml file to reflect the latest changes.

After a bit of poking around in the OData feed data source, it turns out the optionset labels are actually included there. The tricky part is that they’re not simply a [Record] link that you can drill into and expose the values from your existing entity data set. Nope, they reside in a specific table of their own, called PicklistMappingSet. In this tutorial I’ll show you how to retrieve the data for a “Leads by Source” chart created with Power View, taken from an OData feed data source pointing to Dynamics CRM Online, using Power Query to pull the data into a data model built with Power Pivot.

1. The Data Source: Power Query

PowerBI_CRM_Odata_1To follow the steps you should have the latest Power Query version installed in your Excel client. I’ve already covered how to access CRM OData feeds from Power Query in a previous article, so please refer to that one if you haven’t done the exercise before. After connecting to the OData feed URL we should select the tables that we want to work with from the data source navigator pane. By minimum you should grab the LeadSet and PicklistMappingSet to build the chart.

The problem with CRM and OData is that by default the feed will pull down each and every record in the table. The query performance is less than stellar with CRM Online and if you have a high number of leads (status doesn’t matter, also the closed ones will get downloaded), you might be waiting for a while before the query is completed. If you want to move on a bit faster then check out this great tip by Andre Margono on how to set up a query filter for the Dynamics CRM OData query (for example, only active leads).

The real beauty of Power Query is in the query steps you can use for manipulating the workbook queries. Before we go there, though, let’s create a duplicate of the PicklistMappingSet query. This will make it easier for us to map the values into our actual leads table later on, as well as preserve the original option set value table available for further queries.

PowerBI_CRM_Odata_2

Open up the new duplicate query you’ve added into the edit mode by double clicking on it, which launches the Power Query query editor (yes, query is the word of the day). The first step we’re going to add for the query involves expanding a column that only shows a green “Record” value by drilling into it from the small icon next to the column label. Do this to the ColumnMappingId column and just load up all the columns found from behind it.

PowerBI_CRM_Odata_3

You’ll see the Record column transformed into three new columns as a result of drilling down into the data. The column we’re interested in is ColumnMappingId.Name, which has the names for all option set fields in our source CRM system. For this example we want to see the LeadSource field, so add a new filter for this value, just as you would in a normal Excel data table.

PowerBI_CRM_Odata_4

By now you might have noticed that the Applied Steps box in the Query Settings pane is collecting all of the actions that we’re performing on the query and storing them as steps. If you make an error in your selection, just click the delete symbol next to the steps to get rid of it. (more…)