If you want to filter one lookup column by another lookup column, you might think that Model Driven Apps have a simple solution for you: just check a box and you’re done. But is it really that easy? Well, it depends.
The checkbox option only works for 1:N or N:1 relationships between the two lookup columns. These are the most common types of relationships, but not the only ones. What if you have a many-to-many relationship, where each record in one entity can be related to multiple records in another entity, and vice versa? How do you filter your lookup columns then?
In this blog post, I will show you how to handle this scenario using some custom code and a fetch XML query. You will learn how to create a custom view for your lookup column that filters the records based on the value of another lookup column. This way, you can achieve the same functionality as the checkbox option, but for many-to-many relationships. Let’s get started!
Scenario
Consider the hypothetical scenario of WorkWithPower-Enterprise. This enterprise comprises numerous departments, each with distinct job titles. The company aims to implement a Model Driven App for a customized HR Management tool (similar to Dynamics). When a potential new employee is being onboarded, the HR team must input the department and job title information. However, the challenge lies in the fact that there are 92 different job titles across the enterprise. While it’s possible to not filter these job titles by department, it doesn’t provide an optimal user experience.
To summarize it in a picture how our datamodel looks like in out scenario:
In my quest to resolve this issue, I embarked on a deep dive into the digital ocean of the internet. I metaphorically stepped through the gateway of the PowerApps Community, navigating through a labyrinth of articles, trial and error, and social media detours. After an exhaustive search, I stumbled upon a concise article that shed light on the solution.
But what was this magic Thing that saved my life (metaphorically)?
Solution
One litte function called addCustomView(). Pared with a little JS-Code and it will work.
This little helper creates a CustomView which can be defined with code and must not be created in the View-Designer.
In the next few paragraphs we design the function step by step but we firstly we copy the Microsoft Learn function here. You could skip to to the end if you want to. There is the full code without explanation
formContext.getControl(arg).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, isDefault)We will start with the different parameters inside the addCustomView.
viewID
The viewID is the ID of the View you will create. The ID must be unique but that is simpler as it sounds. You can copy this here:
var viewId = "{00000000-0000-0000-0000-000006102020}";It is important, that you not delete any of the characters inside of the surrounding quotation marks. You can replace any of the numbers with any number you want. I used a date which means a lot to me.
This GUID will work but isn’t a valid GUID. To create a valid GUID you would need an extra tool like guidgen.exe.
entityName
This is the structural name of the Table/Entitiy you want to filter. In our Scenario it is jobtitle -> wwe_jobtitle.
viewDisplayName
The viewDisplayName is as it sound, the Name of the Lookup you create with that code. Must not be Unique but it should be. Just to be safe you don’t irritate yourself and others.
var viewDisplayName = "Active Jobtitles in the Department";fetchXML
In this part you will need a functional fetchXML to retrieve the values you need. I will not go though the details how you create one, because thats part of another post.
var fetchXml = "\
<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
"<entity name='wwe_jobtitle'>\
<attribute name='wwe_title' />\
<attribute name='wwe_jobtitleid' />" +
"<order attribute='wwe_title' descending='false' />" +
"<link-entity name='wwe_jobtitle_wwe_department' from='wwe_jobtitleid' to='wwe_jobtitleid' intersect='true'>" +
"<filter type='and'>" +
"<condition attribute='wwe_departmentid' operator='eq' value='"+ department[0].id + "' />" +
"</filter>\
</link-entity>\
</entity>\
</fetch>Two things to mention:
- I use \ and + to split the code to make it more human readable in an IDE
- department[0].id is part of the fullcode I will explain later
To create the FetchXML you want, I suggest the XRMToolbox.
layoutXML
The layoutXML „designs“ the LookUp. It let you decide which fields you want to show in the Lookup.
var layoutXml = "<grid name='resultset' object='10287' jump='wwe_jobtitleid' select='1' icon='1' preview='1'>"+
"<row name='result' id='wwe_jobtitleid'>"+
"<cell name='wwe_title' width='150'/>"+
"</row> </grid>";- Replace wwe_jobtitleid
- Replace object with the ID of your table
- Replace wwe_title with the name of your column
- You can add more cells if you want to
isDefault
isDefault decides if the View you generate is the default view a user will see if the select the LookUp. The values are true and false.
Full Code
this.setCustomView= function(executionContext) {
var formContext = executionContext.getFormContext();
var department = formContext.getAttribute("wwe_department").getValue();
if (department== null) {
return;
}
var fetchXml = "\
<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>" +
"<entity name='wwe_jobtitle'>\
<attribute name='wwe_title' />\
<attribute name='wwe_jobtitleid' />" +
"<order attribute='wwe_title' descending='false' />" +
"<link-entity name='wwe_jobtitle_wwe_department' from='wwe_jobtitleid' to='wwe_jobtitleid' intersect='true'>" +
"<filter type='and'>" +
"<condition attribute='wwe_departmentid' operator='eq' value='"+ department[0].id + "' />" +
"</filter>\
</link-entity>\
</entity>\
</fetch>
var layoutXml = "<grid name='resultset' object='10287' jump='wwe_jobtitleid' select='1' icon='1' preview='1'>"+
"<row name='result' id='wwe_jobtitleid'>"+
"<cell name='wwe_title' width='150'/>"+
"</row> </grid>";
var viewId = "{00000000-0000-0000-0000-000006102020}";
var viewDisplayName = "Active Jobtitles in the Department";
formContext.getControl("wwe_jobtitle").addCustomView(viewId, "wwe_jobtitle", viewDisplayName, fetchXml, layoutXml, true);
}
One more Explanation
var formContext = executionContext.getFormContext();
var department = formContext.getAttribute("wwe_department").getValue();
if (department== null) {
return;
}We need the formContext to interact with any data of the form.
Then we try to get the Value of the department. This returns an array which explains why we need the department[0].id to retrieve the departmentid.
If the array is empty we stop the function, if not we set the CustomView.
Last Step
Upload the JavaScript to your Form. Then add the Function on the OnChange Event of the Lookup Field you want as a trigger. In our example we add the setCustomView on the OnChange Event of the Department Lookup. And don’t forget to pass the excution context.
Conclusion
If you want to filter many to many relationships without creating a manual table this Code is the best way to do it.