Exception message: Table alias is not unique amongst all top-level table and join aliases CRM 2013

Recently I was trying to create a bunch of different views and I was using an existing view to "Save As" another view.
However, after publishing my new views, I would keep getting the error "Exception message: Table alias a_d8c7aa10e688e31180c200155d53e454 is not unique amongst all top-level table and join aliases" in the Event Viewer

When I looked into the FetchXml in SavedQueryBase table for the view that I just created, I would see that there were indeed two "linked entity" tags in the FetchXml.
Searching the internet, I found mostly 2 dirty work around's and 1 perfect work around (perfect for me) to resolve this issue.

Dirty Work Around

1. Export the Solution with the entity inside it. Open the customization.xml and remove the duplicate reference to the "linked entity" tag. Import the solution.
2. Get the FetchXml for the view from the SavedQueryBase table and remove the duplicate reference to the "linked entity" tag. Update the FetchXml back into the SavedQueryBase table for that view and then Publish the Entity from CRM.

Perfect Work Around (Perfect for my situation)

I just happened to accidentally read the comments left by one of the users in one of the blogs while researching a fix for this issue. I can't remember the blog or the commenter but all thanks to him for the issue resolution.

Root Cause
This is a bug in the product and apparently there was a fix released for this in CRM 2011 UR 12 but no fix has been released so far for CRM 2013 until UR 2.
The Root Cause is that if you already have columns from a "linked entity" in your "Entity Advanced Find View", any further views that you create that reference columns from a linked entity will throw an error.

Resolution
1. Go to the Entity Customization
2. Click on Views
3. Open the Entity Advanced Find View (Ex. Contact Advanced Find View)
4. Remove any references to columns that may have been added from another entity. i.e these fields do no exist inside the current entity.
5. Save and Publish the entity.

Thats it! Now, I was able to create all my views as usual without getting any errors. Hope this helps someone.

Retrieve CRM Form Metadata Xml

The below method helps us to retrieve the FormXml of a CRM Entity

        private string GetFormXml(string websiteName, string entityName, IOrganizationService service)
        {
            string formXml = "";

            try
            {
                RetrieveEntityRequest mdRequest = new RetrieveEntityRequest()
                {
                    EntityFilters = EntityFilters.Attributes,
                    LogicalName = entityName,
                    RetrieveAsIfPublished = true
                };
                // Execute the request
                RetrieveEntityResponse entityResponse = (RetrieveEntityResponse)service.Execute(mdRequest);
                //EntityMetadata entityData = entityResponse.EntityMetadata;y

                var entityTypeCode = entityResponse.EntityMetadata.ObjectTypeCode.Value;

                QueryExpression query = new QueryExpression("systemform");
                query.ColumnSet = new ColumnSet("formxml");
                query.Criteria.AddCondition(new ConditionExpression("name", ConditionOperator.Equal, websiteName));
                query.Criteria.AddCondition(new ConditionExpression("objecttypecode", ConditionOperator.Equal, entityTypeCode));

                RetrieveMultipleRequest retrieveRequest = new RetrieveMultipleRequest();
                retrieveRequest.Query = query;

                var results = (RetrieveMultipleResponse)service.Execute(retrieveRequest);
                formXml = results.EntityCollection.Entities[0].Attributes["formxml"].ToString();

                return formXml;

            }            
            catch (FaultException ex)
            {
                throw new FaultException("Error occured while instantiating the CRM Service " + ex.ToString());
            }
            
        }

Retrieving the optionset label

The below code helps us to retrieve the label of the option set. Please note that the "optionValue" is the integer value of the label that you want to retrieve.

        public static string GetOptionSetLabel(string entityName, string attributeName, int optionValue, IOrganizationService service)
        {
            try
            {
                RetrieveAttributeRequest retrieveAttributeRequest =
                            new RetrieveAttributeRequest
                            {
                                EntityLogicalName = entityName,
                                LogicalName = attributeName,
                                RetrieveAsIfPublished = true
                            };

                // Execute the request.
                RetrieveAttributeResponse retrieveAttributeResponse =
                    (RetrieveAttributeResponse)service.Execute(retrieveAttributeRequest);

                // Access the retrieved attribute.
                PicklistAttributeMetadata retrievedPicklistAttributeMetadata = (PicklistAttributeMetadata)retrieveAttributeResponse.AttributeMetadata;

                // Get the current options list for the retrieved attribute.
                OptionMetadata[] optionList = retrievedPicklistAttributeMetadata.OptionSet.Options.ToArray();

                foreach (OptionMetadata option in optionList)
                {
                    if (option.Value == optionValue)
                    {
                        return option.Label.UserLocalizedLabel.Label;
                    }
                }
            }
            catch(Exception ex)
            {
                WriteErrorLog(entityName, "Error while retrieving the optionset labels " + ex.ToString(), service);
            }

            return String.Empty;
        }

CRM 2011 - Auto assign a role to user when a New User is created/Updated

We had a recent requirement where we needed to assign a specific role to a user on user create using a plugin. The below is the complete source code of the plugin that had been written.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Messages; 


namespace AssignDefaultRole
{
    public class AssignRoleCS : IPlugin
    {        
        Guid systemUserGuid = Guid.Empty;
        EntityReference buLookup;
        Guid buid = Guid.Empty;
        EntityCollection userRolesColl = null;
        Entity entitySystemUser;
        string securityRole = string.Empty;

        public void Execute(IServiceProvider serviceProvider)
        {
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            IOrganizationService service = factory.CreateOrganizationService(context.UserId);

            
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {
                Entity primaryEntity = (Entity)context.InputParameters["Target"]; // systemuser
                if (primaryEntity.LogicalName != "systemuser" && context.MessageName.ToLower()!="update")
                {
                    return;
                }
                else if (context.PostEntityImages.Contains("PostUserImage") && context.PostEntityImages["PostUserImage"] is Entity)
                {
                    entitySystemUser = (Entity)context.PostEntityImages["PostUserImage"];
                    buLookup = (EntityReference)entitySystemUser.Attributes["businessunitid"];
                    buid = buLookup.Id;                   
                    systemUserGuid = new Guid(entitySystemUser.Attributes["systemuserid"].ToString());

                    //Check if the user already has the Default Role
                    bool userHasRole = CheckRoleExists(service);

                    if (!userHasRole)//user does not have the Default role
                    {
                        //Get the Default Role
                        Guid DefaultSecurityRoleId = GetSecurityRoleId(service, buid, securityRole);

                        //Assign the role to the user.
                        AssignSecurityRole(systemUserGuid, DefaultSecurityRoleId, service);
                    }
                }
            }          

          
        }

        /// 

        /// Check if the user already has the default role assigned.
        /// 
  
        private bool CheckRoleExists(IOrganizationService service)
        {
            try
            {
                //UserGuid = context.InitiatingUserId;
                securityRole = "Default Privileges Role";
                QueryExpression Query = new QueryExpression()
                {
                    LinkEntities = 
                    {
                        new LinkEntity
                        {
                            LinkFromEntityName = "role",
                            LinkFromAttributeName = "roleid",
                            LinkToEntityName = "systemuserroles",
                            LinkToAttributeName = "roleid",
                            LinkCriteria = new FilterExpression
                            {
                                FilterOperator = LogicalOperator.And,
                                Conditions = 
                                {
                                    new ConditionExpression
                                    {
                                        AttributeName = "systemuserid",
                                        Operator = ConditionOperator.Equal,
                                        Values = { systemUserGuid }
                                    }
                                }
                            }
                        }
                    }
                };

                Query.ColumnSet = new ColumnSet(true);
                Query.EntityName = "role";

                userRolesColl = service.RetrieveMultiple(Query);
               
                //If the role exists, return true
                if (userRolesColl.Entities.Count > 0 && userRolesColl.Entities[0].Attributes["name"].ToString() == securityRole)
                {
                    return true;
                }

                return false;

            }
            catch (System.Web.Services.Protocols.SoapException ex)
            {
                throw new InvalidPluginExecutionException(ex.Detail.InnerText);
            }
            catch (Exception ex)
            {
                throw new InvalidPluginExecutionException(ex.Message);
            }
        }


        /// 

        /// Get the Role ID of the Default CRM 2011 role
        /// 
     
        private Guid GetSecurityRoleId(IOrganizationService service,Guid businessUnitID,string roleName)
        {
            Guid SecurityRoleId = Guid.Empty;
            EntityCollection ec = new EntityCollection();

            try
            {
                QueryExpression qe = new QueryExpression("role");
                ColumnSet set = new ColumnSet(new string[] { "roleid" });

                qe.ColumnSet = set;

                ConditionExpression cond = new ConditionExpression("name", ConditionOperator.Equal, roleName);
                ConditionExpression cond2 = new ConditionExpression("businessunitid", ConditionOperator.Equal, businessUnitID);
                FilterExpression fl = new FilterExpression(LogicalOperator.And);
                fl.Conditions.Add(cond);
                fl.Conditions.Add(cond2);

                qe.Criteria.AddFilter(fl);

                ec = service.RetrieveMultiple(qe);

                if (ec.Entities.Count > 0)
                {
                    SecurityRoleId = new Guid(ec.Entities[0].Attributes["roleid"].ToString());
                }
            }

            catch (System.Web.Services.Protocols.SoapException soapex)
            {
                throw new InvalidPluginExecutionException(soapex.Detail.InnerText);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return SecurityRoleId;
        }


        /// 

        /// Assign the Default CRM 2011 Role to the user
        /// 
        public void AssignSecurityRole(Guid userID, Guid securityRoleID, IOrganizationService service)
        {
            try
            {
                service.Associate(
                               "role",
                               securityRoleID,
                               new Relationship("systemuserroles_association"),
                               new EntityReferenceCollection() { new EntityReference("systemuser", userID) });
            }
            catch (System.Web.Services.Protocols.SoapException soapex)
            {
                throw new InvalidPluginExecutionException(soapex.Detail.InnerText);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        
    }
}

The above code first checks if the user has any role assigned. If not, it goes ahead and assigns the Default Security Role to the user.

After building the plugin, I had it registered in the following manner:

Notice that for both "Create" and "Update" events, I have registered just the one step displayed above

I am not exactly sure why, but I noticed that even if I register my plugin for the Create event, the plugin also automatically fires another update event. So, I have just registered my plugin for the update event and this works for both the create and the update.

Another issue I was facing was when I tried to register this plugin in a synchronous manner. I would keep getting the error message "Error: usersettings With Id = 92bd1403-6ace-e211-a1c4-0800279f0ce3 Does Not Exist" in the trace logs.

In order to rid myself of the error and in order to ensure that the plugin only fires once per transaction, I have registered it ONLY for the "Update" message and in "Asynchronous" execution mode.

Cheers!

CRM 2011 - Instantiating the CRM Service and Impersonating it

 The below code can be used to create the CRM service in CRM 2011 and also to impersonate the CRM service

public static IOrganizationService Service() 
{
    ClientCredentials Credentials = new ClientCredentials(); 
    Credentials.Windows.ClientCredential.UserName ="Impersonating User ID Name"; 
    Credentials.Windows.ClientCredential.Password ="Impersonating User ID password"; 

    //Update the below URL as per your environment
    Uri OrganizationUri = new Uri("http://serverName/OrganizationName/XRMServices/2011/Organization.svc"); 
    Uri HomeRealmUri = null; 

    //Get the CRM Service from the CRM Service Proxy
    using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, Credentials, null)) 
    {
        IOrganizationService service = (IOrganizationService)serviceProxy; 
        return service; 
    }
}

Publishers and Changing a Custom Entity Prefix in MS CRM 2011

I think this is a great new feature in CRM 2011. Please refer to the following article that describes how to do this:
http://support.microsoft.com/kb/2500602

Changing the Font Color of a Read-Only Field in MS CRM 2011

I recently got a request to change the font color of certain fields in CRM 2011. I used the below code to achieve this:

function setColor() {
    if (document.getElementById("new_name") != null) {
        //Add the below line if the field is disabled
        document.getElementById("new_name").disabled = false;
        //The below line changes the font color
        document.getElementById("new_name").style.color = "red";
    }
}

Note: Accessing the DOM as shown above is totally unsupported. I had to use the above method since I was not able to find any supported way of achieving this functionality

Resizing the IFRAME in Header in CRM 2011

I was trying to add an IFRAME in the header in CRM form with a height of just 1 row. For some reason. CRM would not respect the 1 row height in the header. It would display about 6 rows always. This was very strange but after posting in the CRM Dev Forum (http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/da61999c-d5e3-45a9-841c-3e09a84782d4/), it was confirmed that this seems to be a bug.
As a workaround, you an try the following code:

document.getElementById('IFRAME_Name').style.height = 35;

OR

document.getElementById("IFRAME_Test_d").parentElement.parentElement.height = 35;

Update - This issue seems to have been resolved with the Update Rollup 1 for MS CRM 2011

Getting started with writing JScript in MS CRM 2011: Step by Step

We are just in the process on migrating to 2011 and I was just trying to write some JScripts for testing purposes. However, I found that the way of writing JScript in 2011 has changed a bit and after some trying, the below are the steps that I used to write my JScripts:

1. I created a simple JScript function as shown below in Visual Studio:

function welcome() {

    alert("Welcome to CRM 2011");
}

BTW, it looks like all the JScript in 2011 can only be accessed using functions.
2. Save this file as TestWebResource.js
3. Open CRM and go to your custom entity's customization and click on Form Properties
4. Under Form Libraries -->Click on "Add"
5. In the Look Up Record, click on the "New" Button
6. In the "Web Resource: New" window, fill the mandatory fields and then click on "Browse" -->select the JScript file that we  had saved earlier and open. Save this file(I also published it).
7. Select OK in the "Look Up Record" window
8. You should be back at the main "Form Properties" window as shown below:

9. Now, click the "Add" button under Event Handlers for the onLoad event.
10. The library will default to our jscript file. Type "welcome" as the function name and click on OK
11. Save and Publish.
12. Click on Preview-->Create Form
13. You should see the alert being displayed:

Thanks!

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

I was trying to add a button to the ribbon but I was not able to test it as I would keep getting the below error while trying to import the solution zip files:

After spending a huge amount of time checking and rechecking my code, I was finally sure that the issue had to be somewhere else.
Finally, it turned out that when I used to zip the folder using the built in windows compression(zip), it would create a folder inside the zip file and place the solution files inside this folder.

So, as the error above states, CRM is looking for those files in the "Root" but was actually finding a folder in it's place and hence throwing the error. When I selected the solution files and zippped them directly, the import was successful. I know this might be a small thing but it definitely cost me an awful lot of time. :)

Adding a button to the CRM 2011 ribbon

I found a nice article about adding a button to the CRM 2011 ribbon here:

http://gtcrm.wordpress.com/2011/01/18/quick-reference-for-adding-a-button-to-the-crm-2011-ribbon/

Invalid column name 'DeletionStateCode' CRM 2011 upgrade

I was getting the error "Invalid column name 'DeletionStateCode'" when I was trying to upgrade from 4.0 to 2011. After a lot of trials and errors I got to know that there were many stored procedures and triggers in the database which were referencing to this column. 


Since the 'DeletionStateCode' has been dropped in 2011, maybe it was erroring out while trying to upgrade them.


Solution: I deleted all the triggers and stored procs from the database as it was just a test environment and I was able to install 2011 sucessfully.