Skip to content

Format Strings in CRM and fn_GetNumberFormatString surrogate

October 19, 2011

CurrenciesIf you need to format numbers in plugins in the same way that CRM does, you will likely benefit of the code below. This is especially useful for Currencies, since although CRM calculates the format of currency fields based on the user or system settings, there is no direct method to get the format from the API.

What you will find below is a direct re-implementation of the fn_GetNumberFormatString SQL function that CRM uses to render the number format in Filtered Views. The input parameters are quite self explanatory, in your code you can retrieve them from the user settings or the organization settings, depending on whether you are displaying user or system wide information.

public static string GetNumberFormatString(int precision, string numberGroupFormat,
    int negativeFormatCode, bool isCurrency, string currencySymbol = "",
    int currencyformatcode = 0)
{
    string precisionString = string.Empty;
    switch (precision)
    {
        case 1:
            precisionString = ".0";
            break;
        case 2:
            precisionString = ".00";
            break;
        case 3:
            precisionString = ".000";
            break;
        case 4:
            precisionString = ".0000";
            break;
        case 5:
            precisionString = ".00000";
            break;
    }

    string positiveNumberFormat = null;
    switch (numberGroupFormat)
    {
        case "3,0":
            positiveNumberFormat = "##########,##0" + precisionString;
            break;
        case "3,2":
            positiveNumberFormat = "##,##,##,##,##,##0" + precisionString;
            break;
        default:
            positiveNumberFormat = "###,###,###,##0" + precisionString;
            break;
    }

    string zeroNumberFormat = "0" + precisionString;

    string negativeNumberFormat = null;
    if (isCurrency)
    {
        switch (negativeFormatCode)
        {
            case 0:
                negativeNumberFormat = "(\"" + currencySymbol + "\"" + (char)8203 + positiveNumberFormat + ")";
                break;
            case 1:
                negativeNumberFormat = "-\"" + currencySymbol + "\"" + (char)8203 + positiveNumberFormat;
                break;
            case 2:
                negativeNumberFormat = "\"" + currencySymbol + "\"-" + positiveNumberFormat;
                break;
            case 3:
                negativeNumberFormat = "\"" + currencySymbol + "\"" + (char)8203 + positiveNumberFormat + "-";
                break;
            case 4:
                negativeNumberFormat = "(" + positiveNumberFormat + (char)8203 + "\"" + currencySymbol + "\")";
                break;
            case 5:
                negativeNumberFormat = "-" + positiveNumberFormat + (char)8203 + "\"" + currencySymbol + "\"";
                break;
            case 6:
                negativeNumberFormat = positiveNumberFormat + "-\"" + currencySymbol + "\"";
                break;
            case 7:
                negativeNumberFormat = positiveNumberFormat + (char)8203 + "\"" + currencySymbol + "\"-";
                break;
            case 8:
                negativeNumberFormat = "-" + positiveNumberFormat + (char)160 + "\"" + currencySymbol + "\"";
                break;
            case 9:
                negativeNumberFormat = "-\"" + currencySymbol + "\"" + (char)160 + positiveNumberFormat;
                break;
            case 10:
                negativeNumberFormat = positiveNumberFormat + (char)160 + "\"" + currencySymbol + "\"-";
                break;
            case 11:
                negativeNumberFormat = "\"" + currencySymbol + "\"" + (char)16 + positiveNumberFormat + "-";
                break;
            case 12:
                negativeNumberFormat = "\"" + currencySymbol + "\" -" + positiveNumberFormat;
                break;
            case 13:
                negativeNumberFormat = positiveNumberFormat + "- \"" + currencySymbol + "\"";
                break;
            case 14:
                negativeNumberFormat = "(\"" + currencySymbol + "\"" + (char)160 + positiveNumberFormat + ")";
                break;
            case 15:
                negativeNumberFormat = "(" + positiveNumberFormat + (char)160 + "\"" + currencySymbol + "\")";
                break;
            default:
                negativeNumberFormat = "(\"" + currencySymbol + "\"" + (char)8203 + positiveNumberFormat + ")";
                break;
        }

        switch (currencyformatcode)
        {
            case 0:
                positiveNumberFormat = "\"" + currencySymbol + "\"" + (char)8203 + positiveNumberFormat;
                zeroNumberFormat = "\"" + currencySymbol + "\"" + (char)8203 + zeroNumberFormat;
                break;
            case 1:
                positiveNumberFormat = positiveNumberFormat + (char)8203 + "\"" + currencySymbol + "\"";
                zeroNumberFormat = zeroNumberFormat + (char)8203 + "\"" + currencySymbol + "\"";
                break;
            case 2:
                positiveNumberFormat = "\"" + currencySymbol + "\"" + (char)160 + positiveNumberFormat;
                zeroNumberFormat = "\"" + currencySymbol + "\"" + (char)160 + zeroNumberFormat;
                break;
            case 3:
                positiveNumberFormat = positiveNumberFormat + (char)160 + "\"" + currencySymbol + "\"";
                zeroNumberFormat = zeroNumberFormat + (char)160 + "\"" + currencySymbol + "\"";
                break;
            default:
                positiveNumberFormat = "\"" + currencySymbol + "\"" + positiveNumberFormat;
                zeroNumberFormat = "\"" + currencySymbol + "\"" + zeroNumberFormat;
                break;
        }
    }
    else
    {
        switch (negativeFormatCode)
        {
            case 0:
                negativeNumberFormat = "(" + positiveNumberFormat + ")";
                break;
            case 1:
                negativeNumberFormat = "-" + positiveNumberFormat;
                break;
            case 2:
                negativeNumberFormat = "-" + (char)160 + positiveNumberFormat;
                break;
            case 3:
                negativeNumberFormat = positiveNumberFormat + "-";
                break;
            case 4:
                negativeNumberFormat = positiveNumberFormat + (char)160 + "-";
                break;
            default:
                negativeNumberFormat = "(" + positiveNumberFormat + ")";
                break;
        }
    }

    return positiveNumberFormat + ";" + negativeNumberFormat + ";" + zeroNumberFormat;
}

Happy coding!

Alberto “Form-over-function” Gemin

CRM 2011 Views and Local Data Groups – Part III: Deploying System Filters

September 28, 2011

Continuing the series of articles related to the CRM 2011 Views and Filters Toolkit, this article shows how to deploy system-wide filters for Outlook users. You can find more information about the toolkit by selecting the Views Toolkit Category of this blog. For a detailed discussion about Filters and Templates see this article.

Note

This note applies to this article as well.

Introduction

By default, CRM synchronises My Contacts to Outlook. Users can create additional filters to expand the range of contacts that they synchronise, but it is possible to do this across the board (i.e. for all users), and the quickest way to accomplish this is by creating a System Filter.

Outlook Contacts View

Let’s start by logging in as the CRM Administrator and by creating a personal view called Outlook Contacts; this view shows all active contacts that a user has interacted with (i.e. for whom there are related activities that are also related to them).

See the picture below for the filter conditions:

Outlook Contacts View

Outlook Contacts View

Make sure you save the view and remember its name because we’ll use it later.

Deploy System Outlook Filter Workflow

Next we’ll create a workflow based on User, called Deploy System Outlook Filter, as shown in the figure below:

Deploy System Outlook Filter workflow

Deploy System Outlook Filter workflow

Note that the workflow could run on any entity; it is just for convenience that I’ve chosen the User entity.

The workflow is composed of three steps:

Retrieve view

This step uses the RetrieveView custom activity to retrieve the Outlook Contacts personal view definition. I’ve specified the following parameters:

RetrieveView Parameters

RetrieveView Parameters

The activity will search for a personal view named Outlook Contacts owned by the user the workflow is running on (which will then have to be the CRM Administrator), and of type 0 (zero). Type 0 (zero) corresponds to type MainApplicationView, as referenced here. Note that I could have entered MainApplicationView instead than 0, the code interprets both names and numeric values when specifying a type.

Check if found

The RetrieveView custom activity has a return parameter called Count of Views which tells how many views were found. Before proceeding I should make sure that one view (and one only) was found:

Check return value of RetrieveView

Check return value of RetrieveView

Deploy Filter

This last step uses the DeploySystemView custom activity with the following parameters:

Deploy Filter parameters

Deploy Filter parameters

I’ve set four parameters, which are:

  • When Existing: Update – This means that if the destination view already exists, the activity will overwrite it. See the documentation in Codeplex for other possible values of this parameter.
  • System View Query Type: OutlookFilters – This is the type of the view when deployed. Since I want to deploy a system filter for outlook synchronization, this must be either OutlookFilters or 256. For a list of all view types see here.
  • System View Name: Outlook Contacts (System Filter) – This overrides the name of the view when deployed. If left blank, this would be the same name of the view in the View Definition parameter, which in this case is Outlook Contacts.
  • View Definition: View Definition(Retrieve View) – This is the actual definition of the view, which is set to the output parameter of the RetrieveView activity.

Test

Now select the record relative to the user that owns the Outlook Contacts personal view (in this example the CRM Administrator), and run the Deploy System Outlook Filter Workflow.

If the workflow completed successfully, users will see the following screen when looking at the synchronisation settings from within their CRM Outlook Client:

Outlook Filters

Outlook Filters

This means that in addition to their custom rules for synchronisation of contacts, which include by default My Contacts, all users will synchronise contacts that are returned by the query we’ve just deployed.

Conclusions

This was a walk-through of how to deploy a system filter without coding, and with the help of the CRM 2011 Views and Filters Toolkit. Note that this example can be also applied to System Offline Filters. The only change needed is to specify OfflineFilters query type instead than OutlookFilters in the DeploySystemView activity.

Alberto “Contact the Filter” Gemin

CRM 2011 – Offline and Outlook Filters and Templates: Local Data Groups

September 21, 2011

Local Data Groups

Introduction

The way in which CRM controls what users download to their online and offline Outlook clients has been improved with the 2011 version, as you can read here. However, in my opinion some of this functionality is not immediately clear. This article is meant to provide a complement to the MSDN article cited above by explaining these features in more detail.

In the next article we’ll see how the CRM 2011 Views and Filters Toolkit can be used to perform tasks related to Filters and Templates, as well as to access some of the functionality that until now was only available through the CRM API.

Some Terminology

In the context of this article there are four terms that, in various combinations, mean different things. Let’s note down few facts about each to act as reference while looking at the functionality. The terms are Outlook, Offline, Filter and Template.

Outlook

This term refers to synchronization capabilities of the CRM Outlook Client when it’s online. The standard Outlook Client is always online. The Offline Outlook Client is online when the user has pressed the “Go Online” button.

Offline

This term refers to synchronization capabilities of the CRM Outlook Client when it’s offline. The standard Outlook Client can never be offline. The Offline Outlook Client is offline when the user has pressed the “Go Offline” button.

Filter

A filter is a query that CRM uses to determine what data should be synchronized between the CRM server and the Outlook Client. Filters can be of two types: System Filters and User Filters.

Template

This is a template query that can be instantiated as a User Filter for a particular user.

Synchronising Data: Filters

Outlook vs. Offline

Outlook synchronizes only few entities when online, such as contacts, appointments, tasks etc. If the user has installed the Offline CRM Client, Outlook synchronizes much more data than just contacts and activities when going offline. In theory one could choose to have the whole database available offline, which would give an offline user the ability to access the same set of data as if they were working online.

To decide what to synchronise in both scenarios, i.e. to determine Local Data Groups, CRM relies on filters, which are called Outlook Filters or Offline Filters depending on whether they apply to when Outlook is online or offline respectively. In addition, each filter can be a System or User filter.

System vs. User Filters

System Filters are stored in the SavedQueryBase table and they are valid for all users. User Filters are stored in the UserQueryBase table and they apply only to their owner. Think of this difference as the difference between a System View and a Personal View.

Users are empowered to maintain their User Filters, as described here, but only administrators can maintain System Filters. In addition, administrators can control the deployment of User Filters via the template mechanism discussed later in this article.

Filter Types

To recap, the following table shows all available filter types and their main attributes.

On / Off S / U QueryType Table Description
Online System OutlookFilters (256) SavedQueryBase Determines which data to synchronise when online. Applies to all users.
Offline System OfflineFilters (16) SavedQueryBase Determines which data to synchronise when offline. Applies to all users.
Online User OutlookFilters (256) UserQueryBase Determines which data to synchronise when online. Applies to one user only.
Offline User OfflineFilters (16) UserQueryBase Determines which data to synchronise when offline. Applies to one user only.

 

A consideration related to the table above is that the QueryType attribute is technically not the same for all filters, since filters can be either savedquery or userquery CRM entities, depending on whether they are System or User Filters respectively.

Therefore, the QueryType attribute can be of type SavedQueryQueryType or UserQueryQueryType. The two are not equivalent: you can find the definition of the two types here and here.

Filter Templates

As explained above, users are empowered to maintain their User Filters, but administrators can control the deployment of User Filters via Filter Templates.

Filter Templates are queries that can be instantiated as User Filters. Templates reside in the SavedQueryBase table (the instantiated User Filters are of course in the UserQueryBase table). Each User Filter record contains a reference back to the filter in the UserQueryId.ParentQueryId attribute.

Like Filters, Templates can be for offline or online synchronisation, as shown in the table below.

On / Off QueryType Table Description
Online OutlookTemplate (131072) SavedQueryBase Instantiates a corresponding User Filter of type OutlookFilters.
Offline OfflineTemplate (8192) SavedQueryBase Instantiates a corresponding User Filter of type OfflineFilters.

 

A Filter Template can be manually instantiated as a User Filter through a call to the InstantiateFiltersRequest request.

Default Filter Templates

A template can be marked as default by setting its SavedQuery.IsDefault attribute.

Each entity can have only one filter template that is marked as default. If you create a custom entity, and set the IsAvailableOffline property, a default filter template is created automatically.

When new users are added to the system, all templates marked with the IsDefault attribute will cause User Filters to be instantiated to those users automatically.

In addition, all User Filters can be manually reset (which includes re-instantiating all the applicable templates) through the ResetUserFiltersRequest request.

Example

To see how the considerations above translate into reality, get your hands on a CRM 2011 instance and run the following query:

SELECT
      SavedQueryId, Name, Description, QueryType, IsDefault
FROM
      SavedQuery
WHERE
      QueryType IN (131072, 8192) AND ReturnedTypeCode = 2

If the default configuration has not been modified extensively, the query should return the following results:

SavedQueryId Name Description QueryType IsDefault
65D2CBA8-EEDA-4419-B03B-D9C2D8272E51 My Contacts Contacts owned by me 8192 1
58DC4BEE-60B7-4A84-987F-800296B71404 My Outlook Contacts Contacts Syncing to Outlook 131072 1

 

You can see that there are two Filter Templates for contacts in the SavedQueryBase table: one for Online and one for Offline filters.

If you look in the UserQueryBase table for a particular user:

SELECT TOP 2
      Name, Description, QueryType, ParentQueryId
FROM
      UserQuery
WHERE
      QueryType IN (256, 16) AND ReturnedTypeCode = 2
ORDER BY OwnerIdName

You should get the following results:

Name Description QueryType ParentQueryId
My Outlook Contacts Contacts Syncing to Outlook 256 58DC4BEE-60B7-4A84-987F-800296B71404
My Contacts Contacts owned by me 16 65D2CBA8-EEDA-4419-B03B-D9C2D8272E51

 

Each filter in the table above is an instance of its corresponding template, as confirmed by the fact that the ParentQueryId attribute points back to the template.

Conclusions

Hopefully this article sheds some light into Filters, Templates and how they are related to Local Data Groups.

The next article will show how the CRM 2011 Views and Filters Toolkit can be used to perform complex tasks related to Filters and Templates, as well as to access some of the functionality that until now was only available through the API.

Alberto “Templet” Gemin

CRM 2011 Views and Local Data Groups Example – Part II

September 16, 2011

Continuing the series of articles related to the CRM 2011 Views and Filters Toolkit, this article shows how to deploy System Views using the toolkit. You can find more information about the toolkit by selecting the Views Toolkit Category of this blog.

Note

See this note in the previous article.

Duplicate a Personal View into a System View

Sometimes I thought it would be great if the administrator could elect to promote a personal view into a system view, without having to configure the system view manually. This can be done through workflow by leveraging the CRM 2011 Views and Filters Toolkit.

My Extended Contacts” View

Let’s imagine that the user John Doe has created a view called “My Extended Contacts”; this view shows all active contacts that John has interacted with (i.e. for whom there are related activities that are also related to John).

See the picture below for the filter conditions:

“My Extended Contacts” View

“My Extended Contacts” View

The “My Extended Contacts” view is such a hit that we want to transform it into a system view available to everyone. Let’s create a workflow based on User, called “Create System View from Personal View”, as shown in the figure below:

“Create System View from Personal View” workflow

“Create System View from Personal View” workflow

The workflow is composed of three steps:

1) Retrieve personal view

This step uses the RetrieveView custom activity with the following parameters:

RetrieveView Parameters

RetrieveView Parameters

The only difference with the previous example is that for this one the User is the user the workflow runs on.

2) Check that a view was found

The RetrieveView custom activity has a return parameter called “Count of Views” which tells how many views were found. Before proceeding I should make sure that one view (and one only) was found:

Check return value of RetrieveView
Check return value of RetrieveView

3) Deploy System View

This last step uses the DeploySystemView custom activity with the following parameters:

Deploy System View parameters

Deploy System View parameters

  • When Existing: Error – This means that if a system view for contacts named “My Extended Contacts” already exists, the activity will throw an exception.
  • View Definition: View Definition(Retrieve personal view) – This is the actual definition of the view, which is set to the output parameter of the RetrieveView activity.

Test

Now select the John Doe user record and run the “Create System View from Personal View” workflow on it. You will have a new system view that is an exact replica of John Doe’s “My Extended Contacts” view.

Note that when deploying system views it is a good practice to work on the development or staging environments only, and deploy to production the views with their related entity (in this case Contact) through solutions.

Conclusions

This example is showing just the beginning of the potential of this toolkit, which can be used to do more complicated and powerful things around Personal Views, System Views, Filters, Filter Templates and Local Data Groups. More articles to come.

Alberto “Views-R-Us” Gemin

CRM 2011 Views and Local Data Groups Example – Part I

September 15, 2011

Now that the first incarnation of the CRM 2011 Views and Filters Toolkit is complete and deployed in Codeplex, let me explain how these tools can be combined to carry out common tasks related to System Views, Personal Views and Local Data Groups. You can find more information about the toolkit by selecting the Views Toolkit Category of this blog.

Note

In the remainder of this article, and in all other articles about usage of the toolkit, the custom workflow activities part of the toolkit have been named as shown in the table below. You can name the activities while registering them, or you can use the shortcut described in the “CRM 2011 – Default Name and GroupName for your Custom Activities” article of this blog to name them automatically.

Activity Group Name Name
DeploySystemView AG Utilities Deploy System View
DeployUserView AG Utilities Deploy User View
ResetUserFilters AG Utilities Reset User Filters
RetrieveView AG Utilities Retrieve View

All activities listed above have extended documentation about their input and output parameters in the Documentation section in Codeplex.

To run the examples described in this series of articles you need to download the toolkit from here and install it using the plugin registration tool.

Duplicate a Personal View into another

There are a couple of ways to share views that users have created for themselves: they can be assigned to someone else, or they can be shared.

Sometimes though it would be nice to be able to copy a personal view from one user to another, so that the destination user can own their own copy independently from the original user. This is especially true for complicated views that a user has refined over time. This can be done through workflow by leveraging the CRM 2011 Views and Filters Toolkit.

My Extended Contacts” View

Let’s imagine that the user John Doe has created a view called “My Extended Contacts”; this view shows all active contacts that John has interacted with (i.e. for whom there are related activities that are also related to John).

See the picture below for the filter conditions:

“My Extended Contacts” View

“My Extended Contacts” View

Bill Green wants a similar personal view but he needs to do some modifications to it, so he would like a duplicate of the view to start with.

Let’s create a workflow based on User, called “Duplicate Personal View”, as shown in the figure below:

“Duplicate Personal View” workflow

“Duplicate Personal View” workflow

The workflow is composed of three steps:

1) Retrieve the view

This step uses the RetrieveView custom activity with the following parameters:

RetrieveView Parameters

RetrieveView Parameters

The activity will search for a personal view named “My Extended Contact View” owned by John Doe.

We could add other conditions (such as Returned Entity = Contact or Query Type = MainApplicationView) but I trust that the two conditions I entered will uniquely identify the view I’m looking for.

2) Check that a view was found

The RetrieveView custom activity has a return parameter called “Count of Views” which tells how many views were found. Before proceeding I should make sure that one view (and one only) was found:

Check return value of RetrieveView

Check return value of RetrieveView

3) Deploy to user

This last step uses the DeployUserView custom activity with the following parameters:

Deploy User View parameters

Deploy User View parameters

I’ve set only three parameters, which are:

  • When Existing: Update – This means that if the destination user already has a personal view named “My Extended Contacts”, the activity will overwrite it. See the documentation in Codeplex for other possible values of this parameter.
  • Personal View Owner: User(User) – The target user is the user which the workflow activity is running against. I could also put here the reference to a particular user.
  • View Definition: View Definition(Retrieve the view) – This is the actual definition of the view, which is set to the output parameter of the RetrieveView activity.

The activity will deploy to “User” the view defined in “View Definition”.

I could override some elements of the view, such as Name or Fetch XML, in which case I could also do without the View Definition Parameter, but using View Definition will make sure that all elements of the original view (including its name) are preserved.

Test

Now select the Bill Green user record and run the Duplicate Personal View workflow on it. You will have a new view, owned by Bill, which is an exact replica of John Doe’s view. Note that you could also run the workflow against multiple users, hence duplicating the original view many times over, once for each user.

Conclusions

This example is showing just the beginning of the potential of this toolkit, which can be used to do more complicated and powerful things around Personal Views, System Views, Filters, Filter Templates and Local Data Groups. More articles to come.

Alberto “Views-R-Us” Gemin

New Release of the CRM 2011 Views and Filters Toolkit: Deploy System View and Instantiate Filter

September 14, 2011
CodePlex homepage

Image via Wikipedia

Two new tools have been released in the CRM 2011 Views and Filters Toolkit Codeplex project: DeploySystemView and InstantiateFilter.

These tools allow to deploy a System View and instantiate Outlook and Offline filters for a specific user. The documentation in Codeplex details all the input parameters and some of the behaviour. I’ll publish new articles with examples soon.

You can find more information on how to use the toolkit by clicking on the Views Toolkit category of the blog.

Alberto “Back from vacation” Gemin

ObjectDisposedException on Custom Workflow Activities

August 29, 2011

I was noticing some strange behaviour of output parameters of custom workflow activities when multiple instances of a workflow containing a particular custom activity were running at once.

The event was random, but sometimes one of the workflow would fail to set the output parameter. No exception, no error, the workflow would simply not do what the code said. While dismantling my code piece by piece and testing for differente race conditions I finally got an exception that shone some light:

System.ObjectDisposedException: An ActivityContext can only be accessed within the scope of the function it was passed into

A little cryptic, but I realised that the problem was caused by the way I was storing the CodeActivityContext for execution throughout my classes:

        [Output("Count")]
        public OutArgument Count { get; set; }
        //
        public CodeActivityContext ExecutionContext { get; set; }
        //
        protected override void Execute(CodeActivityContext executionContext)
        {
            this.ExecutionContext = executionContext;
            SomeOtherMethod();
            (...)
        }

        //
        private void SomeOtherMethod()
        {
            this.Count.Set(this.ExecutionContext, 1);
            (...)

As you can see above, I thought I could use CodeActivityContext like any other class, and I was storing it as a class property so I did not have to pass it from method to method. I still don’t know why, but I am reasonably sure that this does not work, and that the correct method is:

        [Output("Count")]
        public OutArgument Count { get; set; }
        //
        protected override void Execute(CodeActivityContext executionContext)
        {
            SomeOtherMethod(executionContext);
            (...)
        }

        //
        private void SomeOtherMethod(CodeActivityContext executionContext)
        {
            this.Count.Set(executionContext, 1);
            (...)

Which is consistent with the exception message: “An ActivityContext can only be accessed within the scope of the function it was passed into“. Indeed.

I suspect that this has to do with serialisation: everything works until the class is serialised and deserialised, then CodeActivityContext cannot be revived by deserialisation, and it becomes somewhat corrupted. Pity that the behaviour is not consistent, the exception is thrown sporadically and most of the times there are no errors.

If you can offer a logical explanation I’d love to hear about it. I’m not an expert in Microsoft WF and this might just be a beginner’s mistake.

Alberto “Took me a while to figure it out” Gemin

%d bloggers like this: