Retrieve record by Using Many-To-Many Relation (Query Expression)

Problem:

              Need to retrieve related records by using many to many relations.

Solution:

              As in MS Dynamics CRM there are 1:N (one- to-many relation) , N:1 ( many- to- one relation) and there is N:N(many-to-many relation) so some time you need to get data by using one of the above relation. Using either 1: N or N: 1 relation you can easily get the record of the related entity but using N: N many-to-many relation can be little tricky.So I will show you in this post how to used one of the many-to-many relation to get the related entity details if you have such type of requirements.
As I am working on account entity in this series of posts, I have used account and marketing list many-to-many relation.I have added another data grid control for this post as I will show all related market list for the selected account entity. You can see the main screen in Image 1 , here the size of the marketing list data grid control will be less and I have just displayed the marketing list name, type(dynamic or static) and marketing list member type( the list is for account, contact or lead entity).


Note:For this post I have created separate view model for the marketing list data grid so that all the processing of the marketing list will be separated from the account data grid. You can find the new view model in the viewModel folder of the project with name “MarketingListViewModel” 
 

Image 1

Let us start with the code of how to retrieve using many-to-many relationship, the code is listed in the List 1, which you can see as below. Here you can see that first I have set the name of the entity which is used to get the record and as you can see I have set the name of the entity which is used in the many-to-many relationship (I have pick name of the entity from the many-to-many relation dialog "Relationship Entity Name", I have also upload the image of the dialog and highlighted the "Relationship Entity Name" you can see it in Image 2).Next thing is to add the condition to get the related record from the many-to-many entity; here I have passed the GUID of the account entity (entity which is selected by the user by clicking on the data grid row). So I will get all the records where account GUID will be matched( here I have set the attribute name to “entityid” which I have get by debugging the code by retrieving all the column with many-to-many entity name).

List 1

Next is the link entity which I have also discussed in my post "Retrieving Child Entity Columns Using Query Expression".here you can see that I have set the parent entity to the “Relationship entity name” as I will get ids of the list based on the entityid which is the id of the account records. So my parent entity will be the many-to-many entity name and then the parent entity attributes which will be mapped for the relationship is the id of the marketing list.After setting the parent entity name and the parent entity attribute next is to mention the child entity name and the child entity attribute, as I get to display the marketing list name and some of the information of the marketing list so my child entity will be marketing list as you can see that I have mention the LinkToEntityName and the LinkToAttributeName to marketing list name and marketing list id. Columns which I have retrieved for the linked entity are marketing list name, marketing list type (dynamic or static) and member type ( marketing list member type as marketing list is either for account , contact or lead entity).

Image 2

Add the link entity to the query expression and then send the request to retrieve the record is there is any record for the account GUID which passed.

Downloads
You can download the source code of the sample from here.
and working CRM 2011 solution from here.

Go to Home

All and any comments / bugs / suggestions are welcomed!

Retrieving Child Entity Columns Using Query Expression

Problem:

                Need to retrieve child entity attributes to be retrieved along the parent entity records. So that some of the child entity record should be displayed on the data grid.

Solution:

                As the problem state that we are going to get some of child entity attribute so that we can display more detail of the child entity along the parent records. As in my previous posts regarding the query expressions I am using the “account” as main entity mean account will be my parent entity and the contact will be my child entity.For this post I have retrieve contact business phone and the parent account, I have also added two column in the data grid control to show the telephone and the parent account name ( for this post I have rearranged the columns so that primary contact information are side by side as can be seen in the Image 1).

Image 1

Let us start with the code which I have added for the link entity to be retrieved.Code is listed in List 1, Here in code you can see that I have first created the LinkEntity object first and then I have set the column name which I want to get for the link entity (the child entity). Here you can see that I have used the “telephone1” and then “parentcustomerid”, these are the two attributes which will be displayed for the child entity as additional information for the child entity.
Next is how link the parent and child entity here you can see that link entity has properties like “LinkFromEntityName” which is used to get or set the logical name of the entity that you are linking from mean the parent entity (here “account is my parent entity).After setting the name of the parent entity next is to set name of the parent attribute which will be mapped to the child entity. Here you have to set the “LinkFromAttributeName” which is used to set the name of the parent entity attribute which will be mapped from.
Next is to set the “LinkToEntityName” which in my case is the “contact” entity, mean I want to link to the “contact” entity and then set the name of the attribute of the child entity in the “LinkToAttributeName” property. Here I have set the “contactid” as the mapped attribute in the child entity.Mean I want to map the “account” “primarycontactid” which is lookup to the contact entity to be mapped with the “contact” “contactid” so that I will get telephone1 and parentcustomerid of the contact which is the then lookup to the account entity.

 

List 1

Last property of the link entity object is to set the Alias of the entity which will then used to access the attribute values in the completed call back. Here I have set the “EntityAlias” to “child”.Then at the end just add the link entity object to the query expression “LinkEntities” collections.
After adding the link entity to the query expression next is how to get the value of the link entity attributes when call back function will be called. I have list only the piece of code which is used to get the values of the linked entity, which you can see in the List 2.Here you can see that I have used the “AliasedValue” type to be getting using the GetAttibuteValue of entity and then converted to the respective type. For the phone number as it is of type string so I have used the value to convert to the Tostring. But for the EntityReference type (PrimaryAccount is of type entity reference) so the value is converted to EntityReferce type as you can see in List 2.As “AliasedValue” is “Value is of “object” type so you have to convert it to respective type before using the using vaue.

List 2

Last piece of code is the xaml code which is used to bind the two new properties, telephone is simple string type so it will be easy to bind but the Primary account is of type EntityReference so we will use the Name property of “EntityReferece” for binding as you can see in the List 3. I have also appended “(Primary Contact)” with the header text of the columns so that you can distinguish between the attributes.

List 3

That is all about how to retrieve child entity attribute and mapped the linked attribute between parent and child entity. Hope you get starting point of how to use linked entity to get addition information regarding child entity.  

Downloads
You can download the source code of the sample from here.
and working CRM 2011 solution from here.

Go to Home

All and any comments / bugs / suggestions are welcomed!

Adding Conditions in Query Expression

Problem:

              In previous post related to the query expression I have added paging in query expression to get paged data, now need to add some condition so that we get the records based on some conditions.

Solution:

              When working with data retrieval there are some condition based on which you have to fetched the records, if there is not client specific conditions then there must be at least one condition to get the active records.Image for the sample application is shown in Image 1 as shown below, here you can see that I have displayed only the active records (I have deactivated the 3 records so that I have some active and deactivate records).

Image 1

The code for the sample which I have added is shown in the List 1 , here you can see that I have first created and initialized the condition expression object and then I have set the name of the attribute which I want to compare here I have mention the “statecode” mean I want to check the is record is active or not (you have to mention the schema name of the attribute here). Then next is the name of the operator which I will use for this condition and here you can see that I have used the ConditionOperator enumeration to select the operator which I will use for this condition and I have used equal operator. At the end is the values attribute which you need to set here as I have used equal operator so I will add just one value 0 ( mean I just want to get all the active records). You can add multiple values in the Values property the operator which may need more than one value are “in” operator or “between” operator.

List 1

Next is the filter expression object which is used to add the condition which we have created just above , the filter expression class has the filter operation which will be either “or” operator or “And” operation I have used “And” operator mean no matter how many condition I will add to this filter expression object but we can just set one filter operator which will be applied on that condition if you want to set different filter operator then you have to create another filter operation object and add the condition in that filter expression object.After adding condition to the filter expression next is to add the filter expression to the query expression.
Hope you get an idea how to add condition to the query expression as conditions are the most important part of any query. You can add as many condition as you can but remember in one filter expression you can add only one filter operation which may be “Or” or “And” operation.Below are the links for the download for the source code and MS Crm 2011 solution.

Downloads
You can download the source code of the sample from here.
and working CRM 2011 solution from here.

Go to Home

All and any comments / bugs / suggestions are welcomed!