Tips and Trick that Make It Easy to Deal with oData in CRM 2011/2013

Sometimes, in CRM project (well, I found it is many times, not just sometimes), we need to use oData to help the users do something or get the value automatically from related Master Data or another table (for example Global Setting), etc. Well, for some developers, C# can be their favorite and preference, but in fact, there are always requirements from client that force us to use oData.

When?

This list might be describe the real example why you need oData and I am focusing in the oData Query.

1. When you want to get some data from other entities (related or even not related) and you want to display it on the spot to users before server event.

2. You want to filter lookup field based on some entities data (you can use fetch XML), but somehow you don’t want to use complicated addCustomView() method, instead, you can use addCustomFilter().

See this link:

http://missdynamicscrm.blogspot.com/2014/08/crm-2013-using-addcustomfilter-to-get-filtered-lookup-field-based-on-linked-entity.html

3. When you want to perform basic operations, those are so-called CRUD that you want to be done using JavaScript.

To get started, you might refer to some articles:

http://msdn.microsoft.com/en-us/library/gg309549.aspx

http://crmbusiness.wordpress.com/2012/04/30/crm-2011-getting-started-with-odata/

So, if you found that your circumstances meet those examples, you might try to read this post.

Here are the tips and tricks from me that I got from my genuine experiences.

1. Stand by me, please, oData Query Designer.

Yes, you are definitely need this Tool.

It was designed to CRM 2011, but it will work as per expected for CRM 2013.

Then, what we do next after download this tool?

Well, you can use this tool to do Query, similar to what you perform using Advanced Find and it will give you the URL, then what’s next? Please see the tip number 2 and 3.

So, how to use this tool?

You can select the Entity that you want to retrieve the data.

It supports custom entity as well.

Then, you can do filtering using some filter criteria by attribute

Then, you can choose the columns that you want to select to display, including expanding from another entity.

Since the first page of the retrieved records using oData is limited to 50 records, you can do sorting as well.

*It might be useful if you want to get the only top 1, top 5, or etc. and if you want to display the record in order into a grid view, for instance.

And..What’s next?

You can generate to get the query, it is very simple.

You can also open with browser the URL to make sure that the data you expected was successfully retrieved.

Next action, see the tips number 2.

2. Choose your simple and elegant snippet.

I have been playing around oData Query and JavaScript for years and just find the most efficient code so far that I want to share to you.

- Download the CRM 2013 SDK

http://www.microsoft.com/en-us/download/details.aspx?id=40321

- Go to your Form Properties

- Include the two files in the form library.

json2.js and

SDK.REST.js

(you can find those files in the SDK that you’ve downloaded before)

And for your .js, this is the example.

I want to retrieve my own custom entity data Global Setting:

var retrievedGlobalSettingsRecords;

function onLoad() {
    retrieveGlobalSettings(100);
}

function retrieveGlobalSettings(number) {
    ///<summary>
    /// Retrieves Global Settings by passing a filter to the SDK.RestEndpointPaging.RetrieveRecords function
    ///</summary>
    var options = "$select=*&$filter=statecode/Value eq 0&$top=" + number;
    //The retrieveGlobalSettingsCallBack function is passed through as the successCallBack.
    SDK.REST.retrieveMultipleRecords("tfp_globalsetting", options, retrieveGlobalSettingsCallBack, function (error) { alert(error.message); }, globalSettingsRetrieveComplete);
}

function retrieveGlobalSettingsCallBack(retrievedGlobalSettings) {
    ///<summary>
    /// This function is used to call tfp_globalsettings returning records
    ///</summary>
    if (retrievedGlobalSettings.length > 0) {
        retrievedGlobalSettingsRecords = retrievedGlobalSettings;
    }
    else {
        alert("There is no Global Settings declared, please add this!");
    }

}
function globalSettingsRetrieveComplete() {
    ///<summary>
    /// This function is called after all the records have been returned to update the actual total number of records.
    ///</summary>


}

I explain the snippet.

In your .js file you can use this template, basically:

- In your code, break into 3 functions for oData:

  1). The function to call the oData Query

function retrieveGlobalSettings(number) {
    ///<summary>
    /// Retrieves Global Settings by passing a filter to the SDK.RestEndpointPaging.RetrieveRecords function
    ///</summary>
    var options = "$select=*&$filter=statecode/Value eq 0&$top=" + number;
    //The retrieveGlobalSettingsCallBack function is passed through as the successCallBack.
    SDK.REST.retrieveMultipleRecords("tfp_globalsetting", options, retrieveGlobalSettingsCallBack, function (error) { alert(error.message); }, globalSettingsRetrieveComplete);
}

* Important Notes:

If you used the reference to SDK.REST, you will notice that this will make your job easier.

Just focus on the query, you don’t need to care about how to call anymore since it will be the SDK.REST job

How you put the Query

var options = "$select=*&$filter=statecode/Value eq 0&$top=" + number;

How you define the entity you want to retrieve

SDK.REST.retrieveMultipleRecords("tfp_globalsetting", options, retrieveGlobalSettingsCallBack, function (error) { alert(error.message); }, globalSettingsRetrieveComplete);

- The CallBack function

function retrieveGlobalSettingsCallBack(retrievedGlobalSettings) {
    ///<summary>
    /// This function is used to call tfp_globalsettings returning records
    ///</summary>
    if (retrievedGlobalSettings.length > 0) {
        retrievedGlobalSettingsRecords = retrievedGlobalSettings;
        //set Rates
        setInterestRates(retrievedGlobalSettingsRecords);
    }
    else {
        alert("There is no Global Settings declared, please add this!");
    }
}

*Remember that the process of retrieving data through oData is gradually and not synchronous, it is asynchronously retrieved and if you want to call another function, you might place the function into the callBack function, for example:

if (retrievedGlobalSettings.length > 0) {
        retrievedGlobalSettingsRecords = retrievedGlobalSettings;
        //call another function
        setInterestRates(retrievedGlobalSettingsRecords);
    }

Complete function:

function retrieveGlobalSettingsCallBack(retrievedGlobalSettings) {
    ///<summary>
    /// This function is used to call tfp_globalsettings returning records
    ///</summary>
    if (retrievedGlobalSettings.length > 0) {
        retrievedGlobalSettingsRecords = retrievedGlobalSettings;
        //set Rates
        setInterestRates(retrievedGlobalSettingsRecords);
    }
    else {
        alert("There is no Global Settings declared, please add this!");
    }
}

- The onCompleted function:

function globalSettingsRetrieveComplete() {
    ///<summary>
    /// This function is called after all the records have been returned to update the actual total number of records.
    ///</summary>
}

This function I can say it is optional just in case you want to show the users the total retrieved record or display: Account was successfully retrieved or 1 Global Setting was found.

- The main global variable to store the retrieved record

var retrievedGlobalSettingsRecords;

- And of course The main function itself,

function onLoad(), for example, to call the function that will connecting using oData Query

function onLoad() {    
    retrieveGlobalSettings(100);
}

You can use another method, but I found that using this method it works very well and more efficient.

3. Identify and Learn the oData Operators.

You want to be oData Query master? You might know the operators you can use…

There is nothing makes sense you can earn without you learn.

Luckily, there is a MSDN article that you can read.

http://msdn.microsoft.com/en-us/library/gg309461.aspx

4. Debugging…

JavaScript is not like C# which you can have a good friend, Visual Studio that can help you to enlighten any typo error. JavaScript is not easy, it is complicated, and you got wrong in placing the wrong characters, it will make you dizzy after that.

Yes, the debugging is your best friend.

5. Rely on the URL once you get any unexpected error.

The only thing you can ensure your oData is success or not is by constructing the URL and open it into the browser, if you see the data then you can be happy, otherwise, it will give you an error as well.

Catch your error and when you get the pattern after debugging, you can copy paste and construct your own URL.

See this example:

I got this error message:

“Unrecognized 'Edm.DateTime' literal 'datetime'2014-05-12T04:5:28Z'' in '75'”If I pass the millisecond as well, it also show error.

You can debug then got the current URL.

Copy paste the URL to the browser and execute it.

And yes.. I found that it returned the error as well

So, after I played around with the URL and change the parameter, I copy paste to URL and finally I know the error was caused by parameter passed through URL.

After successfully retrieved from URL, I am confidence to use my query.

You can see another error found about oData in my blog:

oData error 404 not found

Expression of type 'System.Boolean' expected at position 0.

Date Time error: Unrecognized 'Edm.DateTime' literal

Which I use the URL tracing as the best way to solve it.

I found this method is very efficient since I was playing around and kept guessing what’s wrong with my code, then I was thinking to copy the URL to browser and yess..I got the same error, so I could understand easily the error is was from the oData URL, then I don’t need to spend the rest of my development time to examine the rest of my code.

You have the 6 and so on? Just let me know.

Thanks for your time reading my post and I will save my time to share you another post.

Hope this helps!

Thank you!

CRM 2011/ 2013 OData Error: Expression of type 'System.Boolean' expected at position 0.

When you do OData Query, somehow you can receive this following error:

“Expression of type 'System.Boolean' expected at position 0.”
Resolution:

Check your ODataQuery

$select=tfp_Color,tfp_extrafreecopiesrateId,tfp_Mono&$filter=tfp_ProductCategoryId/Id (guid'" + productCategoryId + "')

don’t forget to add the operator

$select=tfp_Color,tfp_extrafreecopiesrateId,tfp_Mono&$filter=tfp_ProductCategoryId/Id eq (guid'" + productCategoryId + "')

Try to access using your browser:

Before:



After:



Hope it helps!

CRM 2011/2013 OData Error: 404: Not Found:Resource not found for the segment

When you using SDK.Rest + OData Query, you can receive this following error:

Error:”404: Not Found:Resource not found for the segment ‘productSet’”



Cause:

It is because in ODataQuery, the name of entity and attribute are case sensitive.

Resolution:

Check in this code line:

SDK.REST.retrieveMultipleRecords("product", options, retrieveProductCallBack, function (error) { alert(error.message); }, productRetrieveComplete);

then change to:

SDK.REST.retrieveMultipleRecords("Product", options, retrieveProductCallBack, function (error) { alert(error.message); }, productRetrieveComplete);

Because when you see in the OData Query Designer:

It is ProductSet, it is not productSet


Hope it helps!

CRM 2011/ CRM 2013 oData Error When Passing Dynamic Date as Parameter : Unrecognized 'Edm.DateTime' literal 'datetime'2014-05-12T04:5:28Z'' in '75'

In this link, there is a guide to pass the date parameter to oData method.
http://msdn.microsoft.com/en-us/library/gg328025.aspx#BKMK_WorkingWithDates

Then this is the function

function getODataUTCDateFilter(date) {

 var monthString;
 var rawMonth = (date.getUTCMonth()+1).toString();
 if (rawMonth.length == 1) {
  monthString = "0" + rawMonth;
 }
 else
 { monthString = rawMonth; }

 var dateString;
 var rawDate = date.getUTCDate().toString();
 if (rawDate.length == 1) {
  dateString = "0" + rawDate;
 }
 else
 { dateString = rawDate; }


 var DateFilter = "datetime\'";
 DateFilter += date.getUTCFullYear() + "-";
 DateFilter += monthString + "-";
 DateFilter += dateString;
 DateFilter += "T" + date.getUTCHours() + ":";
 DateFilter += date.getUTCMinutes() + ":";
 DateFilter += date.getUTCSeconds() + ":";
 DateFilter += date.getUTCMilliseconds();
 DateFilter += "Z\'";
 return DateFilter;
}

Sometimes it works, but sometimes it does not. It depends on the time you send.
Like in my experience, I face this error :

“Unrecognized 'Edm.DateTime' literal 'datetime'2014-05-12T04:5:28Z'' in '75'”If I pass the millisecond as well, it also show error :


Because actually, after I try to access the URL that I build, yes, it result the error.




Here is my URL :

http://localhost:5555/dev/xrmservices/2011/OrganizationData.svc/tfp_rasdiscountrateSet?$select=tfp_DiscountRate,tfp_PeriodMonth,tfp_rasdiscountrateId&$filter=tfp_EffectiveFrom%20le%20datetime%272014-05-12T04:05:28Z%27%20and%20tfp_EffectiveTo%20ge%20datetime%272014-05-12T04:5:28Z%27%20and%20tfp_PeriodMonth%20eq%2024

Then, I try to another time, it is actually works, then I found out, actually it works if the hours is more than 1 character, for example : ‘2014-05-12T04:55:28Z’

Actually, CRM only accept this format :
YYYY-MM-ddTHH:mm:ssZ

CRM cannot accept this format :
YYYY-MM-ddTH:m:sZ

So, you have to have H, m, and s in two characters format.
And you also do not need to pass until millisecond, it will not work!

If it is 10:8:8, then have to change to 10:08:08
In my case, I have to convert to ‘2014-05-12T04:05:28Z’’2014-05-12T04:55:28Z’’2014-05-12T04:5:28Z’’

Let me try the URL

http://localhost:5555/dev/xrmservices/2011/OrganizationData.svc/tfp_rasdiscountrateSet?%20$select=tfp_DiscountRate,tfp_PeriodMonth,tfp_rasdiscountrateId&$filter=tfp_EffectiveFrom%20le%20datetime%272014-05-12T04:55:28Z%27%20and%20tfp_EffectiveTo%20ge%20datetime%272014-05-12T04:05:28Z%27%20and%20tfp_PeriodMonth%20eq%2024

And it works



Resolution :

Change your getODataUTCDateFilter() function to :

function getODataUTCDateFilter(date) {
    var monthString;
    var rawMonth = (date.getUTCMonth() + 1).toString();
    if (rawMonth.length == 1) {
        monthString = "0" + rawMonth;
    }
    else { monthString = rawMonth; }

    var dateString;
    var rawDate = date.getUTCDate().toString();
    if (rawDate.length == 1) {
        dateString = "0" + rawDate;
    }
    else { dateString = rawDate; }

    var hourString = date.getUTCHours().toString();
    if (hourString.length == 1)
        hourString = "0" + hourString;

    var minuteString = date.getUTCMinutes().toString();
    if (minuteString.length == 1)
        minuteString = "0" + minuteString;

    var secondString = date.getUTCSeconds().toString();
    if (secondString.length == 1)
        secondString = "0" + secondString;

    var DateFilter = "datetime'";
    DateFilter += date.getUTCFullYear() + "-";
    DateFilter += monthString + "-";
    DateFilter += dateString;
    DateFilter += "T" + hourString + ":";
    DateFilter += minuteString + ":";
    DateFilter += secondString + "Z'";
    return DateFilter;
}

Hope it helps!

CRM 2011/ CRM 2013 OData Datetime Passing Parameter

To pass the date as parameter in oData filter, you can use this format:
filter=tfp_EffectiveFrom le datetime'2014-05-12T04:05:28Z'

To pass the date as parameter to filter using oData to get the data in between certain Date, for example : StartDate= < dateParam <= EndDate

You can use this :

filter=tfp_EffectiveFrom le datetime'2014-05-12T04:05:28Z'
and tfp_EffectiveTo ge datetime'2014-05-12T04:05:28Z'

Which is '2014-05-12T04:05:28Z' is the Dynamics Parameter

Please refer to this link :

http://msdn.microsoft.com/en-us/library/gg328025.aspx#BKMK_WorkingWithDates