In this post, we’ll talk about an example where we need to retrieve N:N related records and code snippet for the same.
We are using ClickDimensions component in D365 for marketing purpose. We came across a requirement where we were supposed to disassociate the related records(Marketing Lists) from a record(Email Send a ClickDimensions entity) based on a condition, considering we have N:N relationship between the 2 entities Email Send and Marketing List.
Here, we have to remember 3 things in particular:
- Primary key schema name of Email Send(cdi_emailsend) entity: cdi_emailsendid
- Primary Key schema name of Marketing List(list) entity: listid
- N:N relationship schema name: cdi_emailsend_suppressed_list
Here’s the code snippet to retrieve the related Marketing Lists of a particular Email Send record:
QueryExpression query = new QueryExpression("list"); //Name of the entity to be passed whose records need to be retrieved var nIsToNRelationshipName="cdi_emailsend_suppressed_list"; //schema name of N:N relationship query.ColumnSet = new ColumnSet(true); var link = query.AddLink(nIsToNRelationshipName, "listid", "listid"); //Method Signature: AddLink("N:N schema name to be passed", "Primary key ID schema name of entity record to be retrieved", "schema name of attribute in the N:N relationship") Guid emailSendID = new Guid("79121c07-220e-41ae-9710-96b880973e6c"); //sample ID of Email Send record for which we are retrieving related records link.LinkCriteria = new FilterExpression() { Conditions = { new ConditionExpression("cdi_emailsendid", ConditionOperator.Equal, emailSendID) // Filter condition to get related marketing lists of an Email Send record } }; EntityCollection collRecords = service.RetrieveMultiple(query); if (collRecords != null && collRecords.Entities != null && collRecords.Entities.Count > 0) { foreach (var entity in collRecords.Entities) { // Do something } }
After retrieving the related Marketing List records, to disassociate them from the Email Send record, use the below code:
EntityCollection collRecords = service.RetrieveMultiple(query); if (collRecords != null && collRecords.Entities != null && collRecords.Entities.Count > 0) { EntityReferenceCollection collection = new EntityReferenceCollection(); foreach (var entity in collRecords.Entities) { var reference = new EntityReference("list", entity.Id); collection.Add(reference); //Create a collection of entity references } Relationship relationship = new Relationship("cdi_emailsend_suppressed_list"); //schema name of N:N relationship service.Disassociate("cdi_emailsend", emailSendID, relationship, collection); //Pass the entity reference collections to be disassociated from the specific Email Send record }
So, here’s the complete code for retrieving the related N:N records and disassociating them from a particular record.
QueryExpression query = new QueryExpression("list"); //Name of the entity to be passed whose records need to be retrieved var nIsToNRelationshipName="cdi_emailsend_suppressed_list"; //schema name of N:N relationship query.ColumnSet = new ColumnSet(true); var link = query.AddLink(nIsToNRelationshipName, "listid", "listid"); //Method Signature: AddLink("N:N schema name to be passed", "Primary key ID schema name of entity record to be retrieved", "schema name of attribute in the N:N relationship") Guid emailSendID = new Guid("79121c07-220e-41ae-9710-96b880973e6c"); //sample ID of Email Send record for which we are retrieving related records link.LinkCriteria = new FilterExpression() { Conditions = { new ConditionExpression("cdi_emailsendid", ConditionOperator.Equal, emailSendID) // Filter condition to get related marketing lists of an Email Send record } }; EntityCollection collRecords = service.RetrieveMultiple(query); if (collRecords != null && collRecords.Entities != null && collRecords.Entities.Count > 0) { EntityReferenceCollection collection = new EntityReferenceCollection(); foreach (var entity in collRecords.Entities) { var reference = new EntityReference("list", entity.Id); collection.Add(reference); //Create a collection of entity references } Relationship relationship = new Relationship("cdi_emailsend_suppressed_list"); //schema name of N:N relationship service.Disassociate("cdi_emailsend", emailSendID, relationship, collection); //Pass the entity reference collections to be disassociated from the specific Email Send record }
Also, to associate a Marketing List record to an Email Send record, we can use below sample code:
Guid marketingListID= new Guid("79abbc07-220e-41ae-9710-96b540973e6c"); //GUID of marketing list to be associated AssociateRequest request = new AssociateRequest { Target = new EntityReference("cdi_emailsend", emailSendID), RelatedEntities = new EntityReferenceCollection { new EntityReference("list", marketingListID) }, Relationship = new Relationship("cdi_emailsend_suppressed_list") }; service.Execute(request);
Hope it helps !!