Recent Posts

ads

FetchXml and QueryExpression in Microsoft CRM

Fetch Xml and Query Expression: 

Dynamic 365



Fetch Xml: 


1: it uphold total and gathering. 


2:it will recover upto 5000 records one after another. 


3: It underpins SSRS , javascript and c#. 


4: on the off chance that you are to change over a FetchXML that has select segments indicated from related element. 


5:it can be compose standard get xml design as it were. 


6: recover the information from various entites isn't feasible for question expressions,its conceivable just by utilizing fetchxml external joins. 


QueryExpression


1:it doesn't uphold total and gathering. 


2:it backings just serverside C#. 


3: the related substance segments are excluded from the QueryExpression sections list. 


4: however in execution question articulation is better than bring xml. 


5: it utilizes object situated style of coding, inteligency makes a difference. 


6: it is preferd to utilize complex conditions, it likewise bolsters starts with and end with conditions 


2)fetchxml


(A) Aggrigate: total is utilized to do numerical computation . 


Types: 1: entirety 


2: avg 


3: min 


4: max 


5: Count


6:multiple totals 


1) sum: it is utilized to ascertain complete number of estimations . 


eg: string estimatedvalue_sum = @" 


<fetch distinct='false' mapping='logical' aggregate='true'> 


<entity name='opportunity'> 


<attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/> 


</entity> 


</fetch>"; 


2) avg: it is utilized to discover the midpoints, 


eg: string estimatedvalue_avg = @" 


<fetch distinct='false' mapping='logical' aggregate='true'> 


<entity name='opportunity'> 


<attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg'/> 


</entity> 


</fetch>"; 


3) min: figures the base of information 


Eg: string estimatedvalue_min = @" 


<fetch distinct='false' mapping='logical' aggregate='true'> 


<entity name='opportunity'> 


<attribute name='estimatedvalue' alias='estimatedvalue_min' aggregate='min'/> 


</entity> 


</fetch>"; 


4) max: figure max of information 


eg: string estimatedvalue_max = @" 


<fetch distinct='false' mapping='logical' aggregate='true'> 


<entity name='opportunity'> 


<attribute name='estimatedvalue' alias='estimatedvalue_max' aggregate='max'/> 


</entity> 


</fetch>"; 


5) count: figure check of information 


eg: string opportunity_count = @" 


<fetch distinct='false' mapping='logical' aggregate='true'> 


<entity name='opportunity'> 


<attribute name='name' alias='opportunity_count' aggregate='count'/> 


</entity> 


</fetch>"; 


6) multiple totals: get various tasks at a solitary information. 


Eg: string estimatedvalue_avg2 = @" 


<fetch distinct='false' mapping='logical' aggregate='true'> 


<entity name='opportunity'> 


<attribute name='opportunityid' alias='opportunity_count' aggregate='count'/> 


<attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/> 


<attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg'/> 


</entity> 


</fetch>"; 


(b) bunch by: it is utilized to figure the gathering things one after another. 


Eg: Below is the guide to get entirety of aggregate sum of every won statement: 


string cites = @" 


<fetch distinct='false' mapping='logical' aggregate='true'> 


<entity name='quote'> 


<attribute name='totalamount' alias='totalamount_sum' aggregate='sum'/> 


<attribute name='statecode' groupby='true' alias='state'/> 


<filter type='and'> 


<condition attribute=' statecode ' operator='eq' value='won'/>"+ 


"</filter> "+ 


"</entity> "+ 


"</fetch>"; 


(c) connect element: interface between one element to another element 


Eg: I have three elements; Project, Product and Contact (Default element). 


Undertaking element can have various Products yet an item can just have a place for one task (1:N connection) 


Item can have various contacts and a contact can have a place with numerous items (N:N connection). 


<fetch version='1.0' yield format='xml-stage' mapping='logical' distinct='false'>"; 


<entity name='contact'>"; 


<attribute name='contactid'/> 


<attribute name='fullname'/> 


<attribute name='jobtitle'/> 


<attribute name='cre_role'/> 


<attribute name='cre_stakeholder'/> 


<attribute name='address1_city'/> 


<order attribute='fullname' descending='false'/> 


<link-element name='cre_contact_cre_product' from='contactid' to='contactid' alias='aa'> 


<link-element name='cre_product' from='cre_productid' to='cre_productid' alias='bb'> 


<link-element name='cre_project' from='cre_projectid' to='cre_projectid' alias='cc'> 


<filter type='and'> 


<condition attribute='cre_projectid' operator='eq' uiname='Contact' uitype='contact' value='" + projectid + "'/> 


</filter> 


</connect entity> 


</entity> 


</fetch> 


Connection type: 


(a) internal 


(b) Outer 


(a) inner: The qualities in the traits being joined are thought about utilizing a correlation administrator. Worth = 0. 


Eg: 


inward join among EntityMap and AttributeMap where the EntityMapID matches. 


<fetch version='1.0' mapping='logical' distinct='false'> 


<entity name='entitymap'> 


<attribute name='sourceentityname'/> 


<attribute name='targetentityname'/> 


<link-substance name='attributemap' alias='attributemap' to='entitymapid' from='entitymapid' connect type='inner'> 


<attribute name='sourceattributename'/> 


<attribute name='targetattributename'/> 


</connect entity> 


</entity> 


</fetch> 


(b) outer: which would be particularly helpful on the off chance that we could characterize "not exists" conditions), and we can't participate in a similar related substance more than once 


eg: Find all records that have no leads 


The accompanying tells the best way to build the question in FetchXML: 


XMLCopy 


<fetch mapping='logical'> 


<entity name='account'> 


<attribute name='name'/> 


<link-element name='lead' 


from='leadid' 


to='originatingleadid' 


connect type='outer'/> 


<filter type='and'> 


<condition entityname='lead' 


attribute='leadid' 


operator='null'/> 


</filter> 


</entity> 


</fetch> 


Queryexpression: 


1.QueryExpression is an article arranged, specifically way to deal with creating inquiries against the CRM information base. 


We need to determine the conditionExpression and Conditiion Operators while structuring the inquiries. 


QueryExpression is ideal in the event that we need to inquiry the CRM information with complex conditions. 


It underpins the mind boggling conditions with "AND" and "OR" administrators 


It upholds distinctive condition administrators like "Beginswith, Doesn'tbeginwith, Endswith,… " 


It supports to recover the information dependent on Link substances. 


Model: Below question recovers all the records their names begins with "MS" and identified with India nation. 


QueryExpression queryExp = newQueryExpression("account"); 


queryExp.ColumnSet = new ColumnSet(true); 


queryExp.Criteria.AddCondition("name", ConditionOperator.BeginsWith, "MS"); 


queryExp.Criteria.AddCondition("new_country", ConditionOperator.Equal, "India"); 


EntityCollection retrievedAccounts=(EntityCollection)OPCrmService.RetrieveMultiple(queryExp);

Post a Comment

0 Comments