When was the last time a user logged in?

Recently, I was asked to look into which users where using our production MSCRM 2011 environment. Prior to MSCRM 2011, you could get a rough idea of which users where accessing CRM by looking through IIS logs. However, this was tedious and it was not very accurate, since the Outlook Client traffic is included in the IIS logs. Luckily, Microsoft made life easier with CRM 2011 and included a column named LastAccessTime in the SystemUserOrganizations table of the MSCRM_CONFIG database. To get the LastAccessTime for your users, you can run the query below from your Organization database, #ORGNAME#_MSCRM.

SELECT
     SU.SystemUserId
     ,SU.DomainName
     ,SU.FullName
     ,SUO.LastAccessTime
FROM SystemUser SU
  INNER JOIN [MSCRM_CONFIG].[dbo].[SystemUserOrganizations] SUO ON SUO.CrmUserId = SU.SystemUserId
  INNER JOIN [MSCRM_CONFIG].[dbo].[SystemUserAuthentication] SUA ON SUA.UserId = SUO.UserId
ORDER BY SU.DomainName

I want to credit Results on Demand who posted the query that inspired mine. You can see their version of the query that runs from the MSCRM_CONFIG database at http://www.resultondemand.nl/support/blog/post/How-to-get-the-last-login-date-and-time-of-a-CRM-user-(MSCRM-2011).aspx

CRM 2011 - Caller is not an owner for SubscriptionID Error

Over the past few weeks my team has been working on reviewing and revising our CRM environment in preparation to migrate to CRM 2011. As part of the preparation we have been testing the offline client and in doing so one of my devs ran into the error message of "Caller 2222222-2222-2222-2222-22222222 is not an owner for SubscriptionID 00000000-0000-0000-0000-000000000000" when attempting to go offline.

Now this odd as my devs CallerId\SystemUserId was 11111111-1111-1111-1111-111111111111 and not 2222222-2222-2222-2222-22222222, which is the SystemUserId for our TestUser1. So why was the outlook client making a call as another user? Well I after doing some initial investigation I conclude that it would just be faster to reconfigured the Outlook client, so we started the configuration wizard and typed in our server URL and hit the ‘Test Connection …’ button.  However, when it finished it showed that my dev authenticated as DOMAIN\TestUser1.

What! How is this happening? So I ask my dev, "Are you logged in as DOMAIN\TestUser1?" To which he replies, "No."  Okay, so how was the Outlook client getting another user’s credentials when it authenticates against the CRM server. Well it turned out that my dev had told IE to store the user name and password for Domain\TestUser1 when he was testing the CRM 2011 web client. We found this by going to Control Panel > User Accounts

And then clicking the ‘Manage Passwords’ button on the Advanced tab, which displays after clicking ‘User Account’

Clicking the ‘Manage Passwords’ button displays a form that lists sites that have user name and passwords saved.

When I clicked on the entry for crm.domain.com I saw the following

So to remove the entry we just selected the row for crm.domain.com and clicked the ‘Remove’ button. Once this was done we were able to reconfigure the CRM Outlook client and have it authenticate as my dev.  He wa then was able to go offline with the CRM 2011 Outlook client.

CRM 2011 - Adding scrollbars to a sub-grid

The sub-grid control in CRM 2011 is fantastic and it has replaced a lot of script I use to write. Unfortunately, as of Roll Up 2 you can only specify the number of record to display on a sub-grid. This in turn determines how many records are on each page of the sub-grid and how the sub-grid appears when it is rendered on a form. Well if your users are like mine, they do not want to page through the the sub-grid at 5,10, or X records a page. No, what they want is to have the paging set to 250 records per page and scroll through the records. While you can have a sub-grid display 250 records at a time, you can not constrain it to a specific height and force it to scroll.

Well to get around this issue you can use the script below which sets the RecordsPerPage and maxrowsbeforescroll parameters of the sub-grid and adjusts the height of #SUB-GRID ID#_divDataArea div element of the sub-grid. The RecordsPerPage and maxrowsbeforescroll parameters and passed to the web service that renders the sub-grid and controls how many reds care visible at a given time in a sub-grid when it is rendered. Please let me know if you find any issues of have a have a better method for addressing this particular issue.

//Warning: This code below is not supported by Microsoft

//Place this code at the start of your script
function setSubgridHeight(grdID, noRecordsDisplay, noRecordsPerPage, forceRefresh) {
/// <summary>
/// Causes sub-grid to scroll by setting the number of records to be displayed and 
/// </summary>
/// <param name="grdID" type="string">
/// ID of sub-grid *required
/// </param>
/// <param name="noRecordsDisplay" type="int">
/// Controls height of sub-grid and how many recors are displayed
/// Before applying scrolling
/// </param>
/// <param name="noRecordsPerPage" type="int">
/// Determines number of records to display per page.  If not provided
/// The value defined in the form editor will be used
/// </param>  
/// <returns type="nothing" />
  if (!IsNull(grdID)) {                
    var tbl = $("#" + grdID);

    //Adjust the sub-grid page size if desired, otherwise this is controlled by the form editor
    if (!IsNull(noRecordsPerPage)) {
      //Get reference to divGridProps element of desired grid.
      //Microsoft stores property infomrmaiton about the sub-grid within this element
      var divProps = $("#divGridProps", tbl);
      $("#recsPerPage", divProps).attr("value", noRecordsPerPage);  //Controls number of rows returned by data provider and sets the page size for the sub-grid
    }

    //Get reference to divGridParams element of desired grid.
    //Microsoft stores parameter infomrmaiton about the sub-grid within this element  
    var divParams = $("#divGridParams", tbl);  

    noRecordsDisplay = (IsNull(noRecordsDisplay) ? "5" : noRecordsDisplay);  //Default to 5 visible records at a time

    $("#RecordsPerPage", divParams).attr("value", noRecordsDisplay);  //Controls number of rows displayed in output table
    $("#maxrowsbeforescroll", divParams).attr("value", noRecordsDisplay);  //Force scroll bar to display if more records included than desired to display
  
    //Clear all of the empty TR that are added to fill the table height
    var tb = $("#" + grdID + "_d").parent().parent();
    $("tr", tb).filter(function(index) { return $(this).html() == ""; }).remove();

    //Now resize the data area of the grid
    $("#" + grdID + "_divDataArea").css("height", ((noRecordsDisplay - 1) * 25) + "px");
  
    //Refresh the grid if desired
    if(forceRefresh) { document.getElementById(grdID).control.refresh(); }
  } else {
    throw Error.argument("grdID", "grdID is null or undefined")
  }
} 

//To use you would write something like 
setSubgridHeight(#SUB-GRID ID#, 10, null, false);

How to get the Entity Type Code / Object Type Code in CRM 2011 - Part 2

This is a followup to my previous post about How to get the Entity Type Code in CRM 2011. While that method works, it requires you to go to a page and manually get the type code for the entity. What we really need is a way through script to get the entity type code / object type code by passing an entity name. Well, the code below uses Microsoft's own logic and will return the entity type code / object type code for the entity name you provide it.  Let me know if you have any trouble or have any suggestions on how to improve this.

var getObjectTypeCode = function(entityName) {
/// <summary>
/// Gets the EntityTypeCode / ObjectTypeCode of a entity
/// </summary>
/// <param name="entityName" type="string">
/// Name of entity to return object type code of
/// </param>
/// <returns type="int" /> 
 var lookupService = new RemoteCommand("LookupService", "RetrieveTypeCode");
 lookupService.SetParameter("entityName", entityName);
 var result = lookupService.Execute();

 if (result.Success && typeof result.ReturnValue == "number") {
  return result.ReturnValue;
 } else {
  return null;
 }
}

CRM 2011 - How to filter the 'Add Existing' button on a sub-grid. (A.K.A. filtered sub-grid look up)

Update 07 September 2011

If you are looking for a simpler solution that only allows for changing the fetchXML used by the lookup control check out James's post at CRM 2011 Change SubGrid FetchXML.

---

I had hoped that Microsoft would provide support for applying custom views to the lookup used by sub-grids, since you can use the Xrm.Page.ui.addCustomView method to apply a custom view to a lookup control. Unfortunately, the Xrm.Page.ui.addCustomView method only works for lookup controls. So for the past three full days I have spent my time trying to understand how the addCustomView method works so I could extend it to support sub-grids. The code below does this; however, IT HAS BEEN TESTED MINIMALLY and thus may not address your particular case. If you find issues or improve this please let me know so I can share it with the rest of the community.

//Warning: This code below is not supported by Microsoft

//Place this code in the onload event of a form containing a sub-grid that you wish to filter

window.attachEvent("onload", function() {
  function locAssocObj_custom(iType, sSubType, sAssociationName, iRoleOrdinal, additionalParams, showNew, showProp, defaultViewId, customViews, allowFilterOff, disableQuickFind, disableViewPicker, viewsIds) {
    var lookupItems = LookupObjects(null, "multi", iType, 0, null, additionalParams, showNew, showProp, null, null, null, null, defaultViewId, customViews, null, null, null, null, allowFilterOff, disableQuickFind, disableViewPicker, viewsIds, false);
    if (lookupItems) lookupItems.items.length > 0 && AssociateObjects(crmFormSubmit.crmFormSubmitObjectType.value, crmFormSubmit.crmFormSubmitId.value, iType, lookupItems, iRoleOrdinal == 2, sSubType, sAssociationName)
  }

  if (Mscrm.GridRibbonActions.addExistingFromSubGridAssociated) {
    Mscrm.GridRibbonActions.addExistingFromSubGridAssociated_org = window.Mscrm.GridRibbonActions.addExistingFromSubGridAssociated;

    window.Mscrm.GridRibbonActions.addExistingFromSubGridAssociated = function(gridTypeCode, gridControl) {
      if (IsNull(gridControl)) {
        throw Error.argument("value", "gridControl is null or undefined")
        return;
      }

   //Check if a filtered grid.  If not call default method      
      if (IsNull(gridControl._element.isFiltered) || !gridControl._element.isFiltered) {
        Mscrm.GridRibbonActions.addExistingFromSubGridAssociated_org(gridTypeCode, gridControl)
      } else {
        var e = document.getElementById(gridControl._element.id);

        var showNew = (IsNull(e.showNew) ? null : e.showNew);
        var showProp = (IsNull(e.showProp) ? null : e.showProp);
        var allowFilterOff = (IsNull(e.allowFilterOff) ? null : e.allowFilterOff);
        var disableQuickFind = (IsNull(e.disableQuickFind) ? null : e.disableQuickFind);
        var disableViewPicker = (IsNull(e.disableViewPicker) ? null : e.disableViewPicker);
        var customViews = (IsNull(e.customViews) ? null : e.customViews);
        var defaultViewId = (IsNull(e.defaultViewId) ? null : e.defaultViewId);
        var viewsIds = null;

        var $v_0 = gridControl.getParameter("relName"), $v_1 = gridControl.getParameter("roleOrd"), $v_2 = false;

        switch (gridTypeCode) {
          case Mscrm.EntityTypeCode.List:
            switch ($v_0) {
              case "campaignactivitylist_association":
                window.parent.locAssocObjCampaignActivity(gridTypeCode, "", $v_0, $v_1);
                break;
              case "campaignlist_association":
                locAssocObjCampaign(gridTypeCode, "subType=targetLists", $v_0, $v_1);
                break;
              case "listlead_association":
                window.parent.locAssocObjLead(gridTypeCode, "", $v_0, $v_1);
                break;
              case "listcontact_association":
                window.parent.locAssocObjContact(gridTypeCode, "", $v_0, $v_1);
                break;
              case "listaccount_association":
                window.parent.locAssocObjAccount(gridTypeCode, "", $v_0, $v_1);
                break;
              default:
                $v_2 = true;
                break
            }
            break;
          case Mscrm.EntityTypeCode.Campaign:
            switch ($v_0) {
              case "campaignlist_association":
                locAssocObjList(gridTypeCode, "subType=targetLists", $v_0, $v_1);
                break;
              case "campaigncampaign_association":
                locAssocObjCampaign(gridTypeCode, "", $v_0, $v_1);
                break;
              default:
                $v_2 = true;
                break
            }
            break;
          case Mscrm.EntityTypeCode.Product:
            switch ($v_0) {
              case "productsubstitute_association":
                locAssocObjProduct(gridTypeCode, "", $v_0, $v_1);
                break;
              case "productassociation_association":
                locAssocObjProduct(gridTypeCode, "", $v_0, $v_1);
                break;
              case "campaignproduct_association":
                locAssocObjCampaign(gridTypeCode, "", $v_0, $v_1);
                break;
              case "competitorproduct_association":
                window.parent.locAssocObjCompetitor(gridTypeCode, "", $v_0, $v_1);
                break;
              default:
                $v_2 = true; break
            }
            break;
          default:
            $v_2 = true;
            break
        }
        if ($v_2) {
          var $v_3 = locAssocObj_custom;
          var additionalParams = null;
          //$v_3(gridTypeCode,"",$v_0,$v_1, additionalParams)     
          $v_3(gridTypeCode, "", $v_0, $v_1, additionalParams, showNew, showProp, defaultViewId, customViews, allowFilterOff, disableQuickFind, disableViewPicker, viewsIds);
        }
      }
    }
  }
});

function addSubgridCustomView(subgridID, entityTypeCode, displayName, fetchXML, layoutXML, filterType, isDefault) {
/// <summary>
/// Adds a custom view to lookup page used by a subgrid
/// </summary>
/// <param name="subgridID" type="string">
/// ID of sub-grid to add custom view to
/// </param>
/// <param name="entityTypeCode" type="int">
/// The entity type code returned by the fetch statement
/// </param>
/// <param name="displayName" type="string">
/// The name to use for the view
/// </param>
/// <param name="fetchXML" type="string">
/// The fetch XML to be used by the view
/// </param>
/// <param name="layoutXML" type="string">
/// The layout XML to be used by the view.
/// If layoutXML == null the layout from the default view of the entity will be used
/// </param>
/// <param name="filterType" type="int">
/// Type of custom view.  Default is 0
/// </param>
/// <param name="isDefault" type="boolean">
/// The layout XML to be used by the view.  
/// If layoutXML == null the layout from the default view of the entity will be used
/// </param>
/// <returns type="nothing" />
 if (IsNull(subgridID)) { throw Error.argument("value", "ID of sub-grid to filter not provided") ; return; }
 if (IsNull(entityTypeCode)) { throw Error.argument("value", "Entype code of returned object by fetchXML not provided"); return; }
 if (IsNull(fetchXML)) { throw Error.argument("value", "FetchXml not provide for custom view on sub-grid"); return; }
 if (IsNull(layoutXML)) { throw Error.argument("value", "LayoutXml not provided for custom view on sub-grid"); return; }

 var grd = document.getElementById(subgridID);

 //Check if the sub-grid has any customViews already.
 var customViews = (IsNull(grd.customViews) ? new Array() : grd.customViews);

 var oScriptlet = new ActiveXObject("Scriptlet.TypeLib");
 var viewId = oScriptlet.GUID.toString().substr(0, 38);  //Call substr to address issue of trailing white space
 
 //Create an object to hold the customView's information
 var customView = new Object();
   customView.fetchXml = fetchXML;
   customView.id = viewId;
   customView.layoutXml = layoutXML;
   customView.name = (IsNull(displayName) || displayName == "" ? "Filtered Lookup" : displayName);
   customView.recordType = entityTypeCode;
   customView.Type = (!IsNull(filterType) ? filterType : 0);

 //Add the customView object to the array of customViews
 customViews.push(customView);      

 //Add the array of custom views to the sub-grid
 grd.customViews = customViews;

 //Set this view as the default if desired
 if (isDefault) { grd.defaultViewId = viewId; }
}

var fetchXML = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>";
 fetchXML += "<entity name='account'>";
 fetchXML += "<all-attributes />";
 fetchXML += "<filter type='and'>";
 fetchXML += "<condition attribute='name' operator='like' value='test%'/>";
 fetchXML += "</filter>";
 fetchXML += "</entity>";
 fetchXML += "</fetch>";

//Build the layout to use in the lookup.  Keep in mind that you will need to know the Object Type Code
//and the primary key of object displayed
var layoutXML = "<grid name='resultset' object='1' jump='name' select='1' icon='1' preview='1'>";
 layoutXML += "<row name='result' id='accountid'>";
 layoutXML += "<cell name='name' width='300' />";
 layoutXML += "</row>";
 layoutXML += "</grid>";
   
//Apply custom view / lookup filter to the grid
addSubgridCustomView("#GRID_ID#", #OBJECT_TYPE_CODE#, "Test", fetchXML, layoutXML, 0, true);

How to get the Entity Type Code in CRM 2011

In CRM 4.0 you can get the ObjectTypeCode by using crmForm.ObjectTypeCode. Unfortunately, in CRM 2011 there is no explicit method in the Xrm namespace that returns the EntityTypeCode/ObjectTypeCode.  However, you can easily get the EntityTypeCode by using the Xrm.Page.context.getQueryStringParameters() method. This method will return an object with properties for each parameter in the query string. So to get the EntityTypeCode you would write something like

//var etc = Entity Type Code of active record
var etc = Xrm.Page.context.getQueryStringParameters().etc

Holly Cow Batman it's a Wiki

Do you have great information about MSCRM that you want to share with the world?  Then you should go to the CRM Wiki, which was just released by CRM MVPs, and add a page.  You can find the Wiki at

https://community.dynamics.com/product/crm/crmtechnical/b/crmteamblog/archive/2011/04/04/crm-mvps-introduce-the-microsoft-dynamics-crm-wiki.aspx?sms_ss=typepad&at_xt=4db7045bbb11d576%2C0

What's New in Microsoft Dynamics CRM 2011

Microsoft has published a marketing guide with shiny pictures and loads of information.

What's New in MS CRM 2011