Case: I wanted to display a list of records (say Entity B) in a form of Entity A. The data of Entity B displayed is filtered based on the equality of two lookup fields (namely, customer and supplier) between Entity B and Entity A. Further, there is no relationship that links the two entities.
From a configuration standpoint, this isn’t doable, even if the two have a 1:N relationship. But, as Google is everybody’s best friend, I had to look it up to be absolutely sure. And rightfully so, it isn’t. So I had to turn to scripting to meet the requirement.
There’s a wealth of references available online that tackles the same. I’m no pro-developer so I kind of just depend on what others had written (see references at the bottom), read them (as I think I could) and just take it from there. Here is the entire javascript code that solved it for me:
var SDK = window.SDK || {};
(function () {
this.filterEntityBsGrid = function (executionContext) {
//---Get the form context using execution context
var formContext = executionContext.getFormContext();
//---Suppose there is a subgrid named "EntityBSubgrid" placed on the entity's form
var conSubGrid = formContext.getControl("EntityBSubgrid");
var customer = null;
var supplier = null;
if (formContext.getAttribute("new_customer").getValue() != null && formContext.getAttribute("new_supplier").getValue() != null) {
customer = formContext.getAttribute("new_customer").getValue()[0].id.replace("}", "").replace("{", "");
supplier = formContext.getAttribute("new_supplier").getValue()[0].id.replace("}", "").replace("{", "");
}
//Set Grid to Empty if Customer or Supplier is null
var fetchXml = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
+ '<entity name="new_entityb">'
+ '<attribute name="new_entitybid" />'
+ '<order attribute="new_entitybid" descending="false" />'
+ '<filter type="and">'
+ ' <filter type="or">'
+ ' <condition attribute="new_customer" operator="null" />'
+ ' <condition attribute="new_supplier" operator="null" />'
+ ' </filter>'
+ '</filter>'
+ '</entity>'
+ '</fetch>';
if (customer != null && supplier != null) {
//Show related records
fetchXml = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
+ '<entity name="new_entityb">'
+ '<attribute name="new_name" />'
+ '<attribute name="new_supplier" />'
+ '<attribute name="new_notes" />'
+ '<attribute name="new_customer" />'
+ '<attribute name="new_entitybid" />'
+ '<order attribute="new_name" descending="false" />'
+ '<filter type="and">'
+ '<condition attribute="new_customer" operator="eq" uitype="account" value="' + customer + '" />'
+ '<condition attribute="new_supplier" operator="eq" uitype="account" value="' + supplier + '" />'
+ '</filter>'
+ '</entity>'
+ '</fetch>';
}
conSubGrid.setFilterXml(fetchXml);
//Refresh grid to show filtered records only.
conSubGrid.refresh();
}
}).call(SDK);
Adding the above code to fire on form load, on save, and on change (of customer and supplier) events, this is how the form behaves:
That’s it! š
UPDATE:
The above code didn’t work in a non-UCI environment, specifically, the setFilterXML() method. In lieu of it is the setParameter property of the getgrid() method. Furthermore, I had to add a few more if conditions to cover for the other combinations of the customer and supplier variables.
var FilterOppGrid = window.FilterGrid || {};
(function () {
this.filterOpenOppGrid = function (executionContext) {
//---Get the form context using execution context
var formContext = executionContext.getFormContext();
//---Suppose there is a subgrid named "EntityBSubgrid" placed on the entity A's form
var conSubGrid = formContext.getControl("EntityBSubgrid");
var customer = null;
var supplier = null;
if (formContext.getAttribute("new_customer").getValue() != null) {
customer = formContext.getAttribute("new_customer").getValue()[0].id.replace("}", "").replace("{", "");
}
if (formContext.getAttribute("new_supplier").getValue() != null) {
supplier = formContext.getAttribute("new_supplier").getValue()[0].id.replace("}", "").replace("{", "");
}
//Create FetchXML for sub grid to filter records
//Set grid to empty if Customer or Supplier is null
var fetchXml = null;
if (customer == null && supplier == null) {
//Show related records
fetchXml = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
+ '<entity name="new_entityb">'
+ '<attribute name="new_entitybid" />'
+ '<order attribute="new_entitybid" descending="false" />'
+ '<filter type="and">'
+ '<condition attribute="new_customer" operator="null" />'
+ '<condition attribute="new_supplier" operator="null" />'
+ '</filter>'
+ '</entity>'
+ '</fetch>';
}
if (customer != null && supplier != null) {
//Show related records
fetchXml = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
+ '<entity name="new_entityb">'
+ '<attribute name="new_entitybid" />'
+ '<order attribute="new_entitybid" descending="false" />'
+ '<filter type="and">'
+ '<condition attribute="new_customer" operator="eq" uitype="account" value="' + customer + '" />'
+ '<condition attribute="new_supplier" operator="eq" uitype="account" value="' + supplier + '" />'
+ '<condition attribute="statecode" operator="eq" value="0" />'
+ '</filter>'
+ '</entity>'
+ '</fetch>';
}
if (customer != null && supplier == null) {
//Show related records
fetchXml = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
+ '<entity name="new_entityb">'
+ '<attribute name="new_entitybid" />'
+ '<order attribute="new_entitybid" descending="false" />'
+ '<filter type="and">'
+ '<condition attribute="new_customer" operator="eq" uitype="account" value="' + customer + '" />'
+ '<condition attribute="new_supplier" operator="null" />'
+ '<condition attribute="statecode" operator="eq" value="0" />'
+ '</filter>'
+ '</entity>'
+ '</fetch>';
}
if (customer == null && supplier != null) {
//Show related records
fetchXml = '<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">'
+ '<entity name="new_entityb">'
+ '<attribute name="new_entitybid" />'
+ '<order attribute="new_entitybid" descending="false" />'
+ '<filter type="and">'
+ '<condition attribute="new_customer" operator="null" />'
+ '<condition attribute="new_supplier" operator="eq" uitype="account" value="' + supplier + '" />'
+ '<condition attribute="statecode" operator="eq" value="0" />'
+ '</filter>'
+ '</entity>'
+ '</fetch>';
}
//conSubGrid.setFilterXml(fetchXml);
conSubGrid.getGrid().setParameter("fetchXml", fetchXml);
//Refresh grid to show filtered records only.
conSubGrid.refresh();
}
}).call(FilterOppGrid);
Lastly, I needed to refresh the subgrid once the form loads as the data in it aren’t what I was looking for. Only until performing some action in the subgrid does it show the filtered data. Obviously, that is not desirable. To refresh the subgrid, I came up with the below code:
var SubgridRefresh = window.SubgridRefresh || {};
(function () {
this.SubgridRefresh = function (executionContext) {
//---Get the form context using execution context
var formContext = executionContext.getFormContext();
//---Suppose there is a subgrid named "EntityBSubgrid" placed on the entity A's form
var conSubGrid = formContext.getControl("EntityBSubgrid");
var customer = null;
var supplier = null;
var conSubGridCount = null;
if (formContext.getAttribute("new_customer").getValue() != null && formContext.getAttribute("new_supplier").getValue() != null) {
customer = formContext.getAttribute("new_customer").getValue()[0].id.replace("}", "").replace("{", "");
supplier = formContext.getAttribute("new_supplier").getValue()[0].id.replace("}", "").replace("{", "");
}
if (customer != null || supplier != null) {
var conSubGridCount = conSubGrid.getGrid().getTotalRecordCount();
}
if (conSubGridCount > 0) {
conSubGrid.refresh();
}
}
}).call(SubgridRefresh);
And hopefully, that really is it.
References:
https://crm365blog.wordpress.com/2019/06/04/filter-subgrid-grid-in-dynamics-365/
https://blog.magnetismsolutions.com/blog/jaredjohnson/2018/07/12/using-fetchxml-with-xrm-webapi-retrievemultiplerecords-in-dynamics-365-v9
https://docs.microsoft.com/en-us/powerapps/developer/model-driven-apps/clientapi/reference/xrm-webapi/retrievemultiplerecords
Striving to Make Things Count 