Oct 122012

You can download the unmanged or managed solutions by clicking on unmanged or managed.

There are times when hierarchy matters, when one object rolls up into another, and you need something like the subject lookup. Unfortunately the subject lookup is not an option you can turn on for your own lookups. However, thanks to some JScript from Tanguy you can override the default lookup control, making it launch your own web-interface. Yes, I know this is not supported, but at least the rest of this add-on follows the SDK guidelines.

UR12 / Polaris: the solution above does not work properly with UR12. I have not tested this UR12 solution with previous versions of CRM 2011. It no-longer requires you to specify the Set in the Set name, i.e. Category is just Category, not CategorySet. It uses the XrmSvcToolkit by Daniel Cai instead of the XrmServiceToolkit. You can download an unmanaged solution here. The technique used by Tanguy nolonger works, so I had to access the underlying behavior of the form element. I wish I could tell you that it won’t ever break, but there is no telling, because it is an unsupported modification. If you would like to do something supported, you can put a button on the ribbon to launch the custom web-resource and load the resulting data into the field.

Here is what it looks like:

Now, I won’t go into the details of how the web-resource works (you can look at my code), but here is how you use it:

  1. You can install either the unmanged or managed solution into your CRM
  2. Add the msblg_treelookup.js to your form scripts.
    1. In the form designer for your entity click on the Form Properties button.
    2. Click add, and search for name msblg_treelookup.js, select it, and click ok.
  3. Then add an event handler for the Form OnLoad (by scrolling down and clicking add)

    1. The parameters are:

      1. Attribute Name
      2. Entity Set Name (for the UR12 release, do not use Set, just use the entity name)
      3. Object Type Code
      4. The Id Field
      5. The Name Field
      6. The Parent Field (Lookup to the same entity)
      7. Title for the lookup window
      8. Description for the lookup window
      9. Column Header
      10. A REST filter that will be applied to all queries (not required)

The LookupTree function makes the existing lookup button launch the custom tree-view lookup. If you wanted to use this control in a completely supported manner, with some modifications you could add a button to launch the control.

Feb 182010

Unfortunately, CRM 4.0 does not currently support adding the queue to a case advanced find. However, that doesn’t mean it can’t be done.

Please note, that this is not a supported modification or change.

Follow the steps below to modify your fetchXML and your layoutXML to have the queue name show up in the advanced find. You can even update the name in the UserQueryBase to rename your advanced find to something better.

  1. Create your advanced find, setup your filters etc.
  2. Save your advanced find (name it something unique)
  3. Get the guid of your advanced find (using open pop-ups in a new tab or using a select statement to look it up in the db.
    SELECT UserQueryID FROM UserQueryBase WHERE Name='Cases (w/Queue Name) - IT Example'
  4. Then using the id, execute the following code to add the queue name:
    UPDATE UserQueryBase SET
    FetchXml = REPLACE(FetchXml,'</entity>',
    '<link-entity name="queueitem" from="objectid" to="incidentid" visible="false" link-type="outer" alias="qi">
    <link-entity name="queue" from="queueid" to="queueid" visible="false" link-type="outer" alias="Q">
    <attribute name="name"/></link-entity></link-entity></entity>')
    ,LayoutXML = REPLACE(LayoutXml,'</row>',
    '<cell name="Q.name" width="200" disableSorting="0"/></row>')
    -- Make sure to change the UserQueryID
    WHERE UserQueryID='71F94C46-F217-DF11-A241-0050569B4FF3'
  5. If you have your advanced find window open, close and reopen it before trying to run it and see the "Q.name" column.

If you have any problems or questions, let me know. Again this is setup to work on advanced find for cases, I’m sure it could be modified for other queue-able entities.

With CRM 2011, you can include fields from related entities, so you don’t have to use this method, you can just add the column through the user-interface.

Oct 122009

Here is a video tutorial of how to take a case advanced find, edit the query, troubleshoot errors in SQL Management Studio, and update your excel query.

The steps are:

  1. Run an advanced find in CRM
  2. Export it to Excel as a Dynamic Worksheet
  3. Open it, save it as an excel file.
  4. In Excel 2007, Click on Data in the ribbon, then Connections.
  5. Click Properties
  6. Click on Definition tab, edit the query
  7. Click ok, then click close
Feb 102009

Paging Limits control how many items show up on the list pages in crm. Most notably advanced finds. I like to be able to display a lot of information, and I need to be able to run operations on more than 250 items. Using the following SQL query I was able to increase my paging limit to 1000.

UPDATE UserSettings SET PagingLimit=1000 WHERE SystemUserId IN
(Select SystemUserId from SystemUserBase WHERE FullName like 'Carlton%')
Feb 042009

One of the problems I noticed in a recent upgrade to CRM 4 was that the System Administrators group did not have access to assign other people’s saved advanced finds (user queries). This is extremely annoying because I have a single aspx page that uses the CRM SDK to allow a system administrator to move any saved view from being owned by one person to another.

Saved views are great for the organization, departments, and teams, but when a team uses them, often times they have someone create and share the views, then they leave the company or move to a different group within the company. They either can’t or no longer wish to be responsible for the advanced finds they have created and shared out with their fellow CRM users.

My custom page allows me to assign those views to someone else, allowing them to move the responisibility to another CRM user.

To update the CRM privileges to allow System Administrators full access (except for read) to all views, you can run the following sql code against the database.

SQL Code:

UPDATE RolePrivileges SET PrivilegeDepthMask=128 WHERE RolePrivilegeId IN
  SELECT RolePrivilegeId FROM RolePrivileges r
  INNER JOIN PrivilegeBase pb ON r.privilegeid=pb.privilegeid
  INNER JOIN RoleBase rb ON r.roleid = rb.roleid
  WHERE pb.Name in
    AND rb.Name = 'System Administrator'

The code for reassigning objects is really straight forward, it uses sql to get the lists, then uses the sdk to assign the items to other users. It doesn’t fail because of the sql modification above.

ASP.Net Code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="reassign.aspx.cs" Inherits="mst_Support.userquery.reassign" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
    <link href="../css/support.css" rel="stylesheet" type="text/css" />
    <form id="form1" runat="server">
    <div align="center">
        <asp:ScriptManager ID="smgr" runat="server">
        <br />
        <b>Source User:</b>
        <asp:DropDownList ID="User_Src" runat="server" DataSourceID="crm_SystemUsers" DataTextField="FullName"
            DataValueField="SystemUserId" AutoPostBack="True" OnSelectedIndexChanged="User_Src_SelectedIndexChanged">
        <br />
        <asp:UpdatePanel ID="upnQueries" runat="server" UpdateMode="Conditional">
                <br />
                <b>Saved Advanced Finds:<br />
                <asp:ListBox ID="QueueList" runat="server" SelectionMode="Multiple" Rows="15"></asp:ListBox>
                <asp:AsyncPostBackTrigger ControlID="User_Src" EventName="SelectedIndexChanged" />
        <br />
        <b>Target User:</b>
        <asp:DropDownList ID="User_Target" runat="server" 
            DataSourceID="crm_SystemUsers" DataTextField="FullName"
        <br />
        <br />
        <asp:Button ID="Assign" runat="server" onclick="Assign_Click" Text="Assign" />
        <br />
        <br />
        <asp:Label ID="lblResponse" runat="server"></asp:Label>
        <br />
        <br />
        <asp:SqlDataSource ID="crm_SystemUsers" runat="server" ConnectionString="<%$ ConnectionStrings:crm_db %>"
            SelectCommand="SELECT [SystemUserId], [FullName] FROM [SystemUserBase] WHERE ([IsDisabled] = @IsDisabled) ORDER BY [FullName]"
            ProviderName="<%$ ConnectionStrings:crm_db.ProviderName %>">
                <asp:Parameter DefaultValue="false" Name="IsDisabled" Type="Boolean" />

C-Sharp code

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using mst_Support.crmsdk;
using System.Web.Services.Protocols;
using System.Web.Configuration;

namespace mst_Support.userquery
    public partial class reassign : System.Web.UI.Page
        protected void Page_Load(object sender, EventArgs e)


        public void AssignUserQuery(string queryid, string userid)
                    CrmAuthenticationToken token = new CrmAuthenticationToken();
                    token.AuthenticationType = 0; //AD
                    token.OrganizationName = "Your Organization"; //Could retrieve it.

                    CrmService crmService = new CrmService();
                    crmService.CrmAuthenticationTokenValue = token;
                    crmService.Credentials = System.Net.CredentialCache.DefaultCredentials;

                    //crmService.Credentials = System.Net.CredentialCache.DefaultCredentials;

                    SecurityPrincipal newowner = new SecurityPrincipal();
                    newowner.Type = SecurityPrincipalType.User;
                    newowner.PrincipalId = new Guid(userid);

                    TargetOwnedUserQuery query = new TargetOwnedUserQuery();
                    query.EntityId = new Guid(queryid);

                    // Create the request object.
                    AssignRequest assign = new AssignRequest();

                    // Set the properties of the request object.
                    assign.Assignee = newowner;
                    assign.Target = query;
                    AssignResponse assignResponse = (AssignResponse)crmService.Execute(assign);
            catch (SoapException ex)
                lblResponse.Text += "<b>Error: </b>" +ex.ToString() + 
                                    "<br /><br />\r\n";
                lblResponse.Text += "<b>Inner Detail: </b>" + ex.Detail.InnerText +
                                    "<br /><br />\r\n";
                lblResponse.Text += "<b>InnerException: </b>" + ex.InnerException.ToString() + 
                                    "<br /><br />\r\n";
                lblResponse.Text += "<b>StackTrace: </b>" + ex.StackTrace.ToString() + 
                                    "<br /><br />\r\n";

        protected void User_Src_SelectedIndexChanged(object sender, EventArgs e)
            string query = "SELECT [Name], [UserQueryId] FROM [UserQueryBase] WHERE ([OwningUser] = '"+
                           User_Src.SelectedValue+"') ORDER BY [Name]";

            // Pull the crm_db connection string for the webconfig. (you could just replace this with
            // your connection string.

            SqlDataAdapter da = new SqlDataAdapter(query, 

            DataSet ds = new DataSet();

            QueueList.DataSource = ds.Tables[0].DefaultView;
            QueueList.DataTextField = "Name";
            QueueList.DataValueField = "UserQueryId";

        protected void Assign_Click(object sender, EventArgs e)
                foreach (ListItem li in QueueList.Items)
                    if (li.Selected)
                        lblResponse.Text += "Item: " + li.Value + " to: ";
                        lblResponse.Text += User_Target.SelectedValue + "<br />";
                        AssignUserQuery(li.Value, User_Target.SelectedValue);
                lblResponse.Text += "<br />Moved Successfully";
                User_Src_SelectedIndexChanged(sender, e);
            catch (Exception ex)
                lblResponse.Text += ex.ToString() + "< br/>";
                lblResponse.Text += "Please report this error to internal systems.<br />";