How to get OptionSet values using SQL?

Obtaining a list of option values for a MS CRM 2011 OptionSet is pretty simple, especially if you plan to do this via the CRM Web service API. However a requirement may arise where you may want to do this using direct SQL against the Organization database.

Example Background

By default on the account entity there are several address fields, and one in particular is the Address Type picklist which is used to categorize the type of address stored for this account. Using this field as an example, the following SQL Query can be used to obtain a list of all options available to that field:

SQL Statement

2. SELECT
3. EV.LogicalName as Entity,
4. ALV.Name as Attribute,
5. LLV.Label,
6. APV.Value
7. FROM LocalizedLabelView as LLV
8.     INNER JOIN AttributePicklistValueView as APV ON LLV.ObjectId = APV.AttributePicklistValueId
9.     INNER JOIN OptionSetView as O ON APV.OptionSetId = O.OptionSetId
10.     INNER JOIN AttributeLogicalView as ALV ON O.OptionSetId = ALV.OptionSetID
11.     INNER JOIN EntityView as EV ON ALV.EntityId = EV.EntityId
12. WHERE
13.     ALV.Name like '%address1_addresstypecode%' AND EV.LogicalName = 'account'
14. ORDER BY EV.LogicalName, ALV.Name, APV.Value

SQL Results

Enjoy :)

Portal Accelerator & Adxstudio - How to force clear Cache?

Caching is an important part of all enterprise applications, they help deliver high-performance and scalability. 

For those of you that do not understand caching, in short the caching manager will cache objects in memory the first time they are requested, and reuse these to feed further requests. 

Both Microsoft Portal Accelerator and AdxStudio Portal use caching. Cache invalidation seems to be an issue that many are currently experiencing. There are some built in features such as the Web Notification URL in CRM, but recently I was asked if there was a method of forcing the cache to clear programmatically.

How to clear the Cache Example

21.         /// <summary>
22.         /// Removes all items, clears everything from the Cache
23.         /// </summary>
24.         public void ClearCache()
25.         {
26.             var cache = Microsoft.Xrm.Client.Caching.ObjectCacheManager.GetInstance(null);
27.             Microsoft.Xrm.Client.Caching.ObjectCacheManager.Clear(cache);
28.         }
29.  
30.         /// <summary>
31.         /// Removes an item from the cache
32.         /// </summary>
33.         /// <param name="item"></param>
34.         public void ClearCache(string item)
35.         {
36.             var cache = Microsoft.Xrm.Client.Caching.ObjectCacheManager.GetInstance(null);
37.             cache.Remove(item);
38.         }

CRM 2011 - How to use Wait/Timeout in Workflows?

One of the great benefits of Microsoft Dynamics CRM, is that is has built-in support for creating Workflows. Creating a workflow involves defining a sequence of steps that describes what the workflow process should do, and when it should do it.

Often you will need to pause the execution of a workflow until a specific condition is met. To achieve this you will want to use a Wait Condition, which can be broken down into the following:

1. Wait - use this to wait until a condition is met, examples of this could be when a Record Status changes, a field is updated or a value on a related Record is changed
2. Timeout - use this to wait until a specific date, a period of time or a date relative to a field in a record e.g. 5 days after the Record Created Date

Example Background

We want to use Workflows to create a phone call activity so that someone from our sales department can follow up on newly created leads. When a user completes the activity we want to track if the lead has been contacted. If the lead has not been contacted within 7 days, we want to re-assign the lead to the 2nd line Sales team. 

For simplicity we will create two workflows to achieve this.

Wait in a Workflow

When a lead is created, it will trigger a workflow to create an associated Phone Call activity and wait until the activity is complete, once complete the workflow will update the "Status Reason" field on the Lead as "Contacted".

Timeout in a Workflow

When a lead is created, the Workflow will pause for exactly 7 days from when the Lead was created, when the timeout is reached the workflow will check if the Lead's "Status Reason" field is set to "New", if yes then the Lead will be re-assigned to the "Sales 2nd Line" Team.

Hopefully Wait and Timeouts now make a little more practical sense.

CRM 2011 - How to create a Custom Workflow Activity?

When working with Microsoft Dynamics CRM, one of the most common tasks undertaken is to map real life business processes and to automate these within CRM. There are two main methods of creating processes that are triggered based on input or an event.

1. Plugins are the most popular choice for C# developers who are familiar with Visual Studio and the CRM Sdk. 
2. Workflows are another useful feature for creating asynchronous processes, which give none-programmers the ability to create simple but powerful components that do not require a software developer. 

The out-of-box Workflow functionality is somewhat limited, you are constrained by the features exposed by the Workflow creation wizard. 

Custom Workflow Activities

Can be used to enhance and provide solutions to functionality you currently cannot achieve solely by a CRM Workflow. Custom WF Activities are a perfect approach to creating reusable pieces of functionality that can be used by both Workflows and Dialog's.

They do however require programming knowledge such as C# .NET.

How to create a Custom Workflow Activity

Background

To help demonstrate the use of Custom Workflow Activities, the scenario is that when a new Contact record is created, you want the Parent Customer Account's marketing opt in/out values to be applied to the contact.

Solution

1. Open Visual Studio 2010 
2. Create a new Project using the Workflow > Activity Library template

3. Delete the .xaml file, as you will not need this

4. Add a new Class to the project, this will be your Custom Workflow

5. Add the following code to your Class

12. public class MyCustomWFActivity : CodeActivity
13. {
14.     public MyCustomWFActivity()
15.     {
16.         
17.     }
18.  
19.     [Input("Parent Account")]
20.     [ReferenceTarget("account")]
21.     public InArgument<EntityReference> ParentAccount { get; set; }
22.  
23.     [Output("Email")]
24.     [DefaultAttribute("true")]
25.     public OutArgument<bool> Email { get; set; }
26.  
27.     [Output("Phone")]
28.     [DefaultAttribute("true")]
29.     public OutArgument<bool> Phone { get; set; }
30.  
31.     [Output("Fax")]
32.     [DefaultAttribute("true")]
33.     public OutArgument<bool> Fax { get; set; }
34.  
35.     [Output("Mail")]
36.     [DefaultAttribute("true")]
37.     public OutArgument<bool> Mail { get; set; }
38.  
39.     [Output("Bulk Email")]
40.     [DefaultAttribute("true")]
41.     public OutArgument<bool> BulkEmail { get; set; }
42.  
43.     protected override void Execute(CodeActivityContext context)
44.     {
45.         try
46.         {
47.             //-----------------------------------
48.             // Workflow Context & Service Objects
49.             //-----------------------------------
50.             IWorkflowContext wfContext =
51.                 context.GetExtension<IWorkflowContext>();
52.             IOrganizationServiceFactory serviceFactory =
53.                 context.GetExtension<IOrganizationServiceFactory>();
54.             IOrganizationService service =
55.                 serviceFactory.CreateOrganizationService(wfContext.InitiatingUserId);
56.  
57.             //--------------------------
58.             // Obtain the Input Argument
59.             // Account EntityReference
60.             //--------------------------
61.             var parentAccountRef = ParentAccount.Get<EntityReference>(context);
62.  
63.             //---------------------------
64.             // Retrieve the Account based
65.             // on the EntityReference Id
66.             //---------------------------
67.             Entity account =
68.                 service.Retrieve(parentAccountRef.LogicalName,
69.                 parentAccountRef.Id,
70.                 new Microsoft.Xrm.Sdk.Query.ColumnSet(true));
71.  
72.             if (account != null)
73.             {
74.                 //---------------------------
75.                 // Set Output Argument Values
76.                 //---------------------------
77.                 this.Email.Set(context, ((bool)account.Attributes["donotemail"]));
78.                 this.BulkEmail.Set(context, ((bool)account.Attributes["donotbulkemail"]));
79.                 this.Phone.Set(context, ((bool)account.Attributes["donotphone"]));
80.                 this.Mail.Set(context, ((bool)account.Attributes["donotpostalmail"]));
81.                 this.Fax.Set(context, ((bool)account.Attributes["donotfax"]));
82.             }
83.         }
84.         catch (SoapException se)
85.         {
86.             //Log Errors
87.         }
88.         catch (Exception ex)
89.         {
90.             //Log Errors
91.         }
92.     }
93. }

• Workflow Activity Class will inherit CodeActivity
• InArguments - define the input parameters that are passed in
• OutArguments - define output parameters, these are passed back to the calling process
• Both In and Out Arguments can have default values, a good way to ensure that parameters are not left empty

6. Right click on the Project, select Properties and create a strong name key file. Similar to plugins, custom workflow activities need to be signed.

7. We need to register the assembly, this will be done using the plugin registration tool as follows:

8. Now that we have the assembly registered, we can create a Workflow to consume the functionality we have defined in our custom activity.

9. To create a workflow, follow these steps:

10. We want this workflow to execute on create for the Contact entity, select Contact from the entity drop-down and enter in a process name.

11. First thing we want this workflow to do, is execute our Custom Activity. We have registered the assembly earlier using the Plugin Registration Tool, it should automatically appear in the Add Step drop-down.

12. Click on the Set Properties button

13. Here we need to provide an input value for the InArgument we defined in our Custom Activity. This value needs to be dynamically set at run-time, so we select the Parent Customer field from the Form Assistant.

14. Next we want to add an Update Record step, to update the Contact record

15. Scroll down to the Preferences section and select each of the marketing fields and using the Form Assistant, select the corresponding value for each field.

16. Add a Stop Workflow step, set it to Succeeded.

17. Make sure you have selected the "Record is created" checkbox, this will ensure that the workflow will only execute when a new Contact is created. Then Save and Close.

18. The workflows needs to be activated, select the newly created workflow and click on the Activate button. On the popup window, click on the OK button.

19. Open a Account record, change some of the marketing fields and save the record.

20. Create a new Contact record, and select the previously edited Account record. Now Save and Close.

21. Re-open the newly created Contact and you will notice that the marketing fields on the Contact record now match those from the Parent Account.

That's it, hope you enjoyed the article :)

CRM 2011 - Error Importing Solution

When trying to Import an unmanaged or managed solution, you may experience the following error:

Error while Importing a solution in CRM 2011 – The solution file is invalid. The compressed file must contain the following files at its root: solution.xml, customizations.xml, and [Content_Types].xml

Issue

The issue can be either one of a few, could be that the file  is corrupt, the file has been tampered with, but the most probable cause is that the file has been zipped/compressed incorrectly. 

You should check that there are 3 .xml files in the root of the solutions .zip file, which should include:

• solutions.xml
• customizations.xml
• [Content_Types].xml

Resolution

The obvious thing would be to try exporting the solution again and try importing this, however if this is not an option, please do the follow:

1. Extract the solution .zip and navigate to the folder until you can see the 3 .xml files, along with the other contents of the solution.

2. Select all files and folders, then right click and use the Send to > Compressed Folder option as displayed below:

Now try importing the newly created .zip solutions file, and you should be fine.

Rollup Update 10 - Cannot insert duplicate key row in object

After installing Rollup Update 10, you may get the following errors when trying to import a solution in CRM 2011.

Cannot insert duplicate key row in object 'dbo.DependencyNodeBase' with unique index 'ndx_DependencyObjectId'. The statement has been terminated

Dependencies Calculation

This is an issue introduced by RU 10, and can be easily fixed by installing RU 11.

Plugin - How to send an Email

Plugin's are very useful business process components within CRM, they can be attached and configured to execute based on certain events and execute pre-defined business logic.

How to send an Email from a plugin is today's post, code example:

private void SendEmail(IOrganizationService service,
    Guid recieverUserId,
    Guid senderUserId,
    Guid regardingObjectId,
    string emailBody,
    string emailSubject)
{
    Entity email = new Entity();
    email.LogicalName = "email";

    //Regarding field, the record, which you want this email associated to
    EntityReference regardingObject = new EntityReference("opportunity", regardingObjectId);
    email.Attributes.Add("regardingobjectid",regardingObject);

    //Define the Sender and Receiver
    EntityReference from = new EntityReference("systemuser", senderUserId);
    EntityReference to = new EntityReference("systemuser", recieverUserId);

    //Activity Party - From
    Entity fromParty = new Entity("activityparty");
    fromParty.Attributes.Add("partyid",from);

    //Activity Party - To
    Entity toParty = new Entity("activityparty");
    toParty.Attributes.Add("partyid", to);

    EntityCollection collFromParty = new EntityCollection();
    collFromParty.EntityName = "systemuser";
    collFromParty.Entities.Add(fromParty);

    EntityCollection collToParty = new EntityCollection();
    collToParty.EntityName = "systemuser";
    collToParty.Entities.Add(toParty);

    email.Attributes.Add("from",collFromParty);
    email.Attributes.Add("to", collToParty);

    //Set Email Subject & Body
    email.Attributes.Add("subject",emailSubject);
    email.Attributes.Add("description", emailBody);

    //Create email activity
    Guid emailID = service.Create(email);

    //Sending email
    SendEmailRequest reqSendEmail = new SendEmailRequest();
    reqSendEmail.EmailId = emailID;
    reqSendEmail.TrackingToken = string.Empty;
    reqSendEmail.IssueSend = true;           

    SendEmailResponse res = (SendEmailResponse)service.Execute(reqSendEmail);
}

The method demonstrates how to send an email, passing in the appropriate parameters. This example is sending an email from and to internal System Users, however this can be easily adapted for other purposes.

Note: Your email mechanism need's to be configured correctly in order for this to work e.g. CRM Email Router.