Archive by Author | BusiPlanITCTO

MS Dynamics 2016 new Web API: So, what do we do now?

As you may already know, MS Dynamics CRM 2016 comes with a new API for web development, and even a new endpoint has been implemented. So, for all of us who had developed resources using the old API, does that mean that we need to rush into a massive refactoring crusade? Well, not for the moment since the old (CRM2011) endpoint is still available, and hopefully will be supported for the near future.

This article won’t be a summary of the new features in the new API, you can find plenty of material on the topic. A good starter can be found here. However, I wanted to provide a side to side comparison of the main syntactical differences between the new and old APIs, for which I have created a very simple web-resource. As a bonus, I made this web-resource backwards-compatible which proves useful if you have to maintain the same code-base for multiple versions of Dynamics CRM.

The sample shown next is for a very simple HTML web resource that displays all the active system views in the organization, sorted in alphabetical order.

Differences

  1. As I mentioned before, the endpoints are different; however, the server url remains the same.
    Old Suffix: "/XRMServices/2011/OrganizationData.svc/"
    New Suffix: "/api/data/v8.0/"
  2. Entity and field names no longer need to match the exact casing defined in the schema names. Also, for entities we no longer need to use a combination “Entity”+”Set”, we just need to use the plural name for the entity.
    Old Entity Format: SavedQuerySet
    New Entity Format: savedqueries
  3. The syntax for filters on complex data types like EntityReference or OptionSet is different.
    Old format: filter=StateCode/Value eq 0
    New format: filter=statecode eq 0
  4. The old page size is 50, whereas, the Web API allows you to set the page size, which in turn can improve performance by reducing the number of trips to SQL server.
    New syntax for setting page size: setRequestHeader("Prefer", "odata.maxpagesize=100")
  5. The syntax to verify whether there’s more data to fetch is also slightly different.
    Old syntax: __next != null
    New syntax: @odata.nextLink != null

Final thoughts

If you are implementing web resources for Dynamics CRM 2016, you can still use the old API; however, it’s definitely a good idea to start using the new web API since the functionality offered is richer and brings the opportunity to develop components that perform faster.

HTML markup:

<html>
<head>
 <title>Sample: Show All System Views</title>
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
 < script src="ClientGlobalContext.js.aspx"></script>
 < script src="bpi_testwebapi.js" type="text/javascript"></script>
 <meta>
 <meta>
</head>
<body style="word-wrap: break-word;">
 < div style="margin-right: 8px; margin-left: 13px;">
 <ol id="systemViews">
 </ol>
 </div>
 < script type="text/javascript">Setup();</script>
</body>
</html>

Supporting JavaScript:

function Setup() {
 var filterExpression = "savedqueries?$select=name,savedqueryid&$filter=statecode eq 0&$orderby=name asc";
 var url = getClientUrl() + "/api/data/v8.0/";
 var qry = url + filterExpression;
 RetieveData(qry);
}

function Setup_Pre2016() {
 var filterExpression = "SavedQuerySet?$select=Name,SavedQueryId&$filter=StateCode/Value eq 0&$orderby=Name asc";
 var url = getClientUrl() + "/XRMServices/2011/OrganizationData.svc/";
 var qry = url + filterExpression;
 RetieveData_Pre2016(qry);

}

function RetieveData(url) {
 var req = new XMLHttpRequest()
 req.open("GET", encodeURI(url), true);
 req.setRequestHeader("Accept", "application/json");
 req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
 req.setRequestHeader("OData-MaxVersion", "4.0");
 req.setRequestHeader("OData-Version", "4.0");
 req.setRequestHeader("Prefer", "odata.include-annotations=\"OData.Community.Display.V1.FormattedValue\"");
 req.setRequestHeader("Prefer", "odata.maxpagesize=100");
 req.onreadystatechange = function () {
 if (this.readyState == 4 /* complete */) {
 req.onreadystatechange = null;
 switch (this.status) {
 case 200:
 var result = JSON.parse(this.response);
 // browse thru all results and build html
 var values = result.value;// type array 

 for (i = 0; i < values.length; i++) {
 addNewView(values[i].name);
 }
 if (result["@odata.nextLink"] != null) {
 //
 RetieveData(decodeURI(result["@odata.nextLink"]));
 }
 break;
 case 503:
 // service not available - we need to call the pre-2016 endpoint
 Setup_Pre2016();
 break;
 default:
 var error = JSON.parse(this.response).error;
 alert(error.message);
 break;
 }

 }
 };
 req.send();
}

function RetieveData_Pre2016(url) {
 var retrieveReq = new XMLHttpRequest();
 retrieveReq.open("GET", url, false);
 retrieveReq.setRequestHeader("Accept", "application/json");
 retrieveReq.setRequestHeader("Content-Type", "application/json; charset=utf-8");
 retrieveReq.onreadystatechange = function () {
 if (retrieveReq.readyState == 4) {
 // complete
 //
 var retrieved = JSON.parse(retrieveReq.responseText).d;
 //
 for (var i = 0; i < retrieved.results.length; i++) {

 addNewView(retrieved.results[i]["Name"]);
 
 }
 // Check if there are more records to fetch
 if (retrieved.__next != null) {
 RetieveData_Pre2016(retrieved.__next)
 }
 }
 };
 retrieveReq.send();
}

function getClientUrl() {
 //Get the organization URL
 if (typeof GetGlobalContext == "function" &&
 typeof GetGlobalContext().getClientUrl == "function") {
 return GetGlobalContext().getClientUrl();
 }
 else {
 //If GetGlobalContext is not defined check for Xrm.Page.context;
 if (typeof Xrm != "undefined" &&
 typeof Xrm.Page != "undefined" &&
 typeof Xrm.Page.context != "undefined" &&
 typeof Xrm.Page.context.getClientUrl == "function") {
 try {
 return Xrm.Page.context.getClientUrl();
 } catch (e) {
 throw new Error("Xrm.Page.context.getClientUrl is not available.");
 }
 }
 else { throw new Error("Context is not available."); }
 }
}

function addNewView(viewName) {
 var newLine = document.createElement("LI");
 newLine.appendChild(document.createTextNode(viewName));
 document.getElementById("systemViews").appendChild(newLine);
}

 

 

 

 

 

Advertisements

Errors in Goal Recalculation

Platform: Dynamics CRM 2013 On-Premise SP1

Entity: SalesOrderDetail

Goal Metric: Extended Amount (sum)

Rollup Query: Product Group

Time Range: Custom

Symptom: Goal Progress totals are inaccurate (when two or more records have the same extended amount, product group and creation date.)

I noticed that after recalculating the above mentioned goal, the resulting progress total amount was inaccurate.  Started a SQL profile, and found out that the supporting SQL statement, yields less results than a direct query.

A direct query like the following one yields 82 rows:sqlok

However, the SQL generated by the goal recalculation yields less records:sql_Union

If we look at the first few rows in both result sets, we realize that since the goal recalculation uses a UNION statement, it excludes records with the same information (thinking that they are duplicates). For example, the first sales order has 2 lines with basically the same data (amount 46.51), but in the second set, we appreciate that only one is accounted for.

The problem happens when you have 2 or more salesorderdetail records with the same “extended amount” and the same product group. Since your SQL script uses a UNION statement, it thinks that those records are duplicates. The goal recalculation SQL statement fetches the following columns:

ExtendedAmount, new_productgroupid, ownerid, createdon, ownerbusinessunit, salesorderid

I am not sure why doesn’t it fetch salesorderdetailid (the primary key), which would resolve the problem in the recalculation.

The MSFT Dynamics CRM product team needs to look into this.

Is Goal Recalculation Time-zone Independent? Well, maybe not.

One of the nice features in Dynamics CRM when it comes to dates, is that it stores everything in Universal Time. For the most part, this works pretty well until you run in the following scenario related to goal recalculation.

Let’s suppose you have teams of salespeople grouped by regions, and that you have members in those teams that have goals not tied to any direct sales but goals assigned to the entire team, for example administrative staff. In this scenario, you would create goals with the following characteristics:

  • Goal Metric: Amount of Sales
  • Goal Owner: Admin staff
  • Target: Fixed Dollar Amount
  • From: Fixed Date
  • To: Fixed Date
  • Rollup Only from Child Goals: No
  • Record Set for Rollup: All (sales records owned by all team members)
  • Rollup Query: A query to filter by region

In this case, the expectation would be that all administrative staff shows the same progress at any moment. Well, at least that’s what I thought until I looked at the following chart:

goals

Figure 1 – Goal Progress Chart

It’s obvious that the progress for the first member is different from all others. After verifying that all the goals were exactly the same and have been recalculated around the same time, I was running out of ideas.

Fortunately, this is a CRM on premise implementation, so I had more chances to look at the server at a deeper level. The CRM async service was running OK, and no errors were found in the event log or CRM trace.

So I needed to dig even deeper. I ran a SQL Profiler session, and decided to recalculate the goals for the first and second person in the team. After a few hours of research, I found the smoking gun:

Person_Central

Figure 2 – Excerpt query person 1

Person_EST

Figure 3 – Excerpt query person 2

It turns out that person 1 had his time-zone set for Central Standard Time, whereas person 2 had his time-zone set for Eastern Standard Time; therefore, the query for person 1 was missing all transactions for the first day in the time range, given that all sales data only records the date of the transaction and the data is imported using Eastern time.

Now that I found this, at least I know what caused the discrepancy, and the fix is as simple as making every team member use the same time-zone. However, that may not be a realistic scenario. You could perfectly have teams that have members across different time-zones, in which case the goal recalculation should really be time-zone agnostic. That’s up for debate or perhaps to have the Dynamics product team take a look at.

Who needs a free customizable team calendar for Dynamics CRM 2013? Maybe you do.

How many times have you heard “I need to display my team’s data in a calendar view” while customizing a Dynamics CRM system? Perhaps more than once, and unless you used one of the paid components available on the market, you were out of luck.

At BusiPlanIT labs, we have come up with a solution that leverages one popular open source JQuery library (FullCalendar) and some OData calls, and embedded them in a solution that allows us to place a calendar anywhere we want in any Dynamics CRM 2013 application.

The components of this solution are:

  • JQuery library (jquery.com)
  • FullCalendar library and styling file (http://fullcalendar.io)
  • HTML page
  • JavaScript that contains OData calls to fetch your data
  • Sample dashboard

The HTML web resource can be used with any entity (OOTB or custom) and the data displayed is sorted by a date field passed in the custom parameters. In addition, you can pass an OData filter and a team name for further filtering. Your data will be color coded, green for active and red for inactive records. Each record displayed in the calendar includes a live link, so that you can navigate directly to the corresponding detail form.

The end result will look like this:

TeamCalendar

The following image shows the configuration screen for the HTML web resource:

TeamCalendar_Setup

Note that you will need to pass a comma separated list of parameters in the format:

Parameter1,Parameter2,Parameter3,Parametr4,Parameter5

  1. Parameter1 (Required) – Entity schema name (use the exact same casing as found in the entities list)
  2. Parameter2 (Required) – Date field schema name (use the exact same casing as found in the attributes list) to sort data in calendar.
  3. Parameter3 (Required) – String field schema name (use the exact same casing as found in the attributes list) to display in the calendar.
  4. Parameter4 (Optional) – OData filter for additional filtering (example “&$filter=Community/Value eq 1” for social channel Facebook).
  5. Parametr 5 (Optional) – Team name to filter by its members.

The parameters in the provided  example “ActivityPointer,ModifiedOn,Subject,,test,” will fetch all “activities” whose members belong to the team “test“, the field to be displayed in the calendar will be the “Subject” and the cells will displayed on the last date they were modified. It’s up you to decide which date field suits your needs best; for example if you wanted to show activities by due date, then the syntax would be “ActivityPointer,ScheduledEnd,Subject,,test,“, or if you wanted to sort them by start date then you would use “ActivityPointer,ScheduledStart,Subject,,test,“. The calendar can change display modes from daily, weekly to monthly.

This free component has some limitations that will be addressed in the paid version:

  • No drag and drop
  • Only captures one date, so no data ranges are shown
  • No edits are available from within the calendar
  • Teams of type “Access” are not supported

If you want to get a copy of the solution, just send us a request at: http://busiplanit.com/contactus.aspx

DISCLAIMER
THIS TOOL IS PROVIDED BY BUSIPLANIT INC. “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL BUSIPLANIT INC. BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

OData Syntax and JSON

The last html web resource I wrote seemed to be another routine coding activity.  This was a simple graphical indicator to display a light based on a value in a CRM form, and a simple OData query to fetch a single value.

This time I decided to use the following syntax:

http://<servername>/xrmservices/2011/OrganizationData.svc/<Entity>
Set(guid'<guid_value>’)?$select=<list_of_fields>

The Odata query worked as expected but the web resource was not.  Nothing was displayed and no error was generated.

After several hours of research and debugging, I found out that the JSON string that was generated had the following format:

json1

This was a little bit different from what my standard parsing routine expected.  Basically, the idea was to traverse an array called “results” using this construct:

var retrievedData = new XMLHttpRequest();

retrievedData.open(“GET”, url, false);

retrievedData.setRequestHeader(“Accept”, “application/json”);

retrievedData.setRequestHeader(“Content-Type”, “application/json; charset=utf-8”);

retrievedData.onreadystatechange = function () { retrieveReqCallBack(this, destination); };

retrievedData.send();

var fetchedData = JSON.parse(retrievedData.responseText).d;

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

. . . DO SOMETHING HERE

}

Looking at old web resources that fetched multiple records, the JSON string returned was like this:

json2

So, I had a smoking gun and then the fix was relatively easy. I just needed to access the attributes of the returned (single-record) dataset directly:

var dateStart = fetchedData.myAttribute;

. . . DO SOMETHING HERE

But I wasn’t happy just finding a fix to the problem.  I needed to find the root of the problem, why was the JSON string different?

The answer was in the OData syntax used.  In previous web resources I had used “filter”:

http://<servername>/xrmservices/2011/OrganizationData.svc/<Entity>Set?$filter=<key_name>/Id eq guid'{ <guid_value> }’&$select=<list_of_fields>

This syntax will fetch the same single record but it will use the equivalent to RetrieveMultiple in the SDK, which automatically wraps the fetched results in an array called “results”.

So, keep that in mind the next time you use OData, that the syntax you choose may have an impact on how the returned dataset needs to be parsed.

Yes, we drink our own KoolAid

Yes, we drink our own KoolAid

We just finished our first draft of our website (www.busiplanit.com), and we faced the dilemma of keeping it static or making it somewhat dynamic. So we decided to lead by example and connected our site with our own instance of Dynamics CRM Online.

Technically, it wasn’t as difficult as we thought it was going to be, and now we have a working example of how easy it is to use Dynamics CRM to keep track of your contacts and leads, even outside of the native Dynamics CRM interface. Granted, we didn’t build a full fledged portal with user authentication or Dynamics as a content management system (like the ADX Studio Portal), but still we are able to capture leads and manage the inquiries posted by our visitors, and by the way, we made it using the ‘Responsive UI’ design pattern so you can access it from any device.

Here are a few highlights of what you’ll need for a seamless integration of you portal with Dynamics CRM:

  • Find a host that offers at least .Net framework 4.0 and allows for full trust level.
  • Add the following tags in your web.config:
  1. <trust level=”Full” /> in system.web
  2. <add name=”Your Connection Name” connectionString=”Url=Your CRM Online Url”; Username=Your user name; Password=Your password;/>  in connectionStrings
  3. <section name=”microsoft.xrm.client” type=”Microsoft.Xrm.Client.Configuration.CrmSection, Microsoft.Xrm.Client” /> in configSections
  4. <add name=”Xrm” type=”Xrm.XrmServiceContext, busiplanit_1″ /> under contexts, microsoft.xrm.client
  • Even if you are using .Net 4.5, make sure you add microsoft.identitymodel.dll in your project (bin)
  • For you own protection, we recommend you add some anti-bot routine either a captcha component (lots of them in the market) or you may develop your own.
  • Finally, we developed a routine that loads static data from Dynamics CRM asynchronously, but that may deserve its own blog entry.

Well, we hope to hear from you and your questions, and are eager to apply these techniques to help you solve your information needs.