Building queries with FetchXML – MS CRM

CRM offers different querying patterns to use build queries. In this module we are going to discuss about some theory about building queries with Fetch XML and followed by demo

With Fetch XML you can use all standard query operators and partial values. Fetch XML is really powerful you can do all these checks with query expressions or date related examples.  You can also combine multiple conditions.

1)      Standard Operators Examples

Ex: eq, neq, ne, gt, ge, le, lt, like, not-like, in, not-in, null, not-null

2)      Partial value Examples

Ex: Begins-with, ends-with, not-end-with, not-begin-with

3)      Date Related Examples

·         Today, tomorrow, last-seven-days, this-week, this-month, next-month

·         This-fiscal-year, next-fiscal year

·         On, on-or-before/after

Getting Related Data

1.  Useful to reference and include data from N:1 related entities
2.   We Can specify filter conditions based on the related data

Using Aggregation

Unique and powerful features with Fetch XML is Aggregation

1.  Enable aggregates vis aggregate= true and distinct=false on <fetch   tag
2. Can have one or more attributes with aggregate specified.
3.  Avg, Count, Max, Min and Sum

Ex:

Null values are not considering in computing averages, zeros are

Null, 100, 100, 1000 would have average of 100

Null, 100, 0, 1000 would have average of 66.66

Null, 100, Null, 100 would have average of 100

<fetch  distinct='false' mapping ='logical' aggregate='true'>

<entity name='opportunity'>

<attribute name='estimatedvalue' aggregate='avg' alias='estimatedvalue_avg' />

<attribute name='estimatedvalue' aggregate='min' alias='estimatedvalue_min' />

<attribute name='estimatedvalue' aggregate='max' alias='estimatedvalue_max' />

</ entity>

</ fetch>

Using Grouping

Grouping is another powerful feature we can do.

1. Can group on normal attributes – can’t group by virtuals
2.  For dates you must specify a dategrouping                                                                                Ex: Year, quarter, month, weekly or day
3. Group can be specifies for Linked Entity.

<fetch  distinct='false' mapping ='logical' aggregate='true'>

<entity name='opportunity'>

<attribute name='statecode' groupby='true' alias='state' />

<attribute name='createdon' groupby='true' alias='state' dategroupping='month' alias='created' />

<attribute name='estimatedvalue' aggregate='avg' alias='estimatedvalue_avg' />

<attribute name='estimatedvalue' aggregate='min' alias='estimatedvalue_min' />

<attribute name='estimatedvalue' aggregate='max' alias='estimatedvalue_max' />

</ entity>

</ fetch>

Limitations in Fetch XML

There are some limitations to Fetch XML query expression

1. No Union Support – instead you have to use multiple queries
2. No concept of sub queries
3. Fetch aggregates are limited to 50,000 records by default (online is limit to 50,000)  AggregateQueryRecordLimit is the setting for on-premise
4. Liked entities are limited to 10
5. Conditions can’t be to other fields on the entity
6. No way to do “or” conditions across linked entities

Demo:

Here we are going to build out an aggregate query against the entity called Account

    1) Goto File >> Click on New >> Click on Project >> Select console application

     2)  Check references. “CrmServiceHelpers.cs” and “DeviceIdManager.cs” files you can fine in CRM       SDK 2013 or find below

CrmServiceHelpers.cs 

DeviceIdManager.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.ServiceModel;

using Microsoft.Xrm.Sdk;

using Microsoft.Xrm.Sdk.Client;

using Microsoft.Xrm.Sdk.Query;

namespace FetchXML_Aggregate

{

    class Program

    {

      

        static void Main(string[] args)

        {

            // Obtain the target organization's web address and client logon

Microsoft.Crm.Sdk.Samples.ServerConnection serverConnect = new Microsoft.Crm.Sdk.Samples.ServerConnection();

Microsoft.Crm.Sdk.Samples.ServerConnection.Configuration config =        serverConnect.GetServerConfiguration();

         

           OrganizationServiceProxy _serviceProxy;

using (_serviceProxy = Microsoft.Crm.Sdk.Samples.ServerConnection.GetOrganizationProxy(config))

           {

               string strAggregate = @"<fetch mapping='logical' aggregate='true' distinct='false' >

                                        <entity name='account'>

                                            <attribute name='creditlimit' alias='creditLimitAvg' aggregate='avg' />

                                        </entity>

                                    </fetch>";

               var aggResults = _serviceProxy.RetrieveMultiple(new FetchExpression(strAggregate));

               foreach (var c in aggResults.Entities)

               {

decimal creditLimitAvg = ((Money)c.GetAttributeValue<AliasedValue>("creditLimitAvg").Value).Value;

                   Console.WriteLine("Total : " + decimal.Floor(creditLimitAvg));

                   Console.Read();

               }

           }

        }

    }

}

      3)  Press F5 .

      You can get average of creditlimit

Connecting to CRM from Visual Studio 2013

Here I am going to share how to connect to CRM Server from Visual studio.

Prerequisites: Need to install CRM developer tool kit

you can down load toolkit from here

Click on TOOLS from visual studio horizontal menu

Click on “Connect to Dynamics CRM Server…”

Provide CRM Discovery Server Name

Enter port number : for http it will be “80”, and for https it will be “443”

Select protocol

And click on Connect

Enter Authentication details and click on Log on

Select Organization and Solution Name and click on Ok button

You can able to see CRM Explorer window.

WebStorage in HTML5

In this module I am going to discuss Web Storage in HTML5

What Is Web Storage?

With HTML5 web storage, websites can store data on a users local computer

Now No More Cookies concept in HTML5

We had to use Javascript cookies in the past to achieve this functionality.

How It Differs From Cookies

·         More secure

·         Faster

·         Stores a larger amount of data

·         The stored data is not sent with every server request. It only is included when asked for. This gives a big advantage over cookies

Detecting HTML5 Storage Support

function supports_html5_storage() {

       try {

          return 'localStorage' in window && window['localStorage'] !== null;

        } catch (e) { 

               return false;

        }

}

With Modernizr (Recommended):

if (Modernizr.localstorage) {

        // Local storage available

} else {

       // No local storage available

}

localStorage( ) & sessionStorage()

There are 2 types of web storage objects, local and session

Difference:

localStorage – Stores data with no expiration date

How It Works

sessionStorage – Stores data for one session

The localStorage and sessionStorage objects create a key = value pair

Example: key=“Name”, value=“Bob”

They are stored as strings but can be converted after if needed by using JS functions like parseInt() and parseFloat()

Syntax for Web Storage

1)   Storing a Value:

localStorage.setItem("key1", "value1");             object

localStorage["key1"] = "value1";                          array

2)   Getting a Value:

alert(localStorage.getItem("key1"));  

alert(localStorage["key1"]);  

3)   Remove a Value:

removeItem("key1");  

Remove All Values:

localStorage.clear();

Introduction to HTML5

Now we are going to see what is HTML5, what is the need of expanding HTML, what are the new features in HTML5 and  etc. concepts.

What is HTML5?

HTML5 will be new standard

HTML5 is a cooperation between the W3C (world wide web consortium) and (web hypertext application technology working group (WHATWG)

HTML5 Offers some great features that I will make the web more dynamic.

New Features:

·         New sets of tags such as <header> and <section>

·         <canvas> element for 2d drawing

·         Local storage ex cookies and small amount of data

·         New form control like calendar,date and time

·         New media functionality

·         Geolocation

Expanding HTML

One of the biggest reasons for HTML5 is to prevent users from needing to download and install multiple plugins such as Flash and Silverlight.

HTML5 Stack:

·         HTML5

·         CSS3

·         Javascript

New Form Elements

New Input types 

Ex: color, rage, seach, datatime, tel, datetime-local, time, email, url, month, week, number etc

·         Email:  input type allows the form to validate the email address format without using javascript

<input type=”email” name=”email”>

·         Color : Allows to you color from the picker

<input type=”color” name”favcolor”>

·         Date: Allows user to select a date

<input type=”date” name=”bDate”>

·         Range: Gives us a control for entering a number whose exact value is not important  (like  slider control)

<input type=”range” name “points” min=”1” ma=”10”>

·         Tel: Used to define a field for entering telephone number

<input type=”tel” name=”usrtel”>

·         Time: Used to define a field for entering a time

<input type=”time” name=”usr_time”>

·         URL: Used to define a field for entering a url/link

<input type=”url” name=”homepage”>

Datalist:

Specifies a list of predefined options for an <input> element.

Provides an “autocomplete” feature on <input> elements. Users will see drop-down list of pre-defined options as they input data.

Use the <input> element’s list attribute to bind it together with a <datalist> element.

<input list="browsers">

<datalist id="browsers">

                   <option value="Internet Explorer">

                   <option value="Firefox">

                   <option value="Chrome">

                   <option value="Opera">

                   <option value="Safari">

          </datalist>

<keygen>

·         Provides a secure way to communicate users.

·         The <keygen> tag specifies a key-pay generator field in a form.

·         When the form is submitted, two keys are generated, one private and another one public.

·         The private key is stored locally, and the public key is sent to server. The public key could be used to generate a client certificate to authenticate the user in the future.

<output>

Perform calculation and show the result in an <output> element:

Tip

What happens if you view a new HTML5 form input type in an older browser?

It reverts to a standard text input