Free Tool: Dynamics CRM User Language Update Tool

Just had an encounter with a user who needed their language updated. The UI Language was set to Korean – I wasn’t able to walk the user through updating it since I don’t read Korean either.

So I whipped up a little tool to do the job for me.
userupdatetool

You supply a connection string for the target CRM Organization (Should support everything – CRM Online, On-Premise – just paste everything in the CRM url up to (but not including) the “/main.aspx”)

You can search for the user by Guid, Full Name or DomainName (aka User Name) (all the search fields auto add wildcards)

The tool returns a list of matching users – select the user to update, select the desired values for language code and click Update. Pretty basic and there are instructions for doing it, but I didn’t see anything so simple out there.

You can get the binaries here: Dynamics CRM User Language Update Tool

And the source code here: Dynamics CRM User Language Update Tool Source Code

(Note that you may need to have Windows Identity Foundation installed – this is a prerequisite of the CRM SDK)

This tool works with Dynamics CRM 2011, Dynamics CRM 2013, Dynamics CRM 2015, Dynamics CRM Online, and the forthcoming Dynamics CRM 2016 (ie everything 2011 and later)

Get the SQL Server and database name from Dynamics CRM

As far as I can tell, there is no way to retrieve the SQL Server name using the Organization Service. If you have the Organization Service URL, there really is no good way to get the database connection string.

(You can use the deployment service to get it, but that only works if you happen to be a deployment administrator)

No good way… but there is a way. When you download a dynamic spreadsheet, the database connection string is embedded in the xml. And you can programmatically download that spreadsheet using an entity that all CRM orgs have.

Using some fiddler experimentation, I was able to make a .NET method that will extract the database connection string, given the organization base url (https://crmserver/orgnam)

        /// <summary>
        /// Given a Dynamics CRM Org URL, retrieve the Database Connection string
        /// </summary>
        /// <param name="crmOrgUrlBase"></param>
        /// <returns></returns>
        private static string GetCrmDatabaseConnectionString(string crmOrgUrlBase)
        {
            string cleanurl = crmOrgUrlBase.ToLowerInvariant().Trim().Replace("/xrmservices/2011/organization.svc", "");
            {
                int mainPos = cleanurl.IndexOf("/main.aspx");
                if (mainPos > 0)
                {
                    cleanurl = cleanurl.Substring(0, mainPos);
                }
            }

            string requestPayload = @"xdpi=96&exportType=list&useSqlQuery=1&fetchXml=%3Cfetch+distinct%3D%22false%22+no-lock%3D%22false%22+mapping%3D%22logical%22+page%3D%221%22+count%3D%2250%22+returntotalrecordcount%3D%22true%22%3E%3Centity+name%3D%22systemuser%22%3E%3Cattribute+name%3D%22systemuserid%22%2F%3E%3Cattribute+name%3D%22fullname%22%2F%3E%3Cattribute+name%3D%22fullname%22%2F%3E%3Corder+attribute%3D%22fullname%22+descending%3D%22false%22%2F%3E%3C%2Fentity%3E%3C%2Ffetch%3E%0D%0A&layoutXml=%3Cgrid+name%3D%22excelGrid%22+select%3D%220%22+icon%3D%220%22+preview%3D%220%22%3E%3Crow+name%3D%22result%22+id%3D%22systemuserid%22%3E%3Ccell+name%3D%22fullname%22+width%3D%22100%22%2F%3E%3C%2Frow%3E%3C%2Fgrid%3E%0D%0A";
            string url = cleanurl + "/_grid/print/export_live.aspx";

            string response = null;


            using (var wc = new System.Net.WebClient())
            {
                wc.UseDefaultCredentials = true;
                wc.Headers.Add("Accept-Encoding", "gzip, deflate");
                wc.Headers.Add("Content-Type", "application/x-www-form-urlencoded");
                wc.Headers.Add("DNT", "1");
                response = wc.UploadString(url, requestPayload);
            }


            var xe = System.Xml.Linq.XElement.Parse(response);


            System.Xml.Linq.XNamespace nn = "urn:schemas-microsoft-com:office:spreadsheet";
            System.Xml.Linq.XNamespace nn2 = "urn:schemas-microsoft-com:office:excel";


            var connectionEl = xe.Element(nn + "Worksheet").Element(nn2 + "QueryTable").Element(nn2 + "QuerySource").Element(nn2 + "Connection");

            string rawString = connectionEl.Value;

            Regex rex = new Regex("SERVER=([^;]+);DATABASE=([^;]+)", RegexOptions.Compiled);

            var m = rex.Match(rawString);

            string server = m.Groups[1].Value;
            string database = m.Groups[2].Value;

            return "DATA SOURCE=" + server +";INITIAL CATALOG=" + database + ";Integrated Security=SSPI";
        }

It is technically unsupported to access export_live.aspx directly, so this may stop working in future releases of CRM.
I have tested this on CRM 2011 UR 11 and UR 17.

CRM 2011 Runtime calculated fields –retrieval behavior notes

In previous versions of CRM, creating realtime calculated fields involved a plugin that listened to the global Execute message (and optionally RetrieveMultiple if you wanted to handle filter criteria) – to handle FetchXml requests. The remaining messages (Retrieve, RetrieveMultiple, Update, Create) can be registered for the specific Entity, so you are not gumming up CRM with global plugins.

Some of the neat new features of CRM 2011 greatly complicate the process of creating a calculated field – your Plugin now must be registered globally for Retrieve, RetrieveMultiple and Execute; and there are three different query types to handle.

These are my research notes from building a plugin that allows for functionality similar to the “Customer” lookup in the built-in entities. It would be easy enough to sit on the Post-Stage event, look through the Result and use a retrieve for each record to get the calculated value. This would quickly degrade system performance – instead my solution modifies the query in the Pre-stage to include all necessary columns, then uses the data in the result to update the calculated field. It requires significantly more code, but is far more scalable.

1. Execute Message

I haven’t yet determined all the cases that Execute is called, but it is for sure called when the legacy webservice is used to perform an ExecuteFetch (such as when using Stunnware tools). It is also fired now and again by the UI (but not for normal grids – almost everything uses RetrieveMultiple now)

In the Pre-Stage, you must look for the “FetchXml” value in the InputParameters. If it is there, use your Xml parser / modifier of choice to look through it for any instances of your entity, then for each entity instance, check for your calculated field (these can be nested arbitrarily deep). If it contains the field, add your required source columns to Xml, and put the FetchXml back into the InputParameters.

The ResultXml on the Post-Stage is flat, and columns from Linked Entities are given a prefix. The prefix comes from the link-entity – it is the alias if one is specified, or the “to” attribute. (Caveat: If aliases are not used in a fetch, and there are multiple link-entities off attributes with the same name, the result will have duplicate column names. The UI always adds aliases.)

2. RetrieveMultiple Message

RetrieveMultiple is powered by a Query, and that query can be one of QueryExpression, QueryByAttribute, or FetchExpression. The query is in the InputParameter[“Query”], and is of type QueryBase.

QueryExpression

In the pre-stage, check that query.EntityName is the correct entity; check query.ColumnSet for your calculated field, adding any required fields to the ColumnSet.
Next, recurse through the LinkEntities, checking the LinkToEntityName and the Columns for each.

In the post-stage, the result is in OutputParameters[“BusinessEntityCollection”] which is of type EntityCollection. Each record is an Entity, and all fields from linked entities are in the Attributes collection (similar to Fetch), prefixed for links. The prefix is either the alias of the LinkEntity, or if the alias is blank it is the entityname followed by a sequence number (eg “contact1.fullname”, “contact1.firstname” “account2.name”. The sequence number is assigned to each LinkEntity based on the order it occurs in the LinkEntity Tree.

(Note: to be complete, something similar should be done for the Orders, so that you can sort by your calculated column)

  • Account (no prefix)
    • Contact (contact1.<attribute>)
    • Contact [alias shipper] (shipper.<attribute>)
    • User (systemuser3.<attribute>)
      • BusinessUnit (businessunit4.<attribute>)

This is better and worse than the old fetch method. It guarantees that prefixes are unique, but it requires you to traverse the entire LinkEntity tree in order to know what the resulting column name will be.

In your result, you simply do Entity[“calculatedFieldName”] = Entity[“sourceFieldName”].

 

QueryByAttribute

The simplest of all – there is no nesting, no links, no related fields. You only have to check the ColumnSet (and optionally Orders).

FetchExpression

All the same complexity as the QueryExpression, except you have to process FetchXml in the Pre-Stage. The Result works the same as with QueryExpression.

 

3. Retrieve Message

This is by far the most complicated message thanks to the RelatedEntitiesQuery property of the RetrieveRequest. I haven’t determined if / when this is used by the UI. In most cases it will be null, and you only need to worry about InputParameters[“ColumnSet”]. If InputParameters[“RelatedEntitiesQuery”] is populated, it is a keyed value collection keyed on a “Relationship” – the root of a related entity query must be part of an existing relationship. However, each query in the bag can have LinkEntities – so essentially the RelatedEntitiesQuery is a collection of multiple RetrieveMultiples attached to the record. It is a mess.

In the output, the returned Entity contains a field called RelatedEntities – this is a collection of entities again keyed on the relationship. Each of the relationship queries must be handled separately as the column aliases are specific to the query.

 

—-

I’ve encountered several discussion and blog postings about this topic, and they are generally vague or incomplete. Suffice it to say handling all ways in which a field can be “hit” in CRM 2011 is daunting.

Dynamics CRM 4.0 truly calculated fields

Microsoft has a real gem with Dynamics CRM. It is both an application and a platform, highly customizable without writing a line of code, and once you throw in the code you can do most anything (depending on how unsupported you want to go)

Internally, CRM has a number of calculated fields. The customer lookup, for example is defined in the database as COALESCE(accountid, contactid). If you wanted to be really bold, I supposed you could create a column then modify it to be a function within the database, but that would be an extremely bad idea. The correct way is to use plugins and / or javascript.

Form JavaScript is fine when you are only considered with maintaining the value when a use opens the form, but what if they print? Or use the field in a view? Or another application uses the web services to retrieve the value?

You need a plugin, and that plugin has a lot of work to do.

I recently created a plugin that permits the creation of lookups that target multiple entities. After finally handling (or chosing not to handle) all the contingent scenarios I learned a few things.

1. Your plugin has to handle the Execute message, as this is the message used for FetchXml. You will have to parse the fetchxml, determine if your entity is in it, determine if your column is in it (and you have to recurse through the link-entities).
You can either register on the post stage and perform your own retrievals to get the data for the calculation, or you can sit on the pre and post stages and modify the fetchxml in the pre stage to include the extra columns needed for your calculated field. This is MUCH MUCH faster. You will need to determine what the ResultXml looks like based on how the fetch is designed.
2. Despite what the Microsoft Documentation suggests, you do not have to register the ReteieveMultiple message for all entities – just the entity in question.
(Unless you intend to handle your calculated field being used in a filter condition. This could get extremely complicated and it is better to just make the field non-searchable)
3. You should make sure your calculated field is configured as non-searchable. This prevents it being used as a filter condition from the UI (advanced find and the like)
4. The same performance recommendation holds for the Retrieve message – register pre and post, modify the ColumnSet to contain the fields needed for the calculation, then modify the result set.
5. CRM doesn’t care if extra columns show up in the result set.
6. If your field is editable (the calculation is two-way) you will need to handle the create and update, filtered for your calculated field. Add the changes to the underlying fields to the InputParameters Target propertybag.

A couple notes about CRM 2011:
I haven’t found explicit documentation of this fact, but Fetch requests no longer use the Execute message – they are instead RetrieveMultiple.
There are two reasons I have inferred for this:
1: It is now possible because the QueryExpression in CRM 2011 allows LinkEntities to have a ColumnSet – thus providing feature parity between Queries and Fetch.
2: Plugins generally now execute within the CRM transaction, so most likely the FetchXml is parsed into a QueryExpression before the plugin is fired.
This is good and bad – it reduces the number of messages you have to listen for, but it means you do have to hook the global RetrieveMultiple.

Since CRM 4.0 is pretty dated I’m guessing there isn’t too much demand for a multilookup akin to the Customer lookups – but I have one, and I have managed to retool the basic functionality to work in CRM 2011 as well. (Who knows, it may end up as a solution!)

CRM 4.0: Programmatically Upload a Mail Merge Template

image

 

The Mail Merge Facility in Microsoft Dynamics CRM 4.0 works fairly well in most cases. It is at the same time a bit to complicated for casual users, and a bit too simplistic for power users, but at least in our environment it has been tweakable enough to be useful.

Like everything in CRM the Mail Merge Template is an Entity, and thankfully Microsoft permits it’s customization (without unsupported tweaking).

image

You can add custom attributes and relationships; you can customize the form and the views; you can trigger workflows when attributes change (although you cannot register a plugin against it without unsupported tweaking). The tricky fields – body, filename, documentformat – they are all read/write through web services (http://msdn.microsoft.com/en-us/library/dd903944.aspx). Meaning you can programmatically upload new templates… but how?

 

After Google failed me, I initially chose the reverse engineer approach. The contents of the body field looked like Base64 (ended with equals signs), and indeed was. I create a tool to download the existing templated, modify them slightly, then add them back.  I had a working solution, but as we approached golive I got cold feet about messing things up in production- what if I’m doing it wrong and something breaks?

A bit of digging with reflector led to the solution: internally, the attachment on a mail merge template is treated the same as the annotation entity, and Microsoft has an article explaining the correct way to upload an attachment here : Upload an Attachment

The paraphrased money line: “updateTemplate.body = System.Convert.ToBase64String(byteData)”;

Dynamics CRM 4.0 Optimized Web Service Generator

Microsoft Dynamics CRM 4.0 is really a beautiful thing – with a few very annoying exceptions, it is a very flexible, easy to use, easy to customize business application development platform. It includes a comprehensive Web Service that allows you to perform pretty much any UI task programmatically.

There is one problem though – the CrmService proxy class can take upwards of 7 seconds just to initialize the class. Before a singe bit is passed from client to server, you have to wait for the .NET runtime to build the ginormous XmlSerializers for the equally ginormous Web service.

Normally Sgen.exe can be used to improve the startup time of Web Services, but for some reason the CRM Service doesn’t always get along with pregenerated XmlSerializers.

The solution: remove all those unused entities! My organization’s current CRM instance has 192 entities, which translates into (at least) 1,616 separate classes in the generated proxy code.
I have built a tool that consumes a CRM 4.0 web service wsdl, and produces a nice packaged .dll (complete with embedded XmlSerializers) with practically all the unnecessary fluff removed. The produced dll fully supports Fetch and the DynamicEntity – just no static types.

On a .net IIS server, creating a new CrmService now takes 0.12 seconds – instead of 6+.

Update: Released! You can get it on Codeplex here:
http://crmservicegenerator.codeplex.com/