Dynamic CRM - Fetch XML with No Lock

In this post, we will review, how to write a Fetch XML with No lock implemented. This feature will enhance the execution of SQL and will be safer to be run in any environment(s).  Lets review a sample SQL with no lock attribute and will follow with an example on how to using Fetch XML.


SELECT * FROM Employee WITH (NOLOCK);

The above can be implemented using Fetch XML as :

Happy coding...

Dynamic CRM - Fetch XML with NULL operator

In this post, will review how to handle NULL condition within Fetch XML.

As an example, lets review a basic SQL with a NULL condition in WHERE clause and how to execute this statement using Fetch XML.

SELECT EmpId FROM Employee WHERE DeptId = NULL;

Lets review the possibility to execute this SQL using Fetch XML.

Happy Coding....

Dynamic CRM - To retrieve all attributes using Fetch XML in CRM

In this post, will review the Fetch XML script to retrieve all attributes within a given entity.

As an example, lets consider the sample SQL:

SELECT * FROM Employee;

Above SQL, when converted to Fetch XML, will be :

Happy coding....

Dyanmic CRM - Retrieve multiple values with IN operator in Fetch XML.

In this post, will review the need for multiple values in IN operator and how to execute them.

In plain SQL, it is pretty simple and easy to handle IN operator with multiple values, but doing in Fetch XML can be simple too, with few extra steps. I will list two different options to achieve the results.

First, lets review the plain SQL, that needs to be converted.

SELECT EmployeeId, EmployeeName FROM Employee WHERE DeptId IN(1,2,3,4,5). 

Now lets see, how we can implement the same with Fetch XML. First option will be to hard code the values of IN operator.

Now lets review, the other option to include multiple values within the condition for IN operator.

I hope this provides some insight into using IN operator within Fetch XML.

Happy coding....

Dynamic CRM - Unable to get property 'value' of undefined or null reference.

In this post, I would like to talk about this error while executing a Fetch Xml through Xrm Service Toolkit (JavaScript).

There was an error with this field's customized event.
Field: window
Event: onload
Error: Unable to get property 'value' of undefined or null reference.

The script where the error occured is :

var resultset = XrmServiceToolkit.Soap.Fetch(strFetchXml);

Generally with the research it involved, I found various possibilities that could result in this error message (mainly being missing field in a given form / entity), but that was not the case for me. 

 After spending huge amount of time, it was found that the resultset after executing Fetch XML was null and this error message was part of Xrm Service Toolkit for handling returned data. 

To make it more clear, I added a try / catch for the above statement and added a not null condition for resultset.

if(resultset != null)
{
}

Above implemented action(s) made sure, it didn't throw any error messages and handled properly.

Happy coding....

Dynamic CRM - Ways to retrieve data from database.

In this post, lets go over two different ways to retrieve data from SQL in Dynamics CRM.

Why do we need data:

    Though within CRM, it is easy to review data created towards any specific entity, but getting a customized view to get only specific/complex details as a report/page to senior management might be an option to get started with.

   As an example, lets take a SQL (sample) and try to implement them using direct SQL Fetch and FetchXml options.

SQL for example:

SELECT EmpID, EmpName FROM Employee WHERE EmpID = 101;

SQL Fetch:

This process involves using regular SqlConnection object and respective parameters to fetch data from the table directly. The above sql will be directly passed in as a parameter to Command objects text value. Using a DataReader, retrieved data can be looped through to analyze and handle accordingly;

 Advantage: Exclusive access to any table / entity. Any possible combination of data can be retrieved.

Disadvantage: Works primarily with on-prem versions only. When migrating to Online version, these procedures / SQL's will not be effective.

  To support the online version, we can use Fetch XML to handle them effectively.

FetchXml:

Now lets retrieve the same using Fetch XML.

string fxml = @"<fetch mapping='logical'>
<entity name='employee'>
   <attribute name='empid'/>
   <attribute name='empname'/>
   <filter type='and'>
   <condition attribute='empid' operator='eq' value='100'/></filter>
  </entity>
</fetch>"

Using the above string as input, Employee Entity can be retrieved using  serviceproxy (OrganizationServiceProxy)

Note :

1. Entity and column names should be lowercase.
2. Multiple conditions can be included (similar to where clause).

To get started, you can use this website http://sql2fetchxml.com/.

You can enter your SQL and in turn it will derive fetch xml for the provided SQL. Again this website has its own limitations

To know more on what will be supported, visit this page: http://sql2fetchxml.com/Help.aspx. But good enough for a starter to get started..

Happy coding...

XRM Page - getId() returning null or empty string

In this blog, I would like to indicate the usage of Xrm.Page.data.entity.getId() and its responses in various CRM versions and how it affects. Also what we can do to handle appropriately without having exceptions.

The return value for Xrm.Page.data.entity.getId() will return NULL, when called from a Create form (specific to Dynamics CRM version 2011)

The return value for Xrm.Page.data.entity.getId() will return EMPTY STRING, when called from a Create form (specific to Dynamics CRM version 2013).

To handle this given situation in any version (2011,2013 or 2015), you could implement the following code:

 var FormType = Xrm.Page.ui.getFormType();

 if(FormType != 1) //Refers to update form or other Form statuses.

  {

     var myId = Xrm.Page.data.entity.getId();

  }

Happy coding....