Lookup filtering with connection roles

Here is what I wanted to set up today:

There is a custom SkillSet entity that has an “Advisor” field. That field is a lookup to the out-of-the-box contact entity. However, unlike with a regular lookup, I want that “Advisor” field to only display contacts which are connected to the current skillset through the “Expert” connection role.

In other words, imagine I have the skillset record below, and it has a couple of connected contacts (both in the “Expert” role):

I want only those two to show up in the lookup selector when I am choosing a contact for the “Advisor” field:

Even though there are, of course, other contacts in the system.

---

Actually, before I continue, let’s talk about connections and connection roles quickly. There is not a lot I can say in addition to what has already been written in the docs:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/configure-connection-roles

Although, if you have not worked with the connections before, there is something to keep in mind

Connection roles can connect records of different types, but there is neither “source” nor “target” in the role definition

It’s not as if there were a source entity, a target entity, and a role. It’s just that there is a set of entities, and you can connect any entity from that set to any other entity in that set using your connection role:

Which may lead to some interesting effects – for example, I can have a SkillSet connected to a Contact as if that SkillSet were an expert, which does not really make sense:

But, of course, I can get a contact connected to a skillset in that role, and that makes much more sense:

 

---

That’s all great, but how do I filter the lookup now that I have an “Expert” role, and there are two contacts connected to the Power Platform skillset through that role?

That’s where we need to use addCustomView method

Why not to use addCustomFilter?

The first method (addCustomView) accepts complete fetchXml as one of the parameters, which means we can do pretty much anything there. For example, we can link other entities to define more advanced conditions.

The second method (addCustomFilter) accepts a filter to be applied to the existing view. We cannot use this method to define a filter on the linked entities.

In case with the connections, what we need is a view that starts with the contacts and that only displays those which are connected to the selected SkillSet record in the “Expert” role like this:

So… You will find a link to the github repo below, but here is the script:

function formOnLoad(executionContext)
{

	var context = executionContext.getFormContext();
	
	var viewId = "bc80640e-45b7-4c51-b745-7f3b648e62a1";
	var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>"+
	  "<entity name='contact'>"+
		"<attribute name='fullname' />"+
		"<attribute name='telephone1' />"+
		"<attribute name='contactid' />"+
		"<order attribute='fullname' descending='false' />"+
		"<link-entity name='connection' from='record2id' to='contactid' link-type='inner' alias='ce'>"+
		  "<link-entity name='connectionrole' from='connectionroleid' to='record2roleid' link-type='inner' alias='cf'>"+
			"<filter type='and'>"+
			  "<condition attribute='name' operator='eq' value='Expert' />"+
			"</filter>"+
		  "</link-entity>"+
		  "<link-entity name='ita__skillset' from='ita__skillsetid' to='record1id' link-type='inner' alias='cg'>"+
			"<filter type='and'>"+
			  "<condition attribute='ita__name' operator='eq' value='" + context.getAttribute("ita__name").getValue() + "' />"+
			"</filter>"+
		  "</link-entity>"+
		"</link-entity>"+
	  "</entity>"+
	"</fetch>";
	
	var layoutXml = "<grid name='resultset' object='2' jump='fullname' select='1' preview='0' icon='1'>"+
	  "<row name='result' id='contactid'>"+
		"<cell name='fullname' width='300' />"+
	  "</row>"+
	"</grid>";
	
    context.getControl("ita__advisor").addCustomView(viewId, "contact", "Experts", fetchXml, layoutXml, true);
}

 

What’s happening in the script is:

• It defines fetchXml (which I downloaded from the Advanced Find)
• It dynamically populates skillset name in the fetchXml condition
• Then it defines layout xml for the view. I used View Layout Replicator plugin in XrmToolBox to get the layout quickly:
• 
• Finally, the script calls “addCustomView” on the lookup control

 

And, of course, that script has been added to the “onLoad” of the form:

Now, I used connections above since that’s something that came up on the current project, but, of course, the same technique with custom views can be applied in other scenarios where you need to create a custom lookup view.

Either way, if you wanted to try it quickly, you will find unmanaged solution file in the git repo below:

https://github.com/ashlega/ItAintBoring.ConnectionRoleFilteredLookup

Have fun!