Filter related records using addPreSearch?
Within a Model Driven App there’s frequently a requirement to filter the values in a lookup field dynamically. To do this we can use the addPreSearch() method in the Client API. Let’s see how this interacts with the recently documented ’exists’/‘any’ FetchXml functionality.
Basics
Let’s say we want to dynamically apply an additional filter to a contact lookup to only show contacts with a first name beginning with ‘P’.
We might use the following JavaScript library on a model driven form. This is very similar to the MS documentation example.
function onLoad(executionContext) {
const formContext = executionContext.getFormContext();
formContext.getAttribute("pgc_contact1").controls.forEach(c =>
c.addPreSearch(beginsWithContactFilter)
);
}
function beginsWithContactFilter(executionContext) {
const control = executionContext.getEventSource();
const filter = "<filter><condition attribute='firstname' operator='begins-with' value='P' /></filter>";
control.addCustomFilter(filter);
}
We can check this filter has been applied by looking in the browser at the network trace immediately after the lookup magnifying glass button has been pressed:
Decoding this url, using a site such as urldecoder.org gives:
We can see that an additional <filter> tag with the condition on the firstname column has been added.
Exists filtering
Let’s experiment to see if the new filtering with link-type any is supported.
We can update the filter to only show contacts where the PrimaryContactId lookup column of at least one account exists that has it’s Name column equal to ‘Cole Corporation’.
<fetch>
<entity name='contact'>
<attribute name='fullname' />
<filter>
<link-entity name='account' from='primarycontactid' to='contactid' link-type='any'>
<filter type='and'>
<condition attribute='name' operator='eq' value='Cole Corporation' />
</filter>
</link-entity>
</filter>
</entity>
</fetch>
We can check this FetchXml query is valid outside of the browser by using the FetchXml Tester or the latest release of the FetchXml Builder tools in XrmToolbox.
Now let’s update our JavaScript library to use the new filter criteria:
function onLoad(executionContext) {
const formContext = executionContext.getFormContext();
formContext.getAttribute("pgc_contact2").controls.forEach(c =>
c.addPreSearch(coleCorpContactFilter)
);
}
function coleCorpContactFilter(executionContext) {
const control = executionContext.getEventSource();
const filter = [
"<filter>",
"<link-entity name='account' from='primarycontactid' to='contactid' link-type='any'>",
"<filter type='and'>",
"<condition attribute='name' operator='eq' value='Cole Corporation' />",
"</filter>",
"</link-entity>",
"</filter>",
].join("");
control.addCustomFilter(filter);
}
We can see that the full list of contacts (as returned by the standard lookup view) is returned!! This is not what was expected! Decoding and examining the FetchXml requested we can see that the additional <filter> tag has not been added.
So, we can deduce that the link-entity element is not supported within a filter element when using addPreSearch in model driven apps.
Full Solution
The full unmanaged solution with a very simple model driven app is available on my GitHub.
Workaround
The following workaround can be used to overcome this limitation - Kudos to Oliver Flint for the idea.
We’re using addCustomView() and setDefaultView() to set the view and all of the FetchXml, and not using addPreSearch() at all.
function onLoad(executionContext) {
const formContext = executionContext.getFormContext();
const viewId = "{00000000-0000-0000-0000-000000F00001}";
const viewDisplayName = "Contacts at Cole Corp";
const entityName = "contact";
const fetchXml = [
'<fetch version="1.0" mapping="logical">',
'<entity name="contact">',
'<attribute name="fullname" />',
'<attribute name="parentcustomerid" />',
'<attribute name="address1_city" />',
'<attribute name="address1_telephone1" />',
'<attribute name="telephone1" />',
'<attribute name="emailaddress1" />',
'<attribute name="contactid" />',
'<attribute name="fax" />',
'<attribute name="address1_name" />',
'<attribute name="address1_fax" />',
'<filter type="and">',
'<condition attribute="statecode" operator="eq" value="0" />',
'<link-entity name="account" from="primarycontactid" to="contactid" link-type="any">',
'<filter type="and">',
'<condition attribute="name" operator="eq" value="Cole Corporation" />',
'</filter>',
'</link-entity>',
'</filter>',
'<order attribute="fullname" descending="false" />',
'</entity>',
'</fetch>',
].join("");
const layoutXml = [
'<grid name="resultset" object="2" jump="fullname" select="1" icon="1" preview="1">',
'<row name="result" id="contactid">',
'<cell name="fullname" width="300" />',
'<cell name="emailaddress1" width="200" />',
'<cell name="telephone1" width="125" />',
'<cell name="parentcustomerid" width="150" />',
'<cell name="address1_city" width="100" />',
'<cell name="address1_telephone1" width="125" />',
'<cell name="fax" ishidden="1" />',
'<cell name="address1_name" ishidden="1" />',
'<cell name="address1_fax" ishidden="1" />',
'<cell name="contactid" ishidden="1" />',
'</row>',
'</grid>',
].join("");
formContext.getAttribute("pgc_contact3").controls.forEach(c => {
c.addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, true);
c.setDefaultView(viewId);
});
}
Summary
We can’t use the new any filters within model driven apps dynamic filtering at the current time in combination with addPreSearch().
This seems to be an arbitrary restriction. Please consider commenting on this Power Apps Community [forum post]](https://powerusers.microsoft.com/t5/Power-Apps-Pro-Dev-ISV/Allow-the-new-exists-style-FetchXml-filters-to-be-used-within/m-p/2728530#M10249) if you also hit this issue.
There is a workaround, but it is quite cumbersome.
