In Marketing Lists of Dynamics 365, we have an option to associate Contacts/Accounts using a Dynamic Query rather than adding them manually. But this isn’t a case with other entities. We do encounter scenarios where we need to associate or disassociate set of records based on a specific criterion. Let assume Contacts from City New York should be added to specific entity.
Table rows are associated to each other using lookup columns on the related table row. The simplest way to associate two rows in a one-to-many relationship is to use an EntityReference to set the value of a lookup column on the related row. The simplest way to disassociate two rows in a one-to-many relationship is to set the value of the lookup column to null.
Relationships using a many-to-many also depend on lookup columns on the intersect entity that supports the many-to-many.
Steps to be followed:
- Create a 1:N relationship with Rollup Query.
Using Rollup Query, we can define conditions and can retrieve multiple records.
Defined criteria on Rollup Query is stored in a field “fetchxml” as a FetchXML Query.
2. Add Rollup Query lookup field on the form.
3. Create and associate Rollup Query.
4. Use a button click as an action to call Pugin to Associate records.
Retrieve the base record and then get Rollup Query using plugin:
Entity baseEntity = service.Retrieve(“baseEntitySchemaNa”, entity.Id, new ColumnSet(true));
EntityReference rollQuery = baseEntity.GetAttributeValue<EntityReference>(“pcms_rollupquery”);
If Rollup Query has a value, retrieve attributes of the record. Then get the FetchXML associated with the Rollup Query.
Entity goalrollupquery = service.Retrieve(“goalrollupquery”, rollQuery.Id, new ColumnSet(true));
String fetchXML = goalrollupquery.GetAttributeValue<String>(“fetchxml”);
Using FetchExpression and RetrieveMultiple, we can fetch records and add them to an Entity Collection.
FetchExpression retrieveRecords = new
EntityCollection retrievedRecords = service.RetrieveMultiple(retrieveRecords);
To associate table rows with these APIs you need three things:
- An entity reference to the row you want to associate
- The name of the relationship
- One or more references that you want to associate the table row to
Using Associate Method, records can be associated from the Entity Collection, loop for all the records:
foreach (Entity ent in retrievedProspects.Entities)
Guid prospectID = ent.Id;
string relationshipName = “provide1:NRelationship”;
string firstEntityName = “provide base entity schema name”;
Guid firstEntityId = entity.Id;
string secondEntityName = “provide child entity schema name”;
Guid secondEntityId = prospectID;
Relationship relationship = new Relationship(relationshipName);
EntityReferenceCollection relatedEntities = new EntityReferenceCollection();
EntityReference secondaryEntity = new EntityReference(secondEntityName, secondEntityId);
service.Associate(firstEntityName, firstEntityId, relationship, relatedEntities);
To remove them, retrieve list of records associated with the base Entity record and then use “Disassociate” Method to remove the association.