The ‘orderBy’ expression is limited to invoking the ‘ ‘ parameter

LINQ is widely used in projects and it makes querying very handy. Using LINQ for querying CRM data has some limitations though, some of the standard LINQ features are not supported to use with CRM.

One such limitation is when we are joining entities and also sorting results. To use orderby, we are limited to the first entity only (the left side entity in join). So the query in Example 1 works perfectly fine:

Example 1:

var results = from c in ContactSet
join a in AccountSet
on c.ContactId equals a.PrimaryContactId.Id
orderby c.FullName
select new
{
c.FullName,
c.Address1_City,
a.Name,
a.Address1_Name
};

But trying to sort by columns of other entity is not supported. So if we try Example 2 it will throw the following NotSupportedException

The ‘orderBy’ expression is limited to invoking the ‘c’ parameter.

Exaple 2:

var results = from c in ContactSet
join a in AccountSet
on c.ContactId equals a.PrimaryContactId.Id
orderby a.Name
select new
{
c.FullName,
c.Address1_City,
a.Name,
a.Address1_Name
};

Of course if you run similar quires directly on SQL Server database they will work fine.

Solution:

If you are getting this exception there are two possible solutions:

  • Modify your quey as per Example 1 and put orderby columns’s entity firs or on the left side of join
  • For queries involving orderby from multiple entities, select data, store it in memory and use a separate LINQ query to sort it.
    This MSDN document list limitations of LINQ for CRM

Enjoy working with CRM 🙂

Parent child lookup fields in Dynamic CRM

Showing parent-child records in drop-down lists is a common requirement of business applications.

Some examples:

  1. Manager > Employees
  2. Country > State or Province > City
  3. Account category > Account sub-category > Account number

In Dynamics CRM same behaviour can be implemented by using multiple lookup fields with “Related Records Filtering” which is an out of box CRM feature. For this demo consider an entity “Car Finance Application”, which needs some related details like car brand, model and variant.

a

 

 

Steps:

  • Create the following entities and their relationship as described below:
    • Brand
    • Model, it has N:1 relationship with Brand
    • Variant, it has N:1 relationship with Model
  • Add brand, model and variant lookup fields in consumer entity which in this case is CarFinanceApplication:
  • Add brand, model and variant fields in the form
  • Double click on Model lookup field, and do the “Related Records Filtering”  configurations as shown below:  
  • Double click on Variant lookup field, and do the “Related Records Filtering”  configurations as below:

 

  • Save changes + build customizations + test working of lookup field

Form and lookup fields have been configured and selecting brand will show relevant models. As user selects a model, variant look up will list relevant records but there is a catch. If user changes brand or model after selecting variant, values in three lookup fields will not be in sync. Saving record in such scenarios will basically save  incorrect data  and you will agree many times even user will not realise this. An easy approach to fix this issue can be to reset model and variant look up fields when brand field changes and reset variant lookup field when model field changes.

  • From same form window add the following JS as a code library:
function ClearLookUp() {
for (var counter = 0; counter < arguments.length; counter++) {
if (typeof(arguments[counter]) === "string") {
var field = Xrm.Page.data.entity.attributes.get(arguments[counter]);
if (field != null && field.getValue() != null)
field.setValue(null);
}
}
}
  • Double click on “Brand” lookup field and add “ClearLookUp” javascript function on its “onChange” event. Pass execution context and name of lookup field(s) as comma-separated arguments which will need to reset.

 

  • Double click on “Model” lookup field and call “ClearLookUp” function on its “onChange” event. Pass execution context and name of lookup field(s) as a comma-separated argument.
  • That’s it, test your work and go enjoy a coffee if you like 🙂

Considerations:

  • Any number of lookup fields can be configured with this approach
  • Javascript function is a sort of generic, it can be configured with any lookup field without modification. Also just by calling once it can reset any number of lookup fields, the only requirement will be to pass the name of lookup fields as comma-separated arguments.

Credit:

Before this post, I found a similar implementation by Rashan which I have improved.

 

 

 

 

 

 

Turbo forms with new form rendering engine

For faster and efficient form loading CRM Online 2015 Update 1 (v7.1) introduced a new form rendering engine. New engine renders forms up to 8 times faster than legacy form.

New form rendering uses cache and an improved process for form loading which results in better performance for end user but for CRM techies it is more than that. With new process unsupported APIs and direct DOM manipulation may not work and it requires careful testing in sandbox particularly if it is an upgrade. A  dialogue reports such errors:

error

 

CRM team has provided the following examples that may be unsupported:

  • Any attempt to access DOM in the content iframe using JS, jQuery or other 3rd party libraries (document.getElementById() or jQuery selectors)
  • Creating a new HTML content in the parent window for persistent content (and assumed that the parent window was the main CRM iframe.
  • Window.load, parsing iframe/form URL
  • Attempting to use unsupported (non-XRM) APIs, especially undocumented ones that may have been shipped with CRM for internal usage only
  • Accessing window.parent() from a web resource that may assume for example there’s a variable set in the current window context.

Some other examples:

  • Form freeze
  • Cannot select or type in a field
  • Java Script do not perform intended functionality
  • Any thing else unexpected that happens in form

Strategies to identify Potential issues:

  • Use CRM 2015 custom code validator to validate deprecated and unsupported APIs
  • Switched off new rendering engine from below setting and test unsupported code

Settings -> Administration -> System Settings -> General -> Use legacy form rendering

Form rendering engine settings

  • If a third party solution is used, check with vendor if the solution is verified with new rendering engine
  • Analyse client side code, a simple test can be removing client side code segments
  • Try different browsers and their development tools
  • Try removing effected fields from form, test and drill down

Form rendering engine effects whole CRM instance it is not per entity or form. Legacy form rendering is still supported and rendering engine can be switched but CRM team’s plan is to remove it in future.

Understanding leads, opportunities, accounts and contacts in CRM

Leads, Opportunities, Contacts and Accounts are entities in CRM. In this post I will explain their role in CRM. Lets start with easiest one.

Accounts
Accounts are companies we are doing business with.

Contacts
Contacts are people working in other companies (which are Accounts in CRM), it is anyone working in other company if we are dealing with them, it can be for taking orders, approvals or invoices.

Leads
Leads are potential sales. They can be someone new to business or an existing customer. Depending on organizational sales process initial processing (it may be reviewing a lead or contacting the person or something else) qualify or disqualify a lead. In CRM qualified lead become opportunity.

Opportunities
In CRM an opportunity can be created in two ways:
1. A qualified lead becomes an opportunity, also it becomes contact automatically
2. We directly can add an opportunity without having a lead for it

Java Script Facts for great JS coding 1

JS
Introduction

Java script is an interesting and popular programming language. Its syntax is easy, features are cool and its role is becoming more and more important every day in web development. Usually it is easy to get started with JavaScript because a lot of resources and community help is available. Starting this way works but fundamentally there are a few thing in Java Script which are different from other programming languages. I m not saying this post is covering everything need to do great Java Script development but I m sure knowing and and following these facts will help you doing great Java Script coding. Knowing basics of Java Script syntax is good to have to gain maximum benefits from this article.

1. Variable scope in JS is different
In C# or Java variable scope has limit inside curly braces, it means a variable declared inside “{}” cannot be accessed out of “{}”. It is not the case in Java Script and a variable declared inside “{}” is not private and is accessible out of block as well.

if (true) {
var no = 50; //variable declared and value initilized
console.log("Variable value inside block: " + no);
}
console.log("Variable value outside block: " + no); // accessable

2. There are only two types of variable scopes in JS Global and Local
A Java Script variable can be public or private. A variable declared inside Java Script function is refered as private, means it cannot be accessed out of function but other wise a function is always global and can be accessed throughout script.
var var1 = 12;
//var1 is accessable here of course
(function SelfExecutedTestFun() {
console.log("value of globalNo from self executed function is: " + var1);
var var2 = 500;
})();
console.log("value of var1 from out side self executed function is: " + var1);
console.log("value of var2 from out side function is: " + localno); // error var2 is not defiened

3. JS is a hoistic language, so it allows using a variable in code and declaring it later.

no = 50;
console.log(no);
var no;

4. A JS variable can be used without declaration, and its scope will be Global

no = 50;
console.log(no);

5. JS allow doing a few thing which looks funny. For example we can use a variable without declaring, we can delete a variable, can declare a variable multiple times, having multiple function parameters of same name and using a few future reserved key words like public, private, interface etc. So all below case are ok in JS.

no = 50;
console.log(no);

delete no;
console.log("accessing no after deleting it: " + no);
var no = 60;
var no;
console.log("declaring a variable multiple times and accessing it" + no);
function TestFun(para1, para1) {
}

ActionLink passing parameter as QueryString

In MVC Razor ActionLinke, it is a common requirement to pass parameter as a query string. It creates URL like below:

/Home/Edit/1

and of course basically it is <a href="/CodeTest/EditClass/1">Edit</a>

ActionLink provide different overloads, using one of them such links can be generated:

@Html.ActionLink("Home", "Edit", new {id=1})

Instead of hard coded strign a model property can also be used:

@Html.ActionLink("Home", "Edit", new {id=Model.Id})

Important

If you are using T4MVC templates is a little different from above examples, then you will code as per below example:
@Html.ActionLink("Edit",MVC.CodeTest.EditClass()
.AddRouteValue("id",1))

and using model:
@Html.ActionLink("Edit",MVC.CodeTest.EditClass()
.AddRouteValue("id",itm.ClassID))

For passing multiple parameters use AddRouteValues().