Using TraversedPath with Branching Business Flows

Using TraversedPath with Branching Business Process Flows


I recently ran into an issue with Opportunity records and branching Business Process Flows (BPFs) where all the stages were being displayed on the record, instead of what stages the record had gone through. The solution was interesting and I’d like to share the issue and resolution that I came up with.



The scenario I was working with involved using an external SQL Server Integration Services (SSIS) package that was creating new Opportunity records that were associated with a branched BPF in Dynamics 365. This external process would create the opportunity record and attempt to set the current stage to one of the stages in a particular branch. This was being done by setting the StageID field to the GUID of the stage that I wanted to make active.

The problem was that when viewing the created opportunity record, the BPF would display ALL the stages from both branches (I had 2 separate branches). Obviously, this was not desirable and was very confusing.



After analyzing the fields in Dynamics 365 for the opportunity record I had created, it turns out the reason why this issue arises is due to a field on the Opportunity entity called TraversedPath. This field is a comma-separated list of GUIDs that represents the BPF stages the opportunity record “traversed” through. I was not setting this field at all when creating the opportunity record, and therefore Dynamics 365 was displaying all the BPF stages from both branches (due to no entry in this field).

Once I modified my SSIS process to grab all the BPF stages I wanted the opportunity record to traverse through and set the TraversedPath field, the BPF stages (for the specific branch) were set and displayed properly.

I hope this article helps others in quickly determining the cause of the Business Process Flow issue I ran across.

Thanks for reading!



No-Code Configurations for Dynamics CRM – Part 2

This is the second article on how you can configure Dynamics CRM without writing code. In Part 1, I described the configuration options with System and Custom entities, as well as explaining how to use Business Rules. In this article, I will describe how you can configure Dynamics CRM using Workflows and Dialog, Business Process Flows, and Dashboards/Reports.


Workflows and Dialogs

Dynamics CRM provides an engine that can run business processes automatically or interactively. This engine allows administrators to create and manage these automated business processes in order to provide customized functionality to the CRM environment.

Workflows are business processes that run automatically, with little or no interaction with the user. These processes can be configured to run in the background or in real-time, and can be triggered automatically (based on a specified condition). They can also be started manually by a user.

For example, I can create a workflow by opening a solution in CRM and navigating to the Processes area. Once there, I can click on the New button to create a new process (see Figure 1).


Figure 1: Creating a new Workflow


New workflows start in a Draft state, which means they are not activated in the system. This allows you to create the workflow and add your logic to it, before you activate it. Figure 2 shows an example of a workflow in Draft state.


Figure 2: Workflow in Draft state

There are a number of areas when configuring a workflow that define what it does and how it runs. First off, you give it a name so you can recognize your workflow. Under the Available to Run section, you see there are a few checkboxes that allow you to define how the workflow will run in the CRM system. Selecting the Run this workflow in the background option will specify that the workflow runs in the background asynchronously. Unchecking this box specifies that the workflow will run as a real-time workflow, which means it runs synchronously (immediately).

The checkbox under the Workflow Job Retention section allows you to save (or delete automatically) the completed workflow jobs after it runs each time. Checking this box clears these records immediately, so that you can save disk space in your system. Be careful though – I have found that it’s nice to have these records around for a period of time, as you can determine if the workflow is running successfully.

The Entity and Category fields tell you what entity in CRM you are running the workflow against, and that the Category is a Workflow.

Under the Options for Automatic Processes section, you can define the workflow scope. This tells the workflow to run for a specific set of records, as listed below:

  • User – workflow only runs against the records owned by the current user
  • Business Unit – workflow only runs against the records owned by the business unit the current user belongs to
  • Parent: Child Business Units – workflow only runs against the records owned by business unit and the child business units the current user belongs to
  • Organization – workflow runs against any record in the organization

There are also checkboxes that specify what triggers the workflow. You can select when a record is created, record status changes, record is assigned, or when a record is deleted. You can also select when specific fields on the entity record change.

Once you’ve configured how the workflow should run, the main part of the workflow is defining what it does when it runs. This is done by creating one or more steps. Figure 2 shows the area in the workflow that allows you to create steps. Here is where you can add steps and their conditions, along with actions on what each step does. For example, Figure 2 shows a step that indicates that if the Opportunity:Presales Resource field = Yes and the Opportunity:Presales Resource field contains data, then the workflow should then check to see of the Opportunity:Probability <= 20. If it is, the action of sending an email is performed.

Once you’ve added all your steps and have saved the workflow, you can publish it to activate it into the CRM system. At this point, you would activate your workflow in a development system to test its functionality (before deploying it to a production system). You also have the ability to see when your workflow has executed (in your solution that contains the workflow), as shown in Figure 3. This view allows you to see what instances of your workflow has run, along with the status of their execution. You can drill into each record to see the details of the workflow execution, such as what steps performed successfully, or where it failed.


Figure 3: Workflow Process Sessions


Dialogs are very similar to workflows, as they are also business processes. However, these processes are designed to run and interact with the user. You create a dialog in the same way as a workflow process (except you select Dialog instead of Workflow category), and you will notice in Figure 4 that there are less configuration options than in workflows.


Figure 4: Configuring Dialogs

You still create steps in a dialog with actions, but you will notice there is a Prompt and Response selection, under a Page step. This is where you create and configure the dialog prompts that the users will see when the dialog runs. Figure 5 shows an example of a dialog prompt. You also have the ability to define what the prompt will say and how the user will respond.


Figure 5: Dialog Response Prompt Configuration


Business Process Flows

Business Process Flows are another type of process in Dynamics CRM, but they differ in how they provide functionality to users. I like to think of Business Process Flows (BPFs) as a guide in Dynamics CRM that helps users follow a set of steps to complete a process. BPFs have a visual component as well as back-end functionality that perform tasks or actions when the user is interacting with the BPF. They differ from workflows in that they have a UI component that users interact with while using the system. They also differ from dialogs in that they are persist on specific record types, meaning they are part of the displayed form. They are not triggered by an action and therefore do not “popup” a window to request that users add information at that point.

Administrators have the ability to create custom BPFs in Dynamics CRM, or you can use one of the following standard BPFs that come with Dynamics CRM. Some examples of standard BPFs are:

  • Lead to Opportunity Sales Process
  • Opportunity Sales Process
  • Phone to Case Process

The standard BPFs are good for general processes that you can have CRM users follow, but the real power of BPFs are displayed when you create a custom BPF that matches your business process. Figure 6 shows the Opportunity Sales Process that comes with Dynamics CRM.


Figure 6: Opportunity Sales Process BPF


Creating a Custom Business Process Flow

Creating a BPF doesn’t require specific coding skills, as CRM has provided an editor that allows administrators to create these processes without having to write code. You can navigate to the Settings area in CRM and click on Processes. This will navigate you to the My Processes view, which will show you a listing of processes owned by you:


Figure 7: My Processes View

From here, you can see which processes are in the system that are owned by you. This includes both workflows and BPFs. You can create a new BPF by clicking on the New button in the upper right. Once you click this, a designer is displayed that allows you to create a custom Business Process Flow (see Figure 8).

Business Process Flows consist of stages and steps. Stages are the different parts of the BPF that users can navigate back and forth from. Stages also contain one or more steps. Each step represents a piece of data that can be entered for the entity record the BPF is associated with. Figure 8 shows an example of a custom BPF with stages and steps:


Figure 8: Custom BPF with Stages and Steps

Note that when you create steps for a stage, you specify a field on the entity record the BPF is associated with. You can also designate whether the step is required. This means that the user cannot advance to the next stage until a value is entered for each of the required step fields in the stage (commonly known as stage-gating).

You will also notice from Figure 8 that there is a branched stage in the BPF. Branching BPFs gives the ability for a stage to only be displayed (and active) if a certain condition is met. In our example, the Procurement Approval stage is only displayed if the Budget Amount value (Step in Stage 1) is greater than $100,000. Figures 9 and 10 show what this branching looks like when the Budget Amount is above or below the $100,000 threshold.


Figure 9: Custom BPF with Branch Hidden


Figure 10: Custom BPF with Branch Displayed

Once you’ve created your custom BPF, you must activate it for it to be used in the system. All new BPFs you create start in a Draft state. You can have more than one BPF active in the system for an entity, and if you do there is an order to which BPF will be attached to the entity record (based on the first BPF the user has access to, via security roles). Figure 11 shows the Process Order Flow dialog for a custom BPF:


Figure 11: Process Flow Order Dialog


Dashboards and Reports

There are two different ways Dynamics CRM displays data in the system: Dashboards or Reports. This section will describe each and how they are used.



Dashboards display data from CRM as an overview of business data. Users can see data that they can perform actions against, and this data is usually displayed across the organization. If you wish to see CRM data at a glance, creating a dashboard will help you do that.

There are two types of dashboards in CRM: System Dashboards and User Dashboards. System Dashboards are created by a System Administrator or Customizer and these dashboards are visible to the entire organization. User Dashboards are created by CRM users and are only visible to the CRM user who created it. User Dashboards can be shared by the CRM user to other users, so that others can view the dashboard.

For either type of dashboard, CRM users have the ability to set the default dashboard they wish. System dashboards can be marked as the default dashboard for a specific area (Sales, Marketing, etc.) but if a user marks a different dashboard as their default (System or Custom) it will override the System dashboard that is marked as the default. Figure 12 shows an example of a default System dashboard.


Figure 12: Dashboards in Dynamics CRM

Dashboards can also be created to display either tabular data (like a view) or graphical data (like charts). Other types that can be included in a dashboard are Web Resources or IFrames. Users also have the ability to drill down into a dashboard chart to see the tabular data it represents. This gives the user the added functionality of being able to examine specific sets of data in a dashboard in more detail. Figure 13 shows a System dashboard with both tabular data and a chart:


Figure 13: Charts and Tabular Data in Dashboards in Dynamics CRM

One thing to remember when viewing dashboards: The data it displays must be accessible by the user viewing the dashboard. What this means is that if a user doesn’t have the security role to view a particular set of data, the dashboard itself will still be accessible but the data it displays will not be. Usually an error is displayed in the specific dashboard window that states the user doesn’t have the proper permissions to view the displayed data.



Dynamics CRM also has the ability for users to create and run reports. These differ from dashboards as they are a snapshot of data at the time the report is run. They are also typically used to print out (or export) data from the CRM system. Creating reports in CRM is a large topic, so I will focus on how users can run reports.

Users have the ability to run a report that is in the system. This is done by navigating to the Reports section (i.e. Sales -> Reports) which will display a view of the Available Reports the user can see:


Figure 14: Available Reports View in Dynamics CRM

Clicking on one of these reports displays a dialog that allows the user to modify the (pre-defined) criteria for the report, and then run the report (by clicking the Run Report button in the lower right):


Figure 15: Running a Report in Dynamics CRM

Once the Run Report button is clicked, CRM processes the request and will display the report results. At this point, the user has the ability to perform a number of actions on the report, such as modifying the filtering criteria (depends on the report), saving the report results to a file (Excel, PDF, etc.), or refreshing the data. Figure 16 shows an example of a displayed report:


Figure 16: Report Results in Dynamics CRM

The above description for running a report in CRM is for reports that run on all sets of data in the system. There is another type of report in Dynamics CRM that allows users to run a report against a single record in the system. These types of reports are useful for displaying a snapshot of detailed information about a specific record. This is done by opening a specific record in CRM (such as an Account record, for example). If you click on the command bar on the top of the page, you will see a section titled Run Report (see Figure 17). Clicking on this will expand a sub-menu, which will then display a listing of all the reports you can run against this record. Note that sometimes there may not be anything listed here if these types of reports have not been created in the system. Clicking on a selection here will display the report results. The user then has the ability to perform specific actions against the report results, as described earlier.


Figure 17: Selecting a Current Record Report in Dynamics CRM



As you can see, there are a multitude of different ways users and administrators of Dynamics CRM can configure the system without having to write a single line of code. These no-code configurations make Dynamics CRM a very powerful platform for tailoring the system to a business’s needs, and can take you very far into the realm of a custom CRM system for your organization.



No-Code Configurations for Dynamics CRM – Part 1

Microsoft has architected the Dynamics CRM platform to allow administrators to customize the environment to fit business needs, without requiring complex coding skills of a developer. This no-code configuration option is very powerful and allows the CRM environment to be built with a large portion of custom functionality before having to rely on custom scripting or coding practices.

This is the first article (of two) where I am going to detail out the different areas of no-code configurations, to give you an idea of what you can do with this type of customization in Dynamics CRM. Keep in mind that this list is not all-inclusive; I am only covering the larger areas of no-code configurations. I will be covering System and Custom Entities, along with Business Rules in this first part. Part 2 will cover Workflows and Dialogs, Business Process Flows, and Dashboards/Reports.


Configuring System Entities

Dynamics CRM comes with a set of system entities when installed. These entities make up the core of an out-of-the-box install. While you cannot delete a system entity (which is a good thing), you are able to configure them to extend their functionality. Here are some of the things you can do with system entities:

  • Create custom fields
  • Modify the Display Name of system fields
  • Change whether system fields are required/optional
  • Change the minimum/maximum range values on fields of certain data types (decimal number, for example)

There are some restrictions when configuring system entities, which are in place to protect and maintain system stability. Here are some of the things you cannot do:

  • Delete a system entity
  • Delete a non-custom field on a system entity
  • Change the data type of a field
  • Change the name of a field once it has been created

You can also create custom forms and views to extend how a system entity is displayed in the environment. Custom forms can be created to replace system forms to provide a custom look-and-feel. Custom fields can be added to system forms if a totally new form is not needed. System views can be configured to display additional fields or their filter criteria can be modified to provide custom data results. Custom views can also be created to be used in place of system views if other criteria or entity fields need to be displayed in the system. Please note that the filter criteria you can create are limited to what you can configure in the dialog window.


Figure 1: Example of System Form editor with Custom Fields

Another item to keep in mind is to make sure you publish any changes you make to the system, when you are ready to test them. Dynamics CRM allows you to make changes to the system without actually committing them. This allows you to load/add your changes and make edits before you actually apply them to the system. If you make changes and don’t publish them, they will not work as expected.


Figure 2: Example of Default Solution with Publish All Customizations button

Creating Custom Entities

If the system entities that Dynamics CRM provides is not enough for your requirements, you can also create your own custom entities. This is where CRM shines in terms of no-code customizations – you are able to create fully custom entities in the system without writing a single line of code! This allows you to configure your CRM system to fit almost any complex business requirements you may have.

There are a vast number of configuration possibilities here, but the most common I have seen is where custom entities are created to support the standard entities in the system. Sure, you can use custom entities completely, but why not leverage the functionality that Microsoft provides with Dynamics CRM, and then extend that functionality by creating custom entities that fit your need?

Please note that you are still not able to do certain things in the system, even with custom entities. For example, you are not able to change the data type on a field once it has been created. You are also not able to change the name of a field once it has been created.

Let’s go through an example. Suppose that we want to have our CRM system be able to display and keep track of automobiles owned by contacts. Since CRM doesn’t provide an entity out of the box to record automobiles (ignore the Products entity for this example J ), I can extend the functionality by creating a custom entity of my own to handle this.

The first thing I want to do is to design my custom entity, so it meets my business requirements. I would like to provide the following:

  • Allow a contact in CRM to own one or more automobiles
  • An automobile record in CRM has the following properties:
    • Make
    • Model
    • Year
    • Mileage
    • Owner
  • An automobile record in CRM can only be owned by one contact

Once I have these requirements, I can create the custom entity in CRM. This is done by creating an unmanaged solution (in the Settings->Solutions area) – I will call the solution AutomobileTest:


Figure 3: Creating a custom entity

Once you save your new custom entity, the dialog then allows you to create fields for the entity. Dynamics CRM creates some fields by default for you, such as the Name, CreatedBy, CreatedOn and Status.

As you create custom fields on your entity, note that the system prepends a publisher designator (“cncy_” in our example). A publisher is needed in order to create solutions in Dynamics CRM, and they are used to help manage customizations (managed or unmanaged).


Figure 4: Custom AutomobileTest solution with custom Automobile entity

Once the fields for our custom entity are defined, we can define the relationships between AutomobileTest and Contact entities. Since the requirements state one Contact can own multiple automobiles, and that each automobile record can only be owned by one Contact, that defines a N:1 relationship on our custom entity.


Figure 5: N:1 relationship between Automobile and Contact entities

Note that when creating this relationship, you must define the Primary and Related entities, along with a Display name. You can specify other attributes about the relationship, such as if it’s searchable, or the type of behavior (parental or referential).

There are 3 types of relationships available in Dynamics CRM:

  • 1:N (One-to-Many) – relates one entity record (the Primary) to one or more different entity records (the Related).
  • N:1 (Many-to-One) – the converse of 1:N relationships. This is really the same type of relationship as 1:N, just from the perspective of the related entity.
  • N:N (Many-to-Many) – relates many entity records to many different related entity records. Sometimes referred to as an Intersect entity.

Entity relationships are an important part of the Dynamics CRM schema, as they allow you to relate records to each other. This also allows the ability to define the schema to fit your business processes.


Business Rules

We now know how to configure standard or custom entities in Dynamics CRM that function in a standard and consistent manner. What do we do if we want to apply some custom logic on these entities? In the past the answer would have been to write some Javascript script code to implement this custom logic. Fortunately, Microsoft has introduced a declarative interface that allows you to create custom logic without writing Javascript. These are called Business Rules and are new (as of CRM 2013).

Business rules provide an easy way to evaluate business logic in Dynamics CRM, without the requirement of using custom code scripts. This can be performed on either the client or the server side. CRM provides a declarative interface that allows creation of these business rules, which can be applied against entity or entity fields. Please be aware that Business Rules in CRM do not replace custom scripting in CRM, as they are not able to perform all the things that custom scripting can perform. They can perform some of the more common things that scripting was providing in the past, as listed here:

  • Set field values
  • Clear field values
  • Set field requirement levels
  • Show or hide fields
  • Enable or disable fields
  • Validate data and show error messages

Also note that Business Rules will only work for Updated Entities ( or custom entities.

In order to create and configure a Business Rule in CRM, you need either the System Administrator or System Customizer security role. You will also need the Activate Business Rules privilege in order to activate a Business Rule. The rules you create are not applied until you activate them. Conversely, if you wish to edit a rule, you need to deactivate it first.

There are a few different ways you can access Business Rules in Dynamics CRM.

  1. Via Solution (Entity level)6
  2. Via Solution (Entity field level)7
  3. Form Editor (Entity level)8
  4. Form Editor (Entity field level)9


Business Rules have a scope – this determines the context of where the rule runs in Dynamics CRM. The different scope selections are:

  • Entity – runs on all forms and server
  • All Forms – runs on all forms
  • Specific Form – runs on that specific form

Note that you cannot select a scope of multiple specific forms. If you choose Specific Form, the rule will run on that one form only. This is also the default scope if you create a Business Rule via the form editor. Choosing All Forms means the rule will run on all the Main forms as well as the Quick Create forms, provided all the referenced fields are present on those forms.

Support for If/Else branches and AND/OR logic is supported for Business Rules. You can create logic in your business rules to check for certain conditions and perform specific actions if those conditions are met.


Figure 6: Business Rule with If/Else branch

There are a few limitations to be aware of. First off, nested If/Else statements are not supported. You can only have one level of If/Else statements. Grouping of expressions in a condition is not supported. Expressions can be combined using AND or OR, but not both. You also have a limit of 10 if/else conditions for a single Business Rule.

Conditions and actions are also supported for Business Rules. Conditions are the checks that you create to determine when specific actions are executed. Once a condition is met, the Actions you create will run as part of the rule. If a condition is not met, then the logic of the rule will skip over the action for when that condition is met.

There are a number of actions available:

  • Show error message – used to set error message text when a field is not valid
  • Set field value – used to set the value of the field. There are 3 types here:
    • Field – sets the value of one form field with the value of another field
    • Value – sets the value of one form field with an entered value
    • Formula – sets the value of one form field with a simple calculated field. Only valid for numeric or date data types
  • Set business required – used to change the requirement level for the field. Options are Business Required and Not Business Required
  • Set visibility – used to change whether the field is displayed/not displayed on the form. Options are Show Field and Hide Field
  • Lock or unlock field – used to change whether the field is enabled/disabled on the form. Options are Lock or Unlock

There are some general limitations when creating business rules:

  • Business Rules only run when the form loads or when field values change. They don’t fire on save, with the exception of when the scope is set to entity level
  • Business Rules only work with fields. If you wish to perform logic against tabs and sections you have to use custom scripts
  • If you set a field via a Business Rule, the system will not fire that field’s OnChange This is to protect against circular references
  • Business Rules that reference fields not present on a form will not run. No error will be displayed, so use caution if you’re removing fields from a form – always check that they are not being used on a Business Rule

Finally, you should be aware of the order in which logic is applied to Business Rules, in relation to system and custom scripts. First off, any system scripts are applied first. Next, any logic in custom form scripts is then applied. And finally, logic in Business Rules is now applied. If there are multiple Business Rules, they are executed in the order that they were activated.

In conclusion, you can see that using no-code configurations in Dynamics CRM has enormous power and flexibility in tailoring the environment to almost any business need. Part 2 of this series will discuss workflows and dialogs, business process flows, as well as dashboards and reports.






Customization Options for Dynamics CRM

This is the first post in a multi-part series on how to create customizations in Dynamics CRM. If you’ve ever used Dynamics CRM, you quickly learn that while it’s got a lot of features and functionality out-of-the-box, the system really needs to be customized to your specific environment to fully meet your business needs.

Once you realize this, your next questions are probably “How do I customize CRM?”, “What are my options to customize CRM?”, or “Do I have the skills to customize CRM?”. These are all great questions, and you should be asking them to ensure your efforts are set on a path to success. I will list out the different ways you can customize your Dynamics CRM environment at a higher level, so you can get a general idea of what area fits your level of customization (and skill).

There are three main areas of customization for Dynamics CRM, described below:

No-Code Configurations

The first place to start when you are considering customizing your Dynamics CRM environment. Dynamics CRM provides a very robust platform to create customizations on the out-of-the-box entities, forms and fields, without having to write complex code solutions.

You can get very far with these types of no-code configurations. I’ve seen full CRM implementations that use no-code configurations as their only customizations, and they don’t have one line of custom code at all! Here are some of the things you can do with no-code configurations:

  • Extend standard entities to add custom fields
  • Create your own custom forms to extend standard entities
  • Create your own custom entities
  • Extend standard views to display additional information
  • Create your own custom views
  • Create your own custom business process flows
  • Create workflows that provide custom logic
  • Create business rules that enforce data integrity

Part 1 can be found here.

Javascript Customizations

If you find that no-code configurations do not satisfy the customizations you need, the next area you should consider is creating custom Javascript files. Dynamics CRM uses Javascript files to perform a multitude of things related to how the forms look/operate, as well as other UI considerations.

If you need to customize Dynamics CRM to perform things when a form loads, or when a field entry value changes, then Javascript files are a good fit. The drawback here is that you need to have the skills to write Javascript code in order to properly implement this type of customization, so it is not as easy as the no-code configuration option.

Please note that Business Rules were introduced in Dynamics CRM 2013 as Microsoft’s way to “lower the bar” for creating Javascript customizations. Business Rules are a no-code way to perform the same (or similar) functionality that Javascript customizations currently provide. CRM provides a designer “surface” to create these rules, so that CRM administrators can create these rules without having to know Javascript.

Some of the things you can do with Javascript files or Business Rules are listed here:

  • Set the value of a field on a form, based on some criteria
  • Make a field on a form required when a value in another field is changed
  • Show an error message when an invalid entry is entered for a field
  • Show or hide a field depending on criteria
  • Enable or disable a field depending on criteria

Code Customizations

If no-code configurations or Javascript customizations do not fit the custom functionality you need to implement, the final option you have is code customizations. This requires the highest level of skills for customizing Dynamics CRM, as the person implementing the customizations (usually a developer) needs to know how to write code.

Code customizations usually are created as plugins, custom workflow activities, or custom web applications that can be embedded in CRM forms. Many companies offer 3rd party application solutions that you can add to your Dynamics CRM environment to extend the out-of-the-box functionality. These 3rd party solutions are custom-coded by their own developers, but if you purchase their application you don’t have to worry about writing code for the functionality (as they have already done that) – you just install the application and you’re good to go!

Custom plugins are designed to execute when an event or action occurs in Dynamics CRM, such as an update or delete of a certain type of record (for example, when an Account record is deleted). The plugin uses a code software development kit (SDK) provided by Microsoft to access the CRM environment to perform whatever custom logic needed.

Custom workflow activities are similar to plugins, but they are not executed when an event or action occurs in the CRM system. They are designed to be added to workflows in CRM, so that when a CRM administrator is creating a no-code workflow they can select a custom workflow activity to perform specific functionality. This extends CRM workflows to perform almost anything you can imagine in the system!

Custom web applications are server-hosted web applications written with custom code (ASP.NET for example) that are hosted on a web server that your Dynamics CRM environment can access. These custom web application pages can be embedded into a standard CRM form, to customize that form in CRM. There are many possibilities here on what can be created, so I will address that in a later post.


In conclusion, I hope you can see that the customization options for Dynamics CRM are numerous and these choices help make the Dynamics CRM an extremely customizable platform to fit any business need.

Please stay tuned for further details on customizing Dynamics CRM, as I will be updating this article with links to future blog articles. Happy customizing!


Creating an Audit Report for User Logins in Dynamics CRM

If you’ve ever tried to create an audit report for Dynamics CRM, you may have noticed that you can’t create one with the Report Wizard. The Audit Summary Report in the Settings area gives you some information, provided you enable auditing for the correct entities. However, if you need detailed information on your report, or you wish to share this report to others in CRM, you quickly realize that your options are limited.

Fortunately, there is another option that you can take to overcome these challenges. This option involves writing a custom SQL Server Reporting Services (SSRS) report that can be uploaded into CRM and shared with others to provide auditing information. I will describe how I created a report to provide detailed login information for CRM users. This article assumes the reader understands how to create a custom SSRS report for Dynamics CRM.

The first challenge I encountered was deciding how to write the query for my custom SSRS report. I have 2 choices: a SQL query or a FetchXML query. Writing a SQL query will work if the report is grabbing data from an on-premises version of CRM, but what would I do if I wanted to write this report for CRM Online? Since I can’t write a SQL query for CRM Online, I am forced to use FetchXML for the report.

My first thought was “Can I access the audit tables via FetchXML like I can with SQL?”. After a bit of searching, I found a GitHub project that had a couple of SSRS reports that accessed the audit tables via FetchXML (credit to BrettRojas: Once I examined the query, I realized that with a bit of SSRS report manipulation I could use this to provide a detailed login report in CRM Online.


FetchXML Query

The query for this report is listed here:

<fetch distinct="false" no-lock="false" mapping="logical">

<entity name="audit">

   <attribute name="createdon" />

   <attribute name="action" />

   <attribute name="userid" />

   <attribute name="objectid" />

   <attribute name="objecttypecode" />

   <order attribute="createdon" descending="true" />

<filter type='and'>

   <condition attribute='action' operator='eq' value='64'/>


<link-entity name='systemuser' from='systemuserid' to='objectid' link-type='outer' alias='SystemUser'>

   <attribute name="fullname" />

   <attribute name="windowsliveid" />

   <attribute name="businessunitid" />

   <attribute name="isdisabled" />





There are a few things to note about this query:

  • Enabling Auditing – Auditing in CRM must be enabled for this report to function properly. Since we are creating a login report, we need to enable auditing for the SystemUser entity in CRM.CRMAuditReport2
  • Query Entity – The entity used in the query is titled Audit, which is an internal entity in Dynamics CRM. This means it is not displayed in the Advanced Find dialog, nor the query generator for the Report Wizard. You still have access to this entity, but you need to have System Administrator privileges to do so.
  • Action Attribute – This attribute in the Audit entity describes the type of record being recorded. We are only interested in entries with the integer value of 64, which corresponds to a successful access via a web page. Since only CRM users would access the system via a web page, we can safely assume that these are real CRM users logging in to the system. As a side note, the original FetchXML query included entries with the value of 65, but this corresponds to a successful access to CRM via a webservices call (such as a plugin) and we do not need these records for our report.
  • Linked Entity – The query also performs an outer link to the SystemUser entity, in order to get the details we need for our report. Since the Audit entity only has the GUID for the main entity record being tracked (SystemUser in our case), we need to perform this link so we can see information such as the name of the user, their business unit, Windows Live ID, and their status (active/disabled).
  • Ordering of Results – The query performs an ordering of the results, and uses the CreatedOn field from the Audit entity. Since we are tracking SystemUser records, this field corresponds to a successful login by a CRM user, so ordering the query results in descending order assures us that the first record return for each CRM user (if we group by CRM users) will be the latest login.


Creating and Publishing the Report

You can now use the above query to create your SSRS report (and publish to CRM) using the normal techniques and processes to do so. Here is an example of the layout of the report that I created – you can display information about specific users, such as Last Login or Number of Logins over the last 30 days. Please note that the data displayed on this report will start when you enable auditing (as mentioned above).


I hope this is helpful – Happy reporting!



Creating Custom Edit Forms with InfoPath in SharePoint 2010

Users have the ability to display and edit properties (known as metadata) for items in SharePoint document libraries and lists. This is standard functionality that comes with SharePoint, and SharePoint provides a standard form when displaying/edit an item.

SharePoint also provides the ability for power users or developers to customize these display/edit forms, using InfoPath. I am going to run through the steps to customize a SharePoint 2010 list, to create a custom display and edit form. I will be using InfoPath 2010 (along with Visual Studio 2010) to demonstrate how you can call a web service method from the custom InfoPath form.


Customizing a SharePoint 2010 List

When you create a list in SharePoint 2010, a standard display/edit form is used, so the first step in customizing a list to use your InfoPath form is to navigate to the SharePoint list, and click on the List tab in the ribbon. This will display ribbon items for the list, and you can click on the Customize Form button, in the Customize List section (Figure 1).


Figure 1: Customize Form ribbon button for a SharePoint list

Once you do this, SharePoint creates a custom template with InfoPath 2010, based on the current columns defined for your list. You can use SharePoint Designer 2010 to verify whether a SharePoint list is using a customized list, as seen in Figure 2 (Figure 3 shows a list that does NOT have a customized InfoPath form):


Figure 2: SharePoint list with a Customized InfoPath 2010 Form


Figure 3: SharePoint list without a Customized InfoPath 2010 Form

When you click the Customize Form button in the ribbon, InfoPath 2010 Designer will open up with the customized form template created for you. At this point, you should make your modifications to the form (we will discuss this in more detail in the next section) and publish the form back to the SharePoint list. One thing to remember is that if you do not publish the form back to the list at this point (you will be prompted if you exit the form), SharePoint will not save the form template as a customized form, meaning your list will continue to use the default form.

For my example list (named TestCustomEditForm), I just published the custom InfoPath 2010 form template back to the list without making any modifications. Once this was completed, clicking on an item in the library displayed my custom form, as seen in Figures 4 and 5. The next section will discuss how to design more advanced changes to the customized form template.


Figure 4: Customized View Item Screen for a SharePoint list


Figure 5: Customized Edit Item Screen for a SharePoint list


Designing a Custom Display/Edit Form with InfoPath 2010

Once you have created a custom form template using InfoPath 2010, there are a lot of options available to you to design the form to your specifications. I am going to show you three things you can do to customize your form template: Adding a custom field that is a lookup to another SharePoint list, creating different views for the Display, Edit and New forms, and calling a web service method to retrieve data in a column when the form loads.


Adding Custom Fields

Adding a custom field is done in InfoPath 2010 Designer, and is very easy to do. Design your form template and in the lower right panel, there is an Actions section. You can click on the Add Field link, and a dialog to add your field information will be displayed (Figure 6).


Figure 6: Adding a Custom Field


Enter your information about the field you are adding, and press OK to add the field. In my example, I am adding a Genre field, which is a lookup to a SharePoint list called Genre. Selecting a value of Lookup (Information in a SharePoint list) in the Data Type entry field will configure the field to be a lookup to a SharePoint list. I also selected Genre from the Get Information From field, which appears after you select the Lookup data type.

After you have entered all the information for your field and clicked OK, the field will appear at the bottom of the Fields section. You can now drag and drop the custom field onto the form design surface, and InfoPath will create the appropriate control for you. In my example, since the Genre field is a lookup to a SharePoint list, InfoPath creates a dropdown control when I drag and drop the field to the design surface (see Figure 7).


Figure 7: Adding a Control for a Custom Field


You can now publish the modified form template to SharePoint, and you will see the Genre column added, as well as the field displayed when viewing/editing an item. Figures 8 and 9 show the list after these changes.


Figure 8: Update SharePoint list with a Custom Column


Figure 9: View Item with a Custom Column


Customizing Different Views

If you need to customize the different views (View/Edit/New Item) for a SharePoint list, you can accomplish that by creating different views in the form template in InfoPath. For my example, I created three (3) views in InfoPath: Display Item, Edit Item and New Item. The only difference between these views is the label for the Title field (of course, in a real-world scenario, your changes would be more extensive).

After these views are created, publish the form back to SharePoint, and in order to “wire up” the different views to the custom pages being used, you need to modify the web part in the different custom forms created (when you originally clicked Customize Form for the list). This is done by clicking on the Modify Form Web Parts (under the Customize List section in the ribbon) to display the different custom forms created (See Figure 10).


Figure 10: Modifying Form Web Parts


The web part properties for each Item form (not the Default forms) needs to be modified to display the proper view. For example, Figure 11 shows the custom form for New Items. Under the Edit tab in the ribbon, select the current view to display that view in the page.


Figure 11: Selecting the View for the Web Part


You also need to modify the web part properties for the web part on the page, as seen in Figure 12. Click on the Options tab in the ribbon (under Web Part Tools), and select the appropriate view you created (from InfoPath), under the Views dropdown. Apply the change and Stop Editing to finish with your changes.



Figure 12: Modifying Web Part Properties for the Web Part

Once you make these changes for each of the views, you can see the changes when you display, edit or create a new item, as shown in Figures 13 and 14. Notice that SharePoint automatically makes the controls read only (or editable), depending on which view is being displayed.


Figure 13: Customized View Item Form


Figure 14: Customized Edit Item Form


Calling a Web Service Method from a Custom Form

Another powerful customization is the ability to call a custom web service method (to retrieve data for a field, for example). Before you can allow your custom InfoPath form to call a web service method, you need to verify the Cross-Domain Access for User Form Templates option is selected (Figure 15). This setting is found in Central Administration, under the Configure InfoPath Forms Services area (look in the General Application Settings section).


Figure 15: Customized Edit Item Form


In order to call a web service method, a data connection must first be configured in the InfoPath form template. Make sure you select a connection to receive data, and select SOAP Web service in the Data Connection Wizard (Figures 16, 17).


Figure 16: Receive Data Connection in Data Connection Wizard


Figure 17: Data Source selection in Data Connection Wizard

To complete the data connection for your web service, enter the URL to the web service and select the web service method you are using. For my example, I’m using a method titled HelloWorld, which takes no input parameters and returns a simple string value.

Once you’ve created this connection, you need to convert it to use a data connection file (UDCX file). This is needed for security purposes, so that the user is not prompted for credentials. Click on the Convert to Connection File button in the Data Connections window (Figure 18) for your newly-created web service connection. A dialog will appear, prompting for a URL to the data connection file (Figure 19). Enter the path and file name to a data connection file that will be created when you click the OK button. For my example, I created a Data Connection library earlier named DataConnections, and used the URL to that library when creating the UDCX file.



Figure 18: Converting a Data Connection to use a Connection File


Figure 19: Convert Data Connection Dialog


At this point, your web service method is configured for use in the template, so the final step is to wire up the method to a field on the form. For my example, I created another string field (named HelloWorld) and added a text box on the design surface. I then set the default value of the text box to be the string returned from the HelloWorld web service method. This was done by entering the Formula Editor and clicking the Insert Field or Group button to allow selection of the return value from my web service method. Please make sure you select your web service method in the Fields dropdown (it will be marked as a Secondary Data Source), and that you select the appropriate field from the dataFields node in the schema (for my example, the string being returned is title HelloWorldResult, under the HelloWorldResponse node).


Figure 20: Selecting Output from Web Service Method

Click OK to save the changes, save and republish the form template, and when you display/edit an item in the list, your web service method will be called and displayed on the dialog (Figure 21).


Figure 21: Displaying Customized Form with a Web Service call


Moving a Customized Display/Edit Form to Another Environment


So far, we’ve discussed how to create a customized display/edit form, and I’ve shown you some powerful customizations you can make to your form. You may be wondering how to deploy this custom form from a development environment to a different environment (staging and/or production). I have found that the process is a bit different than you would expect.

Normally, when InfoPath form templates are created in a development environment, it’s a simple matter to “repoint” the publishing URL to your target environment and republish the template. Unfortunately, since this form template is going against a SharePoint list, we do not have access to repoint the publishing URL to a list in the target environment. Due to this limitation, the only way to get your custom form to a different environment is to save your customized list (which will include the form template and any content you might want to include) to a list template. You can then install this list template on your target environment, and create a list in that environment, based on the list template. The only other changes you would need to make is to repoint your web service method call (if you’re using one) to the web service in your target environment. You would do this by editing and republishing the InfoPath form template in your target environment.


Customizing the display/edit forms for SharePoint 2010 lists and document libraries is simple and provides a new level of customizing your SharePoint environment. Adding a bit of custom code can take this customization even further, if you need additional functionality not available with just SharePoint lists. I hope this article has helped you understand how to customize the display/edit forms using InfoPath 2010, and if you have any additions, I would love to hear about them.


How to Use BCS in SharePoint 2010


Business Connectivity Services (BCS) in SharePoint 2010 is a robust and powerful technology that is part of SharePoint 2010. Microsoft has enhanced BCS with new features, services and tools in order to streamline the development of solutions with deep integration of external data. This article will run through the steps (end to end) to use BCS to use data in SharePoint coming from an AS/400 mainframe system.

Imagine the scenario where a company has line-of-business data stored in a mainframe (AS/400) system, and they want to utilize this data in their new SharePoint 2010 environment. BCS provides the mechanism for reuse of this data, thereby solving the problem of migrating data from the AS/400 system, maintenance of duplicate sets of data. We are going to use this scenario as our example for demonstrating what BCS can do for businesses.

There are a number of steps that will be followed to accomplish this, and are listed here:

  1. Database Configuration
  2. Creation of a Secure Store Application
  3. Setup of an External Content Type
  4. Creation of the External List in SharePoint

For this example, SQL Server 2008 R2 was used for the database server, and SharePoint 2010 Server Enterprise was used for the SharePoint site.

Database Configuration

Accessing the AS/400 data can be done through SQL Server via a linked server. Once a linked server is setup, a normal SQL database can be created to access this linked server through views. In order to create a linked server, follow these steps:

  1. Open SQL Server Management Studio and connect to the server you are working with
  2. In the Object Explorer, navigate to the “Server ObjectsàLinked Servers” node
  3. Right mouse click on “Linked Servers” and create a new linked server. You can use whatever name you choose (I am using the GBCUSTOMERDATA name).
  4. Follow the prompts to create the linked server. Make sure you use the correct remote login and password when appropriate.

Once complete, your linked server should look similar to this:


After you have setup your linked server, you can now create a separate SQL database and create views that reference the linked server. I have created a database called CustomerData that will be used throughout this article.

Creating a Secure Store Target Application

The next step is to create and configure a Secure Store Target Application. A Secure Store Target Application is used to control authentication to the external data system, in order that the end user in SharePoint is not continually prompted for authentication credentials. This component will be used when configuring an External Content Type.

Creating a Secure Store Target Application is done in Central Administration, under the Security area. Before a new Target Application can be created, a new encryption key must be created for that application. This is done by navigating to the Application Management section, and clicking on the Manage Service Applications link under Service Applications. This takes you to a list of the service applications. Scroll down to the Secure Store Service and click on it to access the area to create new secure store target applications. To generate a new key, click the Generate New Key icon in the ribbon. A dialog will appear, asking you to enter a passphrase. This passphrase is used if you need to edit the target application, so make sure you save this in a secure spot for reference (it is not saved anywhere in the system).



At this point, you are able to create a new target application for the secure store. Clicking on the New icon in the ribbon will begin this process.

The first screen allows you to enter information such as the Target Application ID, Display Name, Contact E-mail, Target Application Type, and whether the target application has a page that redirects users when they incorrectly enter credentials. Target Application Type should be set to Group, as this allows configuration of a group of users for the target application. Other selections here are Individual, Individual Ticket, Individual Restricted, Individual, Group Ticket, and Group Restricted.



Please note that here is where you name the target application, along with the Target Application Type, which should be selected as a Group type, so that we can map a group of users to this Secure Store Target Application. If you wish, you can select Individual as the Target Application Type, which will only allow individual users to be mapped to this Secure Store Target Application.  For the Target Application Page URL, I selected None, as I do not want any screen to prompt the user for credentials. You can select a default or custom page if you wish your users to be prompted for credentials.  Clicking on the Next button takes you to the following screen, which allows configuration of authentication fields.



Please note that you have a lot of freedom in what you configure here for fields names, but the default field names of Windows User Name and Windows Password should be selected. Clicking on the Next button takes you to the next screen, which allows selection of the administrator accounts for the target application, as well as the members who are mapped to the target application.


Enter the administrative user(s) for Target Application Administrators, as these users will be the ones managing the Secure Store Target Application. For Members, you can enter any users or groups defined in your Active Directory environment. I selected All Authenticated Users, as I want any user who is authenticated in the AD domain. Click OK to complete the configuration of the Secure Store Target Application.

Now that your Secure Store Target Application is created and configured, you need to set the credentials that will be used. This will be the account that has access to the external data system.


Selecting the Target Application and clicking on the Set button in the ribbon (under Credentials) will display the following dialog, allowing entry of the username and password you wish to use with this Secure Store Target Application.


Setting the credentials for the target application (which you configured as a group type) is essential, as SharePoint uses these credentials when accessing the external system, instead of prompting the user for credentials each time the external system is accessed.

External Content Types

In order to provide access to external data in SharePoint, an entity called an External Content Type must be configured in SharePoint. An External Content Type (ECT) defines the connection information on where to access the external data, as well as the operations to perform when retrieving (or updating) the external data.

Creating an ECT is done with the Microsoft SharePoint Designer tool. There is a link on the left-hand side panel called External Content Types, that will display the information about the ECTs defined in the system.


Click on the External Content Type button in the ribbon, under the New section. This will create a new External Content Type, as seen below:


From here, you can configure the ECT, such as setting the name, the Data Source Type,  and creating the operations that the ECT performs. For this example, I set the name as CustomerData.


Notice that the new External Content Type does not have any operations defined initially. In order to do this, click on the Click here to discover external data sources and define operations link, under the External Content Type Operations area. This will display the following page, which allows you to define operations for the ECT.


Before operations can be created for the External Content Type, you must first create an external data source. Click on the Add Connection button to create a new external data source. You will be asked what type the new external data source is. For this example, I selected SQL Server, as I am connecting the linked server previously created in SQL Server.


Once you select the external data source, another dialog will be displayed, so you can enter the details of the data source. For a SQL Server data source, you are prompted to enter the database server name, database name, and how the ECT will connect to the database. We are connecting to this database with impersonated Windows identities, so select the Connect with Impersonated Windows Identity selection. Selecting this option will enable the Secure Store Application ID entry box, which allows you to specify what Secure Store Application is used (for credentials) when accessing the data source. For this example, I entered the NorthwindSecureStore secure store application.


When you are finished, pressing the OK button will cause the system to validate the data connection. You will be prompted to enter the administrator credentials for the Secure Store application (NorthwindSecureStore) as part of this validation. Enter the credentials you configured when you configured the secure store application.


Once the system is finished, you will be returned to the Operation Designer page for your ECT in SharePoint Designer. You will now notice that there is a data connection in the Data Source Explorer tab that corresponds to the connection you just created. For this example, the SQL connection is to the Northwind database. At this point, you are able to create operations for the external content type. To do this, expand the Northwind data connection and navigate to the table you wish to create operations for. I created Read List operations for the Customers database table by right mouse-clicking the Customers table, and selecting the New Read List Operation selection from the menu.


The system will now take you through the steps to create read list operations for the Customers database table. This includes naming the operations name, as well as creating any filters on the data that you need, and any return parameters. Clicking Next progresses you through the different dialog screens, and clicking Finish completes the creation process.



When configuring filter parameters, you select the Data Source element that the filter uses to filter the data.


Selecting return parameters specifies what columns from the data connection are returned when executing the operation. For this example, I want all the columns in the Customers database to be returned, so I selected all the columns available. I also want the CustomerID column to be mapped to the CustomerID column identifier in the database, so I check the Map to Identifier checkbox and select CustomerID, under the Identifier dropdown. You can also configure columns to be required or read-only, as appropriate.


Once you press Finish, you are returned to the Operation Designer, and you will notice that the operations you just created are listed under the External Content Type Operations section.


As a final step in creating the ECT, go back into Central Administration, and verify that the external content type has proper permissions configured. Under Service Applications, click on the Business Data Connectivity Service.


Right-mouse click the CustomerData external content type and select Set Permissions.


The dialog that is displayed shows what accounts have permissions for the external content type. Verify that the administrative account you configured previously has the proper permissions to the external content type. For this example, I gave the WINGTIP\Administrator account full permissions to the CustomerData external content type.


SharePoint External List Configuration

The final step in configuring the SharePoint system for external data is to go into the SharePoint site and create SharePoint lists that access the external data. These lists can then be used in SharePoint, and will access the data from the external system (AS/400).

To create a list that accesses external data, go to All Site Content and create an External List. When prompted for an External Content Type, select the appropriate ECT that was configured previously.



At this point, you will be able to configure a new external list based off of the external content type. When you press the Create button, the system will create the external list.




Business Connectivity Services is a great way to leverage external data in SharePoint, but can be challenging to configure. I hope that this article helps in understanding what steps are needed to configure BCS, and provides a single reference for end-to-end configuration of accessing external data in SharePoint.