Recently we had a requirement to create a marketing list with members (contact) which are not part of any other marketing list through Advanced Find.
In version 9.0 we have “Does not contain data” filter added in Advanced Find. However, it works only in case of 1 – N relationship.
Here in our case, it is an N-N relationship between Contact and Marketing List.
The solution we implemented was following –
We asked the user to select the following condition in their Advanced Find query.
Which will have the following fetch XML à
And in our Retrieve Multiple Plugin, we added a new filter condition and replaced link entity condition as shown below for implementing the outer join.
Below is the sample code for the plugin that achieves this. The plugin is Registered on Pre Retrieve Multiple of Contact.
The code –
public class PreRetrieveMultipleML : IPlugin { public void Execute(IServiceProvider serviceProvider) { // Obtain the execution context from the service provider. IPluginExecutionContext context = (IPluginExecutionContext) serviceProvider.GetService(typeof(IPluginExecutionContext)); // Obtain the organization service reference. IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory)); IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId); ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService)); // The InputParameters collection contains all the data passed in the message request. if (context.InputParameters.Contains("Query") && context.InputParameters["Query"] is QueryExpression && context.MessageName == "RetrieveMultiple") { var query = (QueryExpression)context.InputParameters["Query"]; tracingService.Trace("Inside Plugin"); if (query != null && query.EntityName == "contact") { foreach (var linkedEntity in query.LinkEntities.Where( a => a.LinkFromAttributeName.Equals("contactid") && a.LinkToAttributeName.Equals("entityid") && a.LinkToEntityName.Equals("listmember") && a.LinkEntities.Count == 1 && a.LinkEntities[0].LinkCriteria != null)) { if (linkedEntity.LinkEntities[0].LinkCriteria.Conditions.Count == 1 && linkedEntity.LinkEntities[0].LinkCriteria.Conditions[0].AttributeName.Equals("listid") && linkedEntity.LinkEntities[0].LinkCriteria.Conditions[0].Operator == ConditionOperator.Null) { // remove the existing link entity query.LinkEntities.Remove(linkedEntity); // create a new link entity LinkEntity doesNotContainLinkEntity = new LinkEntity() { EntityAlias = "ml", LinkFromEntityName = "contact", LinkToEntityName = "listmember", LinkFromAttributeName = "contactid", LinkToAttributeName = "entityid", JoinOperator = JoinOperator.LeftOuter }; // add the newly created link entity query.LinkEntities.Add(doesNotContainLinkEntity); // create a new condition expression var doesNotContainCondition = new ConditionExpression(); doesNotContainCondition.AttributeName = "listid"; doesNotContainCondition.Operator = ConditionOperator.Null; doesNotContainCondition.EntityName = "ml"; //create a new filter condition var doesNotContainFilterExpression = new FilterExpression(); doesNotContainFilterExpression.AddCondition(doesNotContainCondition); // add this new filter in query query.Criteria.AddFilter(doesNotContainFilterExpression); break; } } } } } }
We can update the plugin’s code to be used for any other N-N relationship similarly.
Hope it helps..