Query Examples

Basic Query

Query Options

• Select : Specify the array of fields you want returned - should use to improve performance
• FormattedValues: Indicate you want formatted values also returned with the results
• OrderBy : Array of fields to order by
• Filter : Standard OData filter syntax http://bit.ly/1Q6KEBh
• IncludeCount : Set to true if you want the count included in your results
• Top : Set the number of records to return e.g. I just want 500 would be Top:500
• Skip : Currently not supported by CRM and will result in an error if specified
• SystemQuery : allows you to specify a saved query ID to run e.g. SystemQuery:guid
• UserQuery : allows you to specify a user saved query ID to run e.g. UserQuery:guid
• FetchXml : specify unencoded fetch xml , the API will encode it for you
• Expand : specify an array of properties to expand - see example below
• TrackChanges : request change tracking on query, will result in TrackChangesLink being populated in results
• TrackChangesLink : query is a delta only query - should not be populated on first query

JavaScript Example

           
            var queryOptions = { Top:10 , 
                                 FormattedValues:true, 
                                 Select:['field1','field2'], 
                                 Filter:'field eq <value>',
                                 OrderBy:['name']};
            
            crmAPI.GetList("accounts",queryOptions).then (
                function (response){
                   
                    PopulateOutput(response);

                 }, 
                 function(error){});

Example using a CRM Web API Query Function

In this example we use a filter on BirthDate and are using the Between function to look for people born between the specified dates

           
       var queryOptions = { Top:10 , 
            FormattedValues:true, 
            Select:['field1','field2'], 
            Filter:'Microsoft.Dynamics.CRM.Between(PropertyName='birthdate',PropertyValues=["1979-01-01","1989-12-31"])',
            OrderBy:['name']};

Example using the Expand query option

In this example we use a Expand to retrieve the top 5 users of a business unit, selecting only specific properties from the user

   // JavaScript Example       
      var queryOptionsBU = {
      FormattedValues:true,
      Expand:[
        {Property:'business_unit_system_users', 
         Select:['systemuserid','fullname'], 
         Filter:'systemuserid ne ' + UserId, 
         OrderBy:['createdon asc'],
         Top:5
        }];     
      crmAPI.Get("businessunits",
               buresult.businessunitid.businessunitid,
                 queryOptionsBU).then(function (result)
           {
               console.log("retrieved business unit");
           });

//C# example
CRMGetListOptions buOptions = new CRMGetListOptions()
{
    Expand = new CRMExpandOptions[]
       { new CRMExpandOptions()
            { Property="business_unit_system_users",
              Select = new string[] { "systemuserid","fullname" },
              Filter = "systemuserid ne " + whoamiResults.UserId,
              OrderBy = new string[] {"createdon asc"},
              Top=5

            }
       }
};
var buResult = await api.Get("businessunits",
     Guid.Parse(userResults.businessunitid.businessunitid),
      QueryOptions: buOptions);

Example using Entity Change Tracking

In this example the query is performed with TrackChanges=true. This requires that Entity Change Tracking is enabled for the entity that is being queried. The first GetList will return all records plus a TrackChangesLink in the results. That TrackChangesLink must be passed on the next GetList which will return only the changed records

//C# example
var results = await api.GetList("accounts", 
                   new Requests.CRMGetListOptions() { TrackChanges=true, FormattedValues = true });

 results = await api.GetList("accounts", 
            new Requests.CRMGetListOptions() { TrackChanges = true, 
                TrackChangesLink=results.TrackChangesLink, FormattedValues = true });