Category Archives: General

Retrieving CRM Option Set values from SQL #tips #crm #sql #optionsets

Getting the Option Set Values from SQL

C Sharp & SQL Tips and Tricks

Today I needed to get a list of all the options sets for a particular option set. One option would be to do a DISTINCT query on the filtered view but that might not return me all of the values as some might not be used.

I did a quick search and found out that the option set values are stored in the FilteredStringMap view, here is the query:

View original post

Advertisements

Filtering Views by intercepting Retrieve Multiple Queries with a Plugin

The best way of filtering System Views by diferent parameters or by roles is to capture the Retrieve Multiple queries and adding filters to the related queryexpresion.

It sound like something very difficult but this can be archieved easyly by creating a plugin and registering it over the Retrieve Multiple Call of the related entity.

This plugin must be registered with Pre-Operation, Synchronous and Server settings as shown on the image.

Plugin Registration for Retrieve Multiple

Plugin Registration for Retrieve Multiple

The development of the plugin is quite easy.

First of all the plugin has to inherit from the following base class.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;
using Microsoft.Xrm.Sdk;
using System.Globalization;

namespace L.Plugin
{
    public abstract class LPluginBase : IPlugin
    {       
        static protected IPluginExecutionContext GetExecutionContext(System.IServiceProvider serviceProvider)
        {
            IPluginExecutionContext pluginExecutionContext = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

            return pluginExecutionContext;
        }

        public abstract void Execute(System.IServiceProvider serviceProvider);
    }

    public static class MessageNames
    {
        public static String Create
        {
            get
            {
                return "Create";
            }
        }

        public static String Delete
        {
            get { return "Delete"; }
        }
    }

    public static class ExecutionStage
    {
        public static int PreEvent
        {
            get { return 20; }
        }

        public static int PostEvent
        {
            get { return 40; }
        }
    }
}

Then the plugin that intercepts the call and adds a new filter base on a field of the calling entity will look like:


namespace L.DW.RetrieveCaching
{
    using System;
    using System.ServiceModel;
    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Query;
    using L.Plugin;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Xrm.Sdk.Metadata;
    using Microsoft.Crm.Sdk;
    using Microsoft.Crm.Sdk.Messages;
    using Microsoft.Xrm.Sdk.Messages;

    /// <summary>
    /// PreValidateHazardousEventRevisionRetrieveMultiple Plugin.
    /// </summary>    
    public class PreValidateHazardousEventRevisionRetrieveMultiple : LPluginBase
    {
        public override void Execute(IServiceProvider serviceProvider)
        {

               if (serviceProvider == null)
                {
                    throw new ArgumentNullException("serviceProvider");
                }

                
                Microsoft.Xrm.Sdk.IPluginExecutionContext context =
                      (Microsoft.Xrm.Sdk.IPluginExecutionContext)serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));

                // Get the OrgService
                IOrganizationServiceFactory serviceFactory =
                    (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

                // Get a reference to the tracing service.
                ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

                // Check that all of the following conditions are true:
                //  1. plug-in is running synchronously
                //  3. plug-in is running on the 'RetrieveMultiple' event
                if (context.Mode == 0 && context.MessageName.Equals("RetrieveMultiple"))
                {
                  
                    // The InputParameters collection contains all the data passed in the message request.
                    if (context.InputParameters.Contains("Query"))
                    {
                        if (context.InputParameters["Query"] is QueryExpression)
                        {
                            if (UserHasRole(context.InitiatingUserId, "Drinking Water Team User", service))
                            {
                                // Get the QueryExpression from the property bag
                                QueryExpression objQueryExpression = (QueryExpression)context.InputParameters["Query"];

                                // We can modify the original query to just show record with Completed or Completed with issues estaus.

                                ConditionExpression statusCompletedCond = new ConditionExpression()
                               {
                                   AttributeName = "L_status",
                                   Operator = ConditionOperator.In,
                                   Values = { 3, 4 }
                               };

                                objQueryExpression.Criteria.AddCondition(statusCompletedCond);
                            }

                        }

                    }
                }

            
        }

We could make something even more complex by filtering the results from the status of a field in a N to N relationship.


namespace L.DW.RetrieveCaching
{
    using System;
    using System.ServiceModel;
    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Query;
    using Lema.Plugin;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Xrm.Sdk.Metadata;
    using Microsoft.Crm.Sdk;
    using Microsoft.Crm.Sdk.Messages;
    using Microsoft.Xrm.Sdk.Messages;

    /// <summary>
    /// PreValidateControlMeasureRetrieveMultiple Plugin.
    /// </summary>    
    public class PreValidateControlMeasureRetrieveMultiple : LPluginBase
    {
        public override void Execute(IServiceProvider serviceProvider)
        {
            if (serviceProvider == null)
            {
                throw new ArgumentNullException("serviceProvider");
            }
                           if (serviceProvider == null)
                {
                    throw new ArgumentNullException("serviceProvider");
                }

                
                Microsoft.Xrm.Sdk.IPluginExecutionContext context =
                      (Microsoft.Xrm.Sdk.IPluginExecutionContext)serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));

                // Get the OrgService
                IOrganizationServiceFactory serviceFactory =
                    (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

                // Get a reference to the tracing service.
                ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

                // Check that all of the following conditions are true:
                //  1. plug-in is running synchronously
                //  3. plug-in is running on the 'RetrieveMultiple' event
                if (context.Mode == 0 && context.MessageName.Equals("RetrieveMultiple"))
                {                                                    
                    // The InputParameters collection contains all the data passed in the message request.
                    if (context.InputParameters.Contains("Query"))
                    {
                        if (context.InputParameters["Query"] is QueryExpression)
                        {
                            if (UserHasRole(context.InitiatingUserId, "Water Team User", service))
                            {

                                // Get the QueryExpression from the property bag
                                QueryExpression objQueryExpression = (QueryExpression)context.InputParameters["Query"];
                                objQueryExpression.Distinct = true;

                                // Calculate completed Revisiones
                                string[] CompletedGuids = getAllOpenRevisionsGuid(service);

                                // Add the filter using the N to N middle table 
                                LinkEntity linkEntity1 = new LinkEntity();
                                linkEntity1.JoinOperator = JoinOperator.Natural;
                                linkEntity1.LinkFromEntityName = "L_controlmeasure";
                                linkEntity1.LinkFromAttributeName = "L_controlmeasureid";
                                linkEntity1.LinkToEntityName = "L_controlmeasure_hazardouseventrevision";
                                linkEntity1.LinkToAttributeName = "L_controlmeasureid";

                                // Condition : where hazarrevisionID is in the returned Guidvalues
                                ConditionExpression statusCompletedCond = new ConditionExpression("L_hazardouseventrevisionid", ConditionOperator.In, CompletedGuids);

                                linkEntity1.LinkCriteria.AddCondition(statusCompletedCond);

                                objQueryExpression.LinkEntities.Add(linkEntity1);     
                                   
                            }

                        }

                    }
                }


        }

NOTE: Probably this second option of filtering by the N to N relationship could be done without making 2 queries but I couldn’t make it work, so as my teacher used to say, divide and you will win 😛 !!! Using 2 queries helped me to filter the results based on a N to N relationship.

I hope it was helpful!

Cheers,

Hache

Modify Add Existing View

http://danielcai.blogspot.ie/2011/12/filtered-lookup-for-existing-button-of.html

For rollup 12 issues:

http://www.magnetismsolutions.com/blog/paulnieuwelaar/2013/02/04/filter-n-n-add-existing-lookup-dynamics-crm-2011-rollup-12

FILTERING THE RESULTS USING THE SOAP END POINT


//filters an add existing lookup view (1:N)
function addExistingFromSubGridCustom (gridTypeCode, gridControl, fetch, layout, viewName) {
    var viewId = "{1DFB2B35-B07C-44D1-868D-258DEEAB88E2}"; // a dummy view ID
    var relName, roleOrd;
    if (typeof (gridControl.GetParameter) === "function") { //post rollup 12
        relName = gridControl.GetParameter("relName");
        roleOrd = gridControl.GetParameter("roleOrd");
    }
    else { //pre rollup 12
        relName = gridControl.getParameter("relName");
        roleOrd = gridControl.getParameter("roleOrd");
    }

    //creates the custom view object
    var customView = {
        fetchXml: fetch,
        id: viewId,
        layoutXml: layout,
        name: viewName,
        recordType: gridTypeCode,
        Type: 0
    };

    //pops the lookup window with our view injected
    var lookupItems = LookupObjects(null, "multi", gridTypeCode, 0, null, "", null, null, null, null, null, null, viewId, [customView]);

    //once the lookup window is closed, we need the parent record ID and ETC before associating selected records
    if (lookupItems && lookupItems.items.length > 0)
	{
        var parentId;
        var parentTypeCode;
        if (typeof (GetParentObject) == "function")
		{ //post rollup 12 has its own function to get this
            var parent = GetParentObject();
            parentId = parent.id;
            parentTypeCode = parent.objectTypeCode;
        }
        else
		{ //pre rollup 12 still needs to use the old way
            var parent = typeof (crmFormSubmit) == "undefined" ? $get("crmFormSubmit") : crmFormSubmit; //according to daniels blog crmFormSubmit should already be defined, but it's not...
            if (parent) {
                parentId = parent.crmFormSubmitId.value;
                parentTypeCode = parent.crmFormSubmitObjectType.value;
            }
            else {
                parentId = window.parent.crmFormSubmit.crmFormSubmitId.value;
                parentTypeCode = window.parent.crmFormSubmit.crmFormSubmitObjectType.value;
            }
        }

        //associates the selected records
        AssociateObjects(parentTypeCode, parentId, gridTypeCode, lookupItems, IsNull(roleOrd) || roleOrd == 2, "", relName);
    }
}

//filters the Emision Point 1:N lookup view from Amendment to show only Emision Points Regarding the ammendment!!
function filterAddExistingAmpoints (gridTypeCode, gridControl, primaryEntityName, entityID)
{
	var cols = ["amendment"];
	var retrievedGroup = CrmServiceToolkit.Retrieve(primaryEntityName, entityID, cols);

    //fetch to retrieve filtered data
    var fetch = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
        "  <entity name='amemissionpoint'>" +
        "    <attribute name='amemissionpointid' />" +
		"    <attribute name='name' />" +        
		"    <attribute name='shortcode' />" +
		"    <attribute name='group' />" +		
		"    <attribute name='emissionpointtype' />" +		
		"    <attribute name='createdon' />" +
        "    <order attribute='missionpointtype' descending='false' />" +
        "    <filter type='and'>" +
        "      <condition attribute='amendment' operator='eq' value='" + retrievedGroup.getValue('amendment') +"' />" +
	    "    </filter>" +
        "  </entity>" +
        "</fetch>";

    //columns to display in the custom view (make sure to include these in the fetch query) 
    var layout = "<grid name='resultset' object='1' jump='amemissionpointid' select='1' icon='1' preview='1'>" +
        "  <row name='result' id='amemissionpointid'>" +
        "    <cell name='name' width='200' />" +
		"    <cell name='shortcode' width='30' />" +
		"    <cell name='emissionpointtype' width='100' />" +
		"    <cell name='createdon' width='100' />" +
		"    <cell name='group' width='100' />" +
        "  </row>" +
        "</grid>";

    addExistingFromSubGridCustom(gridTypeCode, gridControl, fetch, layout, "Filtered Emision Points");
}

To hide the lookup view button “new” use the following UNSUPPORTED solution.

	//Removing the New button from lookup.
		var lookupControl = Sys.Application.findComponent(lookupname);
		 
		 if (lookupControl != null)
		{
			 lookupControl._element._behaviors[0].AddParam("ShowNewButton", 0);
		 }

CRM and SHAREPOINT Integration


SharePoint Integration

The step by step tutorial can be found here.

http://blogs.technet.com/b/ptsblog/archive/2012/02/24/crm-2011-and-sharepoint-2010-integration-part-1.aspx

Modifying the Buttons on the View

Document View Buttons

If we want to modify the buttons on the document View (New , Add…) we will have to modify the CRMListComponent following this post instructions.

http://community.dynamics.com/crm/f/117/t/84936.aspx#.UVwVOpPvtqU

A great post about attaching files to notes.

http://lakshmanindian.wordpress.com/2012/11/01/attachments-in-microsoft-dynamics-crm-2011/

Great post about licencing

CRMguru

Kevin Machayya posted links in this article to an updated version of the Microsoft Dynamics CRM 2011 Pricing and Licensing Guide. This is the definitive document for figuring out what you can and can’t do in various situations with different types of licence.

You can download it via Partnersource here (authorised LiveID required):

CRM 2011 Pricing and Licensing Guide Feb 2012 update

<edit> or the direct link here: http://crmdynamics.blob.core.windows.net/docs/Pricing_Licensing_Guide.pdf (I’m not sure if this only works when logged in with an appropriate LiveID)

The guide covers differences between user and device CALs, and the different types for “Full”, “Limited” and the new (for CRM 2011, as opposed to 4.0) “Employee Self Service” (ESS) CAL, which was covered in the previous version of the guide but I am still amazed by the number of users and even partners who seem to have never heard of it.

What’s an ESS CAL for?

View original post 926 more words