Tag Archives: OData

UPDATED SAMPLE: CRM 2011 Windows Store App OData helper

I worked with a partner, EastBanc Technologies, who used the approach I blogged about here and here to build a Windows Store App for a Dynamics CRM solution.  The folks at EastBanc Technologies found some bugs.  We worked together to fix the bugs and they were gracious enough to let me share their changes.  I’ve uploaded the updated sample here:

Visual Studio 2012 Source Code: http://sdrv.ms/S3MND1

CRM Unmanaged Solution: http://sdrv.ms/RYzVzj

You will need the WCF Data Services Tools for Windows Store Apps and Visual Studio 2012 RTM.

If you’ve followed the two previous posts, you’ll notice that I uploaded my samples to new location on my SkyDrive.  The reason is that due to the final terminology for Windows Store Apps, I needed to refactor the solution/project files and class names.  While I plan on updating the walkthrough post when I have a spare moment, I wanted to get the changes published.  Hopefully, the naming changes are obvious enough for folks until I get that post updated.

@devkeydet

UPDATED SAMPLE: CRM 2011 Metro style OData helper

I’ve updated the sample I started here by implementing Create Update and Delete methods.  The library is still not complete or well tested, but it is at least a head start to building a CRUD capable Windows 8 Metro style application using the CRM 2011 OData (aka Organization Data) service.  Both the helper web resource and the Windows 8 Metro style source code have been updated.  The Visual Studio solution now has a little sample Metro style app to quickly test out the functionality of the library.

http://sdrv.ms/NIiMY2

To get the sample to work, you will need to import the updated CRM solution package which updates the helper web resource. Also, make sure you change the CRM organization url in the sample app.  You can still follow the instructions from the first post since there are no breaking changes in the sample, just new functionality.

@devkeydet

CRM 2011, OData and datajs

UPDATE: FALSE ALARM about batch updates.  Turns out the CRM 2011 OData service doesn’t support batch updatesSad smile.  I just assumed it did because I’ve written .NET/Silverlight clients against the CRM 2011 OData service using a derived DataServiceContext.  DataServiceContext based contexts using the “unit of work” pattern through SaveChanges()/BeginSaveChangesc() and they’ve always “just worked.”  I just confirmed that Batch isn’t the default SaveChangesOption so all this was predicated on a bad assumption on my partSad smile.  Nothing to see here.

What people often call the CRM 2011 REST Service or Organization Data Service is an OData service.  If you go to the libraries section of the OData site and select JavaScript, you will be linked to the datajs CodePlex site.  So what about the CRM SDK OData samples (SDK.REST.js and SDK.JQuery.js)?  It’s probably one of those “six in one hand, half dozen in the other” situations in terms of the shared capabilities of the libraries.  Which API you like better probably boils down to a style preference.  There are some differences in capabilities as well.  The CRM SDK libraries have explicit functions to associate / disassociate records.  One of the things the CRM SDK sample libraries don’t offer is a facility to do batch updates, but datajs does.  This comes in really handy when you want to do a bunch of work client side to some data and send it all at once.  Have a look at the OData Code Snippets.  They also have a full OData API reference.

@devkeydet

Using the CRM 2011 OData service from a Metro style app

UPDATE: Fixed a few bugs in the downloadable sample including one where it wouldn’t work in Office 365 based CRM Online subscriptions.  Edited some text in the post for clarity.

UPDATE2: Added some text about how to get this working for Windows Authentication.

UPDATE3: I’ve just blogged about an update to the sample here.  The updates in the code might make following this post a little confusing.  The reason is that due to the final terminology for Windows Store Apps, I needed to refactor the solution/project files and class names.  While I plan on updating this post soon, I wanted to get the changes published.  Hopefully, the naming changes are obvious enough for folks until I update this post. 

Scenario:

“I want to build a Windows 8 Metro style application.  Since my app is going to just perform CRUD operations, I want to use the OData service using json so that I have the least amount of ‘data across the wire’ possible.”

DISCLAIMER: This a pretty hacky approach, but it works and is the only way I know of to do it.  If you like it, use it.  If not, wait until there’s an official sample in the SDK.  You’ve been warned.  I’m just a guy getting creative to solve a problemSmile.

First I will walk you through how to do it.  Then I will explain how it works.  To get started, download and import the following solution into you CRM 2011 organization:

http://sdrv.ms/S3MND1

Next, download the source code of my sample library:

http://sdrv.ms/RYzVzj

I say sample library very loosely.  This is FAR from being a complete, well tested library.  I have good intentions to make it one, but wanted to get a sample of the basic plumbing working for those of you who have asked me how to do it.  This sample should be enough to get you started, but you’ll have to finish out the rest of the functionality.  If anyone wants to take this sample and turn it into an open source project, YOU HAVE MY FULL PERMISSIONSmile.

USING THE SAMPLE

Create a new Metro style app.  I am going to use the Blank App template to keep things simple:

image

Add my sample library to the solution:

image

Don’t forget to right-click References nod of the Crm2011MetroStyleODataApp in Solution Explorer and a reference to the project you just added:

image

image

Find the Devkeydet.CrmMetroODataHelper.csproj file and add it.  Replace the default Grid in MainPage.xaml with the following:

<Grid x:Name="LayoutRoot" Background="{StaticResource ApplicationPageBackgroundThemeBrush}">

    <StackPanel x:Name="MainUIStackPanel" HorizontalAlignment="Center" VerticalAlignment="Center" Visibility="Collapsed">

        <TextBlock x:Name="myTextBlock" Text="TextBlock" TextAlignment="Center" />

        <Button x:Name="queryButton" Content="Button" />

    </StackPanel>

</Grid>

In MainPage.xaml.cs, paste the following code into the OnNavigatedTo method:

var oDataHelperWebResource = "https://devkeydet.crm.dynamics.com/WebResources/dkdt_/MetroODataHelper.htm";

 

if (!CrmMetroODataHelper.IsLoggedIn)

{

    CrmMetroODataHelper.SignIn(oDataHelperWebResource, LayoutRoot);

    CrmMetroODataHelper.SignInComplete = () => MainUIStackPanel.Visibility = Visibility.Visible;

}

Make sure to replace “https://devkeydet.crm.dynamics.com” with the right url for you.  You will have to add a using Devkeydet statement to the file.  Ok, now it’s time to “Add Service Reference” to the OData service.  Unless you are using Windows Authentication, you can’t point the “Add Service Reference” dialog to the data service url.  Instead, you need to download it and point to it locally, from Visual Studio:

image

image

Save it somewhere and point, then “Add Service Reference” by pointing Visual Studio to the file:

image

Add the following method to MainPage.xaml.cs:

private  async void ExecuteODataQueryAsync()

{

    var ctx =

        new devkeydetContext(new Uri("https://devkeydet.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/"));

 

    // Use the generated DataServiceContext to compose the query using LINQ, 

    // but get the string of the query via ToString().

    // We still get to use the query composition capabilities of LINQ!

    var query = from a in ctx.AccountSet

                where a.Name.StartsWith("A")

                select new Account

                {

                    Name = a.Name

                };

 

    var accounts = await CrmMetroODataHelper.RetrieveMultipleRecordsAsync<Account>(query.ToString());

 

    myTextBlock.Text = accounts.First().Name;

}

Again, remember to replace “https://devkeydet.crm.dynamics.com” with the right url for you.  You will have to add a using statement based on the Namespace you gave the generated code and devkeydetContext will need to be replaced with your generated context name.  Now, wire up an event handler for the button (ex: double click the button on the design surface) and call the method:

ExecuteODataQueryAsync();

If your app is using Windows Authentication, then you need to double-click on the Package.appxmanifest and make sure you have the following Capabilities checked:

image

Go ahead and run the app.  You will be prompted for your credentials if necessary:

image

Sign in with your credentials, click the button, and the TextBlock should have the value of the Name property of the first result:

image

There you go!  You just issued an OData query from a C# Metro style app!

HOW THE SAMPLE WORKS

The sample wraps the WebView control.  The WebView control tries to load an html web resource from the CRM server (that’s what’s in the devkeydetMetroODataHelper_1_0.zip solution).  If you haven’t logged in, then the WebView is set to be visible and it will just present you with the web based login UI.  Once logged in, the WebView visibility is collapsed and the C# wrapper basically proxies OData calls through WebView via the web resource using a modified version of the SDK.REST.js file from the CRM 2011 SDK.  The major modification is that the default SDK.REST.js sample will turn the resulting json string into an JavaScript object.  Instead, I just pass the json string back to the C# wrapper and use JSON.NET to turn it into C# objects.

As I mentioned earlier, this is FAR from being a complete and reusable library.  I have good intentions to make it one, but wanted to get a sample of the basic plumbing working for those of you who have asked me how to do it.  This sample should be enough to get you started, but you’ll have to finish out the rest of the functionality.  I only implemented executing a query that returns multiple results.  Furthermore, I didn’t handle continuation tokens.  If anyone wants to take this sample and turn it into an open source project, YOU HAVE MY FULL PERMISSIONSmile.

@devkeydet

SAMPLE: Editable grid for CRM 2011 using jqGrid

A while back, I wrote a post called Building an editable grid for CRM 2011.   The goal of the post was to give you the necessary knowledge to know how to write the code to build an editable grid.  Since then, someone introduced me to jqGrid.  This is quite a powerful JavaScript grid control.  Have a look at the site to see all the things you can do with it.

I’ve built a little sample for a customer using jqGrid.  I wanted to spend some time cleaning the code up and making it more reusable before I blogged about it.  My lofty goal was for it to be a fully reusable editable grid control for CRM.  My thinking was that all you would have to do is drop a web resource on the form, pass the web resource a set of parameters (child entity, columns to display, etc.), and the web resource would render a custom grid based on what you passed it.  Well, as most of my lofty goals for side projects go, I haven’t found the time to get it where I want it to be.  Since then I’ve promised a number of folks to at least share what I have until I can get around to turning this into what I want it to be. 

To get started, download the sample from here.  Unzip the contents somewhere.  If you want to open the Visual Studio solution, you will need the Developer Toolkit for Microsoft Dynamics CRM installed.   Make sure you read the README.txt file in the root of the CrmEditableGrid project.  As I say in the readme:

THIS IS NOT A FULLY REUSABLE EDITABLE GRID CONTROL.  IT IS A SAMPLE DEMONSTRATING HOW
TO USE THE FACILITIES OF THE CRM 2011 SDK COMBINED WITH A POPULAR JAVASCRIPT GRID CONTROL (jqGrid).
WITH THIS AS YOUR STARTING POINT, YOU SHOULD BE ABLE TO USE THE CRM 2011 SDK AND THE jqGrid
DOCUMENTATION TO ADD ADDITIONAL CAPABILITIES TO THE GRID SUCH AS: ADD/DELETE ROW, OPTION SETS, ETC.

To get the sample working, import the dkdteditablegrid.zip file into your CRM organization.  It’s an unmanaged solution.  Make sure you publish all customizations at the end of the import.  I didn’t export the sitemap so you will need to find the Parent entity and add it to the Workplace area to see it:

image

Make sure you publish and refresh the browser so the updates show up.  Ok, I warned you that this sample was incomplete.  To get the editable grid into a useable state, you need to create a Parent record and a couple related Child records.  Navigate to Workplace->Extensions->Parents and create a new parent.  Make sure you Save (not Save & Close).

image

Now navigate to Related->Common->Children and add a couple children.

image

Once you’ve added a couple children.  Close the form and open it again.  Like I said, work in progress.  What you’ll see is a grid on the form that looks something like this:

image

If you click the “e” button, then you’ll put the record in edit mode.  The “c” button cancels and the “s” button saves the record.  The way this works is as follows:

  • The grid is an html web resource that is passed the record id from the parent form
  • When the web resource loads, it makes an OData query for the children
  • Upon query completion, it wires up the data to the jqGrid
  • When “s” button is clicked, the code updates the child record through the OData service

The goal of this sample, for now, is to have enough of the core plumbing in place for you to take and elaborate to your specific scenario.  Once you realize how capable jqGrid is, then you’ll understand that it’s not that big of a leap forward to get to where you want to be.  I still plan to transform this from a code sample to something people can just pickup and use on many forms by passing a few parameters to the web resource.  Of course, time needs to be on my side…yes it does.  Let me know if you beat me to it! 

@devkeydet

Displaying a lookup as a dropdown in a CRM 2011 form

UPDATE (04JUN2013):  If you need to evolve this sample to support a one to many relationship represented as a multi-select, I like this multi-select control which can be themed with jQuery UI themes.

Another scenario from a customer:

We want to use a lookup to another entity because we want to store additional data in the  entities fields and option sets don’t provide this capability.  However, we want the selection of the entity in the form to be a dropdown to minimize the # of clicks a user must go through to select the entity.

HTML / JavaScript web resources and the OData service to the rescue!  First, this sample was loosely inspired by this post from my teammate Carlton Colter.  I took his idea, ran with it, applied it to this problem, and made my solution a reusable web resource that you can pass parameters to in order to apply it to any lookup on a form.  Here are the steps…

Add the lookup field to the form:

image

Notice that this lookup is required.  This solution respects the required metadata of the field and uses it to ensure the dropdown has a value before the form is saved.  Set the properties of the lookup making sure the Visible by default option is unchecked:

image

Insert an html web resource to the form with the following General settings configured (the code for the web resource is available further down):

image

The Custom Parameter(data) property is what makes this reusable.  You can figure out what needs to be passed in by looking at the JavaScript, but here is the comma separated format:

lookupEntitySetName,lookupEntityIdFieldName,lookupEntityDisplayFieldName,nameOfLookupFieldOnForm,webResourceName

Here is a longer description if the variable names aren’t clear enough:

lookupEntitySetName = Name of the entity set in the OData service for the entity.

lookupEntityIdFieldName = The id/primary key for the in the OData service.

lookupEntityDisplayFieldName = The field name from the entity that you want to display in the dropdown.

nameOfLookupFieldOnForm = The name of the actual lookup field that is hidden on the form.

webResourceName = The name of the web resource on the form.  This is set in the Add Web Resource dialog and always starts with WebResource_.

Make sure you set the properties on the Formatting tab like so:

image

Finally, there is a little bug that I gave up on.  So you will need to put a spacer next to or below the web resource:

image

This will prevent the dropdown for getting cut off if there is nothing to the right or bottom of it.  If it already has something to the right of it or below it, then this is unnecessary.  Bingo bango!  You now have a lookup that’s represented on the form as a dropdown complete with required validation:

image 

The dropdown is just UI trickery.  When you save the form, the value of the lookup is saved correctly.  In update scenarios, the form loads, reads the hidden lookup value, and sets the dropdown selected item appropriately.

Here’s the code for the html web resource:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>

    <head>

        <title></title>

        <script src="../ClientGlobalContext.js.aspx" type="text/javascript"></script>

        <script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.7.1.min.js" type="text/javascript"/>

        <script src="scripts/dropdownforlookup.htm.js" type="text/javascript"/>

 

        <!-- TOOD: move the needed styles into our own stylesheet. The CRM team doesn't support referencing their styles because they can change -->

        <link href="/_common/styles/fonts.css.aspx?lcid=1033" rel="stylesheet" type="text/css"/>  

        <link href="/_common/styles/global.css.aspx?lcid=1033" rel="stylesheet" type="text/css"/>  

        <link href="/_common/styles/select.css.aspx?lcid=1033" rel="stylesheet" type="text/css"/>  

    </head>

    <body style="background-color: rgb(246, 248, 250); border-width: 0px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-left: 0px; padding-top: 0px;">

        <!-- TOOD: move the needed styles into our own stylesheet. The CRM team doesn't support referencing their style sheets because they can change -->

        <select id="lookupdropdown" class="ms-crm-SelectBox">

            <option value="Loading..." selected="selected">Loading...</option>

        </select>

    </body>

</html>

Here’s the code for the dropdown.htm.js file that it references:

$(document).ready(function () {

    var context = GetGlobalContext();

    var params = context.getQueryStringParameters();

    // The data passed to the web resource should be in the following format:

    //

    // lookupEntityName,lookupEntityIdFieldName,lookupEntityDisplayFieldName,nameOfLookupFieldOnForm,webResourceName

    //

    var dataParam = params.data.split(",");

    var lookupEntitySetName = dataParam[0];

    var lookupEntityIdFieldName = dataParam[1];

    var lookupEntityDisplayFieldName = dataParam[2];

    var nameOfLookupFieldOnForm = dataParam[3];

    var webResourceName = dataParam[4];

 

    var oDataQuery = context.getServerUrl() + "/XRMServices/2011/OrganizationData.svc" +

    "/" + lookupEntitySetName + "?$select=" + lookupEntityIdFieldName + "," + lookupEntityDisplayFieldName;

 

    var lookupAttribute = window.parent.Xrm.Page.getAttribute(nameOfLookupFieldOnForm);

    var lookupArray = lookupAttribute.getValue();

 

    $.getJSON(oDataQuery, function (data) {

        var results = data.d.results;

        var lookupdropdown = $("#lookupdropdown");

 

        for (var i = 0; i < results.length; i++) {

            var option = "<option value='" + results[i][lookupEntityIdFieldName];

            option += "'>" + results[i][lookupEntityDisplayFieldName] + "</option>";

            lookupdropdown.append(option);

        }

 

        lookupdropdown.prepend("<option value='blank'></option>");

 

        if (lookupArray) {

            var lookupId = lookupArray[0].id.replace("{", "").replace("}", "").toLowerCase();

            lookupdropdown.val(lookupId);

        } else {

            lookupdropdown.val("blank");

        }

 

        $("#lookupdropdown option[value='Loading...']").remove();

 

        lookupdropdown.width("100%"); // couldn't figure out what was causing it to not stretch so I just forced it

 

        lookupdropdown.change(function () {

            var name = $("#lookupdropdown option:selected").text();

            if (name == "") {

                SetLookupValueToNull(nameOfLookupFieldOnForm);

            } else {

                var entityType = results[0].__metadata.type.replace("Microsoft.Crm.Sdk.Data.Services.", "");

                SetLookupValue(nameOfLookupFieldOnForm, lookupdropdown.val(),

                name, entityType);

            }

        });

    });

 

    var requiredLevel = lookupAttribute.getRequiredLevel();

 

    if (requiredLevel == "required") {

        // We want to drive whether our dropdown requires a value by the hidden lookup fields metadata

        // However, if we keep the lookup field's requirement level to required the form validation

        // will tell us it is required an force us to show it.

        lookupAttribute.setRequiredLevel("none");

 

        //NOTE: Modifying html that the CRM server sends down to the browser is not supported.

        // However, here we are finding a label for this control and updating its text.

        // This is relatively harmless and the likelihood of the CRM representing a label

        // for a control as anything other than an html label control is low. So I feel safe

        // enough to break the "don't touch the CRM html" rule. Even if it becomes an issue

        // at upgrade, we have one place where we can make the adjustments

        var label = $("label[for='" + webResourceName + "']", window.parent.document);

 

        // TODO: Improve this so it isn't tightly coupled to CRM generated html/css. Using undocumented css and server urls is unsupported.

        label.append("<IMG class=ms-crm-ImageStrip-frm_required alt=Required src='/_imgs/imagestrips/transparent_spacer.gif?ver=600790655'>");

 

        // TODO: add onsave to display an error message.

        window.parent.Xrm.Page.data.entity.addOnSave(function (context) {

            var name = $("#lookupdropdown option:selected").text();

            if (name == "") {

                var labelForWebResource = window.parent.Xrm.Page.getControl(webResourceName).getLabel();

                alert("You must provide a value for " + labelForWebResource + ".");

                context.getEventArgs().preventDefault();

            }

        });

    }

});

 

function SetLookupValue(fieldName, id, name, entityType) {

    if (fieldName != null) {

        var lookupValue = new Array();

        lookupValue[0] = new Object();

        lookupValue[0].id = id;

        lookupValue[0].name = name;

        lookupValue[0].entityType = entityType;

 

        window.parent.Xrm.Page.getAttribute(fieldName).setValue(lookupValue);

    }

}

 

function SetLookupValueToNull(fieldName) {

    if (fieldName != null) {

        window.parent.Xrm.Page.getAttribute(fieldName).setValue(null);

    }

}

@devkeydet

Adding an Image Carousel to a CRM 2011 Form

I recently had a customer request to display the pictures/images attached to a CRM form in an image carousel.  This is one of those things where I’ve seen people write a bunch of Silverlight code to accomplish.  In my opinion, using Silverlight for this is overkill.  Inspired by the second half of the Displaying a Contact’s Facebook Picture in Microsoft Dynamics CRM 2011 post, I modified the OData query slightly to get back all the images attached to an entity and then used jCarousel Lite in conjunction with an HTML web resource. 

ImageCarousel.htm:

<html>

<head>

    <title></title>

</head>

<body style="background-color: rgb(246, 248, 250); border-width: 0px; margin-bottom: 0px;

    margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-left: 0px; padding-top: 0px;">

    <table>

        <tr>

            <td>

                <button id="prev"><<</button>

            </td>

            <td>

                <div id="carousel">

                    <ul id="carousel-ul">

                    </ul>

                </div>

            </td>

            <td>

                <button id="next">>></button>

            </td>

        </tr>

    </table>

</body>

<script src="../ClientGlobalContext.js.aspx" type="text/javascript"></script>
   1:  

   2: http://span

   2: http://span

   2: <script src="scripts/ImageCarousel.htm.js" type="text/javascript">

</script>

</html>

ImageCarousel.htm.js:

$(document).ready(function () {    

    var context = GetGlobalContext();     

    

    // Get the entity id    

    var entityId = context.getQueryStringParameters().id;     

 

    if (entityId) {        

    

        // Build the OData query to get all the images attached to the current entity        

        var oDataQuery = context.getServerUrl() + "/XRMServices/2011/OrganizationData.svc" +            

        "/AnnotationSet?$select=AnnotationId,Subject,DocumentBody,MimeType&" +            

        "$orderby=ModifiedOn desc&$filter=ObjectId/Id eq guid'" + entityId +            

        "' and IsDocument eq true and startswith(MimeType,'image/') ";

        

        // Get a reference to the carousel ul        

        var carouselul = $("#carousel-ul");         

 

        // Execute the OData query for the images        

        $.getJSON(oDataQuery, function (data) {            

            var results = data.d.results;                        

            

            // Add the results to the carousel ul            

            for (var i = 0; i < results.length; i++) {                

                // Build up an img tag surrounded by a li tag to add to the carousel ul                

                var img = "<li><img src='";                                

 

                // set src attribute of default profile picture web resource.                                    

                // here we use DataURI schema which has 32kb limit in IE8 and doesn't support IE <= 7.                                    

                var src = "data:" + results[i].MimeType + ";base64," + results[i].DocumentBody;                                

 

                img += src;                

                img += "' width='100' height='100' style='border: 1px solid; margin: 1px;' alt='";

                img += results[i].Subject + "'></li>";                                

 

                // Add the li with imag inside to the carousel ul                

                carouselul.append(img);

            }

 

            // Wire-up jCarouselLite to the carousel div            

            $("#carousel").jCarouselLite({

                btnNext: "#next",

                btnPrev: "#prev"

            });

        });

    } else {

        alert("could not get entity id");    

}});

The end result is what you see below:

Capture

We can rotate through all the images attached to this Contact entity by clicking the previous / next buttons.  This code will work with any entity that has Notes enabled.

@devkeydet