Part 3: Creating an Editable Grid in CRM 2013 Using Knockout JS

This is the third installment following Part 2, which demonstrated the editable grid from inside Microsoft Dynamics CRM 2013, and Part 1, which introduced the editable grid in CRM 2011. This blog introduces paging.

I will first demo what the grid looks like with paging in a CRM 2013 environment. Afterwards, I will walk through the main block of code.

I adopted the concepts from this great blog post from Ryan Vanderpol, about adding a pager to a grid.

Demo

The following screen shot demonstrates the pager inside the grid.

The above demonstrates:

• The “Previous” and “Next” buttons allow the user the move forward and backwards one page. Currently, the “Previous” button is disabled because the first page is being displayed.
• The numbers “1”, “2”, and “3” represent the page numbers.

Code Walkthrough

The following code represents the additional changes required to the source originally introduced in Part 2 of my blog.

I have added a new resource to the mix:

• new_bootstrap_no_icons.css

I have made changes to the following source.

• The below html web resource.
  *Look for code marked in green
  *Strike out code is either replaced or removed

See Code Walkthrough here

Code Comments

Snippet	Comments
<tbody data-bind=”foreach: pagedList“>	Loop through pageList instead of oproducts collection.
<div class=”pagination”>	Represents the paging UI controls. This code uses the styles from bootstrap CSS.
self.pageSize = ko.observable(3);	Establishes the number of rows to display per page.
self.pageIndex = ko.observable(0);	Determines what page to start on when the form loads.
self.moveToPage(self.maxPageIndex());	This is called after a new record is inserted
if (self.pageIndex() > self.maxPageIndex()) self.moveToPage(self.maxPageIndex());	This is called after a record is removed.

What’s next?

In future blog posts:

• Resolving deadlocks when saving multiple records from the editable grid.
• Sorting.
• Do you have any suggestions on what you would like to see?

Part 2: Creating an Editable Grid in CRM 2013 Using Knockout JS

This is the second installment following Part 1, which introduced the editable grid in CRM 2011. Since then, I have upgraded the editable grid to work in CRM 2013.

In this blog, I will first demo what the grid looks like in CRM 2013. Afterwards, I will walk through the main block of code.

Demo

The following screen shots demonstrate the editable grid of opportunity products inside of the opportunity.

The above demonstrates:

• Editing existing data, including lookup data
• Adding a new record
• Deleting an existing record
• The introduction of the custom option set field, ‘projected schedule’ (new to this post)

Steps

1. Click ‘Add Opportunity’
2. The standard lookup appears; select a product and click ‘Add’

3. Choose a ‘projected schedule’

4. Click ‘Save’

5. As a proof of concept, the code takes the count of opportunity products, and updates the parent ‘estimated budget’ field.  This demonstrates updating the parent opportunity.

Code Walkthrough

The following code is reference implementation that you can adapt to your needs.

The following web resources make up this solution:

• Jquery.js

• Knockout.js (version 2.2)

• SDK_REST.js

• SDK.MetaData.js

• The below html web resource.

Code Walk Through

Code Comments

What’s next?

In future posts:

• Resolving the known issue with IE (yes, IE only – sigh)
  • Object Expected with JsProvider.ashx
  • How to integrate this code in CRM
  • Sorting
  • Paging through the grid

Creating an Editable Grid in CRM 2011 Using Knockout JS

Dave Berry wrote an excellent customization for CRM 4.0 which provided the ability to mass update child records, directly from the related parent’s form. Unfortunately, the CRM 2011 architecture prevents this customization from being adopted. By now, there exist quite a few commercial solutions for grid editing.

http://www.sparklexrm.com/s/features.html
http://pinpoint.microsoft.com/en-US/applications/add-edit-grid-for-crm-2011-and-crm-online-12884923430 http://pinpoint.microsoft.com/en-US/applications/editable-grid-add-on-for-crm-2011-and-crm-online-12884921672 http://www.c360.com/RecordEditor.aspx http://www.axonom.com/crm_solutions/powertrak/articles/editablegrids.html

I’ve been looking for a way to build a solution instead of buy.  I came across this fantastic blog post, which suggests using knockout JS as the data-binding tool. There is a great tutorial and lots of examples to get started.

The following is an example of an implementation of an editable grid for CRM 2011. I show two examples of editing opportunity products from within the parent opportunity which are:

• How all changes to opportunity product are saved in a single operation
• How only one opportunity product is saved back to the server

I demonstrate the following features which allow:

• Editing existing data, including lookup data
• Adding a new record
• Deleting an existing record

Demo

The first editable grid illustrated above, implements the ‘multi-save’ design. In this case, all data is editable all the time. The second editable grid demonstrates using a single-record approach to editing records.

Referring to the single-edit approach, clicking on the ‘Edit’ link enables that record for edit. In this approach, the user must click ‘Apply’ in order to successful save the data. The record remains in edit mode until the user clicks ‘Apply’ or ‘Cancel.

Click ‘Apply’ results in a pop-up confirmation of the success. Naturally, the pop-up can be removed.

In the multi-save demonstration, note that both quantities have been updated. Click ‘Save’ to send the updates back to the server.

A confirmation pop-up reports the success of both records.

Click ‘Add Opportunity Product’ to add a new record.

Adding a record requires that certain required fields, namely the product, are selected. Therefore, the standard product lookup is presented.

Upon selecting a product, and clicking ‘Ok’, the record is successfully created. Notice that quantity and UOM are defaulted.

Removing a record is accomplished by clicking ‘Delete’ link next to the record.

Notice the record has been removed.

In the next blog post, I will walk through the code and how to integrate this code in CRM.

Some features I plan on demonstrating in a future blog are:

• Editing ‘OptionSet’ fields
• Sorting
• Paging through the grid

CRM 2011 – How to get the current user from within a dialog

CRM 2011 Dialogs do not provide any easy way to access the currently logged in user’s information.

By creating a new instance of an entity (I create a special entity), the owner field will contain the currently logged in user.

The steps I follow:

1. Log into CRM and create a new entity, call it new_CurrentDialogUser. You do not have to add any new fields.
2. Open your existing dialog for editing.
3. From the Dialog editor, the first thing to do is create an instance of new_CurrentDialogUser.
   • Create a new stage, positioned at the very beginning of the dialog.
   • Click ‘Add Step’ and select ‘Create Record’.
   • Choose the type to be new_CurrentDialogUser.
   • Click on properties. Notice the owner field is presented. Do not populate this field. Instead, when the ‘Create Record’ step executes, it will default the owner to the currently logged in user.
4. Once the ‘Create Record’ steps executes in your dialog, you have the ability to reference this step from throughout the rest of your dialog, just as if you were referencing the current record that is related to your dialog.
5. In my case, I use an ‘Assign Value’ step to store the owner of the newly created new_ CurrentDialogUser to a local variable, called CurrentUser.
   • Click ‘Add Step’ and choose ‘assign value details.’
   • In the ‘Look For’, select from the local values, the choice ‘Create (Current Dialog User)’.
6. Finally, once my am done using the current user, I perform an ‘UpdateStatus’ step, as a means to
   deactivate the dynamically created new_CurrentDialogUser instance.
   • Add step, select ‘Change Status’
   • Select the ‘Create (Current Dialog user)’.
   • Change the status to ‘Inactive’.
7. At this point, you can adopt Gonzalo Ruiz’s custom activity workflow, which exposes a ‘delete’ record step. The deleting can performed as  separate workflow.

Microsoft Dynamics CRM – Querying Data Using Late Binding versus Early Binding

Although Microsoft recommends using early binding over late binding in Microsoft Dynamics CRM® 2011, my experience is that querying data is faster when using a late binding approach.

I demonstrate this by using the following three approaches:

• Early binding
• Late binding
• A mix of early and late binding.

My tests were performed using an on-premise environment. The query was to extract over 300 thousand active accounts. (By default, Dynamics CRM 2011 restricts the number of records to 5000. In order to retrieve more, requires enabling the registry setting, TurnOffFetchThrottling – refer to http://support.microsoft.com/kb/911510 for more information)

Approach – All Records	Time Elapsed
1 – Query all using Early Binding – CreateQuery<Account>()	(seconds): 43 (milli): 382
2 – Query all using Late Binding – RetrieveMultiple	(seconds): 23 (milli): 263
3 – Query all using Late & Early Binding – CreateQuery(“account”)	(seconds): 37 (milli): 101
Approach – Specific Account
4 – Query account using Early Binding – CreateQuery<Account>()	(seconds): 1 (milli): 142
5 – Query  account  using Late Binding – RetrieveMultiple	(seconds): 0 (milli): 203
6 – Query  account  Late & Early Binding – CreateQuery(“account”)	(seconds): 0 (milli): 280
7 – Query account using Early Binding – Retrieve	(seconds): 0 (milli): 406
8 – Query account using Late Binding – Retrieve	(seconds): 0 (milli): 128

The above results show that the late binding, approach #2, when retrieving multiple records, is the fastest approach. The above results also highlight that, when retrieving, a specific account by its unique identifier, the late binding approach also out-performs the early binding.

In addition to the above tests, I also compared the performance of returning all columns versus only a few.

• For early binding, the number of columns returned is controlled in the LINQ select statement

var accounts = from c in orgSvcContext.CreateQuery<Account>()
where (c.StateCode.Equals(“Active”))
select new Xrm.Account
{Id = c.Id,
new_Country = c.new_Country
// ADD MORE COLUMNS HERE};

• Changing the number of columns did not affect performance that greatly.
• For late binding, specifically, in the case of Approach #2, RetrieveMultiple, changing the number of columns greatly affects performance.
  • Controlling columns returned is accomplished by the QueryExpression Columnset property.
  • In my tests, when setting  Columnset to true, I actually encountered a timeout exception. (Note: the timeout depends on the number of records that are returned. In my case, returning over 300 thousand put too large a demand on the query.) The following error can be seen in the event viewer, located on the CRM server.

Log Name:      Application
Source:        MSCRMPlatform
Date:          8/12/2012 5:00:57 PM
Event ID:      17972
Task Category: None
Level:         Warning
Keywords:      Classic
User:          N/A
Computer:      crm2011server
Description:
Query execution time of 16.7 seconds exceeded the threshold of 10 seconds. Thread: 14; Database: crm_tst_MSCRM; Query: select
“account0”.Address1_FreightTermsCode as “address1_freighttermscode”
, “account0”.Telephone3 as “telephone3”
, “account0”.new_BookingSales as “new_bookingsales”
, “account0”.new_CompanyPeople as “new_companypeople”
….
….

Query all active accounts

Approach 1 – Query all using Early Binding – CreateQuery<Account>()

var accounts = (from c in orgSvcContext.CreateQuery<Account>()

where (c.StateCode.Equals(“Active”))
select new Xrm.Account
{
Id = c.Id,
new_Country = c.new_Country
}).ToList<Xrm.Account>();

Approach 2 – Query all using Late Binding – RetrieveMultiple

QueryExpression query = new QueryExpression();

query.EntityName = “account”;

query.ColumnSet = new ColumnSet(“ownerid”, “createdby”, “name”, “new_idse”, “new_country”, “new_stateprovince”, “address1_city”, “address1_postalcode”, “new_customertypeid”, “new_companyindustry”);
query.Criteria.AddCondition(“statecode”, ConditionOperator.Equal, 0);
List<Entity> results = new List<Entity>();
EntityCollection retrieved1 = _service.RetrieveMultiple(query);
results = retrieved1.Entities.ToList();
List<CrmAccount> result1 = new List<CrmAccount>();
var accounts = (from item in results
select new CrmAccount
{
accountid = (Guid)item[“accountid”],
name = item.Contains(“name”) ? item[“name”].ToString() : string.Empty
});

Approach 3 – Query all using Late & Early Binding – CreateQuery(“account”)

var query2 = (from c in orgSvcContext.CreateQuery(“account”)
where ((!c[“statecode”].Equals(null) && (string)(c[“statecode”]) == “Active”))
select new CrmAccount
{
accountid = (Guid)c[“accountid”],
name = item.Contains(“name”) ? item[“name”].ToString() : string.Empty
}).ToList();

Query specific account

Approach 4 – Query account using Early Binding – CreateQuery<Account>()

var accounts = (from c in orgSvcContext.CreateQuery<Account>()
where (c.Id.Equals(id) && c.StateCode.Value.Equals(0))
select new Xrm.Account
{
Id = c.Id,
new_Country = c.new_Country,
OwnerId = c.OwnerId,
Name = c.Name,
new_IDSE = c.new_IDSE,
new_StateProvince = c.new_StateProvince,
Address1_City = c.Address1_City,
Address2_PostalCode = c.Address2_PostalCode,
new_CustomerTypeID = c.new_CustomerTypeID,
new_CompanyIndustry = c.new_CompanyIndustry
}).ToList<Xrm.Account>();

Approach 5 – Query account using Late Binding – RetrieveMultiple

QueryExpression query = new QueryExpression();

query.EntityName = “account”;

query.ColumnSet = new ColumnSet(“ownerid”, “createdby”, “name”, “new_idse”, “new_country”, “new_stateprovince”, “address1_city”, “address1_postalcode”, “new_customertypeid”, “new_companyindustry”);
query.Criteria.AddCondition(“statecode”, ConditionOperator.Equal, 0);
query.Criteria.AddCondition(“accountid”, ConditionOperator.Equal, id);
List<Entity> results = new List<Entity>();
EntityCollection retrieved1 = _service.RetrieveMultiple(query);
results = retrieved1.Entities.ToList();
List<CrmAccount> result1 = new List<CrmAccount>();
var accounts = (from item in results
select new CrmAccount
{
accountid = (Guid)item[“accountid”],
name = item.Contains(“name”) ? item[“name”].ToString() : string.Empty
});

Approach 6 – Query account using Late & Early Binding – CreateQuery(“account”)

var query_join2 = (from c in orgSvcContext.CreateQuery(“account”)
where ((!c[“statecode”].Equals(null)
&& (string)(c[“statecode”]) == “Active”)
&& (c[“accountid”].Equals(id)))
select new CrmAccount
{
accountid = (Guid)c[“accountid”]
}).ToList();

Approach 7 – Query account using Late Binding – Retrieve

Entity entity = _serviceProxy.Retrieve(entityName, entityId, new ColumnSet(“name”));

Approach 8 – Query account using Early Binding – Retrieve

Account retrievedAccount = _serviceProxy.Retrieve(Account.EntityLogicalName, accountid,
new ColumnSet(“primarycontactid”)).ToEntity<Account>();

To learn more about Edgewater’s CRM offerings, click here.