CRM 2011 – displaying similar quote products in a subgrid by injecting FetchXML

I had a requirement this week where a customer was adding products to a quote and when they selected a product they then want to see previous quotes which featured the same product and the price and quantity it sold for.  Basically the sales person wanted to see what the product had been sold for before to make sure the price they were quoting wasn’t too radical.

The idea I came up with was to have a grid on the Quote Product form, which displayed quote products of the same type as the quote product selected.

This would mean I would have to dynamically change a view or show items in the grid.

I created a view showing the fields of the Quote Product and then specified a product I was searching for.

I found this excellent blog post

CRM 2011 – Change Subgrid View Java Script

This blog post explains how you can load in new fetchXML into a subgrid, you can inject the FetchXML on the form onload, which enables you to get some variables from the form and inject them into FetchXML which then is used to update the SubGrid.

So on my Quote Product form I checked to see if a product had been selected on the form OnLoad, on this particular form the subgrid doesn’t load until you have selected a product, unit and a quantity, which then reloads the form.

The code itself which I have taken and changed from the blog post referenced above, is quite clever.  The function is called and if the SubGrid has not been loaded, it calls the function again after a small time delay.  This ensures the SubGrid is loaded before you try and inject the fetchXML into the view.

The FetchXML is created, I got the fetchXML by doing an advanced find on my previously created view, there is a button which allows you to retrieve the FetchXM, which is very useful.

function UpdateSubGrid() {
var quoteGrid = document.getElementById("Quote_Products");
var productId = Xrm.Page.getAttribute("productid").getValue();
if (productId != null){
//If this method is called from the form OnLoad, make sure that the grid is loaded before proceeding
 if (quoteGrid == null) {
 //The subgrid hasn't loaded, wait 1 second and then try again
 setTimeout('UpdateSubGrid()', 1000);
 return;
 }

var fetchXml = ""
+ " <entity name='quotedetail'>"
+ " <attribute name='productid' />"
+ " <attribute name='productdescription' />"
+ " <attribute name='priceperunit' />"
+ " <attribute name='quantity' />"
+ " <attribute name='extendedamount' />"
+ " <attribute name='salesrepid' />"
+ " <attribute name='uomid' />"
+ " <attribute name='manualdiscountamount' />"
+ " <attribute name='baseamount' />"
+ " <attribute name='new_quoteid' />"
+ " <attribute name='quotedetailid' />"
+ " <order attribute='productid' descending='false' />"
+ " <filter type='and'>"
+ " <condition attribute='productid' operator='eq' uiname='1 Function Easy Clean Mini Kit' "
+ " uitype='product' value='" + productId[0].id +"' />"
+ " </filter>"
+ " <link-entity name='quote' from='quoteid' to='quoteid' visible='true' link-type='outer' alias='a_ff5a49bd001a45f7a14dd99a28f37f91'>"
+ " <attribute name='customerid' />"
+ " <attribute name='name' />"
+ " <attribute name='quotenumber' />"
+ " <attribute name='quoteid' />"
+ " </link-entity>"
+ " <link-entity name='product' from='productid' to='productid' visible='false' link-type='outer' alias='a_e0b689bded554f07ab0b241a932d482e'>"
+ " <attribute name='productnumber' />"
+ " </link-entity>"
+ " </entity>"
+ "</fetch>";
 // alert(fetchXml);
 //Inject the new fetchXml
 quoteGrid.control.setParameter("fetchXml", fetchXml);
 //Force the subgrid to refresh
 quoteGrid.control.refresh();
}