Single post - 1



Custom Fetch XML and Advanced Search

xml

Introducing the fetch query capabilities for generating dashboard and reports using HTML or SSRS reports. We excited to highlight some of the capabilities that fetch xml customization to retrieve data for complicated data structure.

We can see below query for sql server where user can get data using left outer join.


SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;


Consider below scenario when we use sql server :

Account Entity

Account ID Account Address
1 Alfreds Futterkiste Avda. de la Constitución 2222
2 Ana Trujillo Obere Str. 57
3 Antonio Moreno Taquería Mataderos 2312
4 Around the Horn Berlin
11 B's Beverages Berlin
6 Blauer See Delikatessen Berlin
5 Berglunds snabbkop Newyork

Order Entity

OrderID Account ID Order Total
10308 2 100000.00$
10365 3 201232.00$
10355 4 323240.00$
10383 4 324324.00$
10289 11 4324.00$
10278 5 423423.00$
10280 5 42342.00$
10384 5 424322.00$

Output Needed
All Customers and Order Details

Account Order ID
Alfreds Futterkiste null
Ana Trujillo 10308
Antonio Moreno 10365
Around the Horn 10355
Around the Horn 10383
B's Beverages 10289
Berglunds snabbköp 10278
Berglunds snabbköp 10280
Berglunds snabbköp 10384
Blauer See Delikatessen null

When users cannot find the order details in related while looking for account


order-details

Some of the customer requirements are subjected to 360 view, where MSCRM dashboards have limitations. This deviates to the development of custom dashboards using HTML page. Same we can achieve on MSCRM by customizing the fetch query without using the XML toolbox.


Below advance find fetch all orders for customers with Order ID


order-details order-details

Download the fetch using advanced as shown above:
Customize below fetch xml

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="salesorder">
<attribute name="salesorderid" />
<attribute name="name" />
<order attribute="salesorderid" descending="false"/>
<link-entity name="account" from="accountid" to="customerid" visible="false" link-type="outer" alias="account">
<attribute name="name" />
</link-entity>
</entity>
</fetch>



After Customization this will look like below:
-replacing and inverting the link entity and attributes as shown below

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="account">
<attribute name="name" />
<link-entity name="salesorder" from="customerid" to="accountid" visible="false" link-type="outer" alias="account">
<attribute name="salesorderid" />
<attribute name="name" />
<order attribute="salesorderid" descending="false"/>
</link-entity>
</entity>
</fetch>



Let’s copy this to crm rest builder and evaluate the output needed

First two values are null same as we have seen in sql output above:
Remember Output in SQL account not having orders

All Customers and Order Details

Account Order ID
Alfreds Futterkiste null
Blauer See Delikatessen null

CRM Rest Builder Output:


value:[
• {
o @odata.etag:"W/"6377346"",
o name:"Alfreds Futterkiste",
o accountid:"d94b4766-bbe8-e811-a97f-000d3ab11b7a"

},
• {
o @odata.etag:"W/"6377356"",
o name:"Blauer See Delikatessen",
o accountid:"edd68291-bbe8-e811-a97f-000d3ab11b7a"

},
• {
o @odata.etag:"W/"6377348"",
o name:"Ana Trujillo",
o accountid:"82db5372-bbe8-e811-a97f-000d3ab11b7a",
o salesorder_x002e_salesorderid:"e7a283e8-bbe8-e811-a97d-000d3ab27d56",
o salesorder_x002e_name:"10308"
},
• {
o @odata.etag:"W/"6377350"",
o name:"Antonio Moreno Taquería",
o accountid:"a0a89a7e-bbe8-e811-a97f-000d3ab11b7a",
o salesorder_x002e_salesorderid:"5f27bcfa-bbe8-e811-a97d-000d3ab27d56",
o salesorder_x002e_name:"10365"
},
• {
o @odata.etag:"W/"6377352"",
o name:"Around the Horn",
o accountid:"ca497485-bbe8-e811-a97f-000d3ab11b7a",
o salesorder_x002e_salesorderid:"cb5ac130-bce8-e811-a97d-000d3ab27d56",
o salesorder_x002e_name:"10355"
},
• {
o @odata.etag:"W/"6377352"",
o name:"Around the Horn",
o accountid:"ca497485-bbe8-e811-a97f-000d3ab11b7a",
o salesorder_x002e_salesorderid:"9a517543-bce8-e811-a97d-000d3ab27d56",
o salesorder_x002e_name:"10383"
},
• {
o @odata.etag:"W/"6377354"",
o name:"B's Beverages",
o accountid:"88758c8b-bbe8-e811-a97f-000d3ab11b7a",
o salesorder_x002e_salesorderid:"adbfa555-bce8-e811-a97d-000d3ab27d56",
o salesorder_x002e_name:"10289"
},
• {
o @odata.etag:"W/"6377358"",
o name:"Berglunds snabbkop",
o accountid:"0c238f97-bbe8-e811-a97f-000d3ab11b7a",
o salesorder_x002e_salesorderid:"7e0ea265-bce8-e811-a97d-000d3ab27d56",
o salesorder_x002e_name:"10278"
},
• { o @odata.etag:"W/"6377358"",
o name:"Berglunds snabbkop",
o accountid:"0c238f97-bbe8-e811-a97f-000d3ab11b7a",
o salesorder_x002e_salesorderid:"6b6caf71-bce8-e811-a97d-000d3ab27d56",
o salesorder_x002e_name:"10280"
},
• {
o @odata.etag:"W/"6377358"",
o name:"Berglunds snabbkop",
o accountid:"0c238f97-bbe8-e811-a97f-000d3ab11b7a",
o salesorder_x002e_salesorderid:"c1c0b77d-bce8-e811-a97d-000d3ab27d56",
o salesorder_x002e_name:"10384"
}


Hope this gives little information. You can do this for more complex requirements and data structure .Let me add in part 2 for complex fetching of data, in my next blog.
Happy Coding…!
Thank you.

Prasad Tumanpelli
NSquare Xperts
www.nsquarexperts.com