Monday, May 9, 2011

Selected Specific Columns when Using OData using JSON In Microsoft CRM 2011

In this post I will show you about getting the required columns of the two entities which are fetch in one json request. For this example what I want to achieved is to get full name, first name , last name and the title of the system user and then from system user role I want to fetch the roles of the user which are associated with the system users. The Javascript code is listed in the List 1 as you can see in below. Here you can see code is simple the only thing which need some discussion here is the formation of the serverURL.

List 1

In the serverURL I have used the $expand keyword with the systemuserroles_association entity the $expand is query string options defined in the OData protocol that are implemented in the REST endpoint for Microsoft Dynamics CRM.The $expand
Directs that related records should be retrieved in the record or collection being retrieved. for example, to retrieve opportunity records related to accounts, use the opportunity_customer_accounts entity relationship. The query like /AccountSet?$expand=opportunity_customer_accounts returns the opportunity records and the account records.
Note: By default maixmum six relationships can be expanded.
Next I have to get the Full name , first name, last name and title for the system user and only the name of the roles which are assigned to the user. For that I have use the $select which is one of the query string options defined in the OData protocol which Specifies a subset of properties to return and the order in which the columns of data will be organized. The default is to return all columns that corresponds to $select=*.
You can see the order of the columns selection. Here I have used the $select and then first I have selected the column name for the systemuser pluse the systemuserroles_association entity name and the forward slash (/) and the column name of the systemuserroles_association which in this case is the name as I want to retrieve the name of the role(s) which are assigned to the user.
Image 1

In the retrieveSystemUserCallBack function I have parse the result using Json.parse and assign the .d property to the new variable. Then in the next statement I have used 2 loops to read the roles of the user as the system user roles filled in the collection with the name systemuserroles_association which you can see in the Image 1. You can see the whole structure of the one record which is shown in the Image 1.

All and any comments / bugs / suggestions are welcomed !

No comments: