Create a twin record with its children using a table relationship and field mapping
Few months ago I had a very interesting business requirement, in which I had to take the information of several fields from a primary table and the information of several fields from one of its child tables, in order to create the primary record in another custom table and also the children records in another custom table. In the end both custom tables have to be related.
The requirement was like this:
“As a sales manager, I want to create an order based on the approval of a quote. This should copy the data from the Quote and the Quote Lines to the Order and Order Lines”.
To clarify, these are custom tables, as the rest of the business case was much more complex than the Out of the Box functionality. Due to the limitations of the native product catalogue, it was better to opt for custom tables.
The first idea that came to my mind was Power Automate, but let’s be honest, if one or more fields need to be mapped to other fields or if the user wants to add new fields, then the cloud flow needs to be updated.
Don’t get me wrong, a cloud flow could solve the requirement, but I was looking for something that required less maintenance. And that’s where I found a hidden gem that made my life much easier for this requirement.
This hidden gem is a small piece of code (InitializeFromRequest class) that leverage the relationship and the Out of the box field mapping between two tables. In the future, if a new field needs to be copied from one table to another, the user just needs to create a new Field Mapping using the relationship between the two tables. No need to update the code, how cool is that!
Let me clarify a little with the following image:
We have a primary table with its child table (in my case is Quote and Quote lines), they have a 1:N relationship. On the other side we have the same situation with 2 other tables (in my case is Order and Order lines).
The idea is to create a relationship between the 2 primary tables and the same between the 2 child tables, and then configure the field mapping in these 2 relationships.
Having said that, I decided to create a small plugin to handle this requirement. So without further introduction let me show you how I solved it.
Set up the relationship
The first step is, of course, to create the custom tables and their fields, but I am going to skip this step because it is not part of the purpose of this blogpost.
Once the tables and fields are created, the next step is to establish the relationship between the primary tables and the same with secondary tables.
As I said in my case I want to create an Order when the Quote is approved and I also want to create the Order lines based on the Quote Lines, so the following are two images of how the relationship between these tables was set up:
Set up the field mapping
After both relationships have been established the next step is to set up the field mapping between the primary tables and the same with the secondary tables, and for this we have to use the old school field mapping, this is out of the box feature (yeah I’m using the classic UI 
):
Create the Plugin
Next step is to create the plugin, What does it look like? well I’ve tried to keep the plugin as simple as possible and delegate all heavy logic to other procedures and functions.
So if you want to learn first how to set up a solution with two projects, let’s say a first project called Main to handle plugins and Custom APIs and a second project called Core to handle all the heavy logic in the handler classes, then check out this blogpost, so because I’m skipping those steps on how to set up these two projects, I’m going to make this blogpost simpler.
This plugin must be created in the Main project and must be configured in the SetStateDynamicEntity message:
public class CreateOrderFromQuote : IPlugin
{
public void Execute(IServiceProvider serviceProvider)
{
ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService service = factory.CreateOrganizationService(context.UserId);
EntityReference quoteRef = new EntityReference(context.PrimaryEntityName, context.PrimaryEntityId);
var quoteHandler = new Core.Handlers.QuoteHandler(tracingService, service);
quoteHandler.createOrderFromQuote(quoteRef, context);
}
}
The Execute method calls the createOrderFromQuote procedure that is in QuoteHandler class (Core project). This is the most important method because it calls all other functions.
Create the handler class
Before go further it’s worth to mention that all the following code is in the same QuoteHandler class.
I would also like to point out that in the following method we have the OrganizationRequestCollection object, because with this object we can execute the creation of the Order and its lines in a single request. This is much better because the performance is much better than if we create the Order and its lines one by one inside a loop:
public void CreateOrderFromQuote(EntityReference quoteRef, IPluginExecutionContext context)
{
_tracingService.Trace(“Start QuoteHanlder createOrderFromQuote”);
OrganizationRequestCollection requestCollection = new OrganizationRequestCollection();
Entity order = _CreateOrderFromQuote(quoteRef, requestCollection);
EntityCollection quoteLinesColl = GetQuoteLinesByQuoteId(quoteRef);
requestCollection = CreateOrderLinesFromCollection(quoteLinesColl, requestCollection, order);
Helpers.Common.executeBatchRequest(_service, _tracingService, requestCollection);
_tracingService.Trace(“End QuoteHanlder createOrderFromQuote”);
}
Now in the next piece of code we have two functions that call a third one called createEntityfromMapping. The cool part in here is the InitializeFromRequest object, this is the hidden gem I was talking about at the beginning of this blogpost, this object allows us to create an “In Memory” record with the same field values of the source record, and for this it uses the field mapping between the two tables, that’s why the first step must be to establish the relationships between the tables and establish the field mapping as well.
private Entity _CreateOrderFromQuote(EntityReference quoteRef, OrganizationRequestCollection requestCollection)
{
Entity order = Helpers.Common.createEntityfromMapping(_service, quoteRef, “p365i_order”, TargetFieldType.All);
order.Id = Guid.NewGuid();
requestCollection.Add(new CreateRequest() { Target = order });
return order;
}
private OrganizationRequestCollection CreateOrderLinesFromCollection(EntityCollection quoteLinesColl, OrganizationRequestCollection requestCollection, Entity order)
{
foreach (var quoteLine in quoteLinesColl.Entities)
{
Entity orderLine = Helpers.Common.createEntityfromMapping(_service, quoteLine.ToEntityReference(), “p365i_orderline”, TargetFieldType.All);
orderLine.Attributes.Add(“p365i_orderid”, order.ToEntityReference());
requestCollection.Add(new CreateRequest() { Target = orderLine });
}
return requestCollection;
}
public static Entity createEntityfromMapping(IOrganizationService service, EntityReference sourceEntityRef, String targetEntityName, TargetFieldType targetFieldType)
{
InitializeFromRequest initializeFromRequest = new InitializeFromRequest();
initializeFromRequest.EntityMoniker = sourceEntityRef;
initializeFromRequest.TargetEntityName = targetEntityName;
initializeFromRequest.TargetFieldType = targetFieldType;
InitializeFromResponse initializeFromResponse = (InitializeFromResponse)service.Execute(initializeFromRequest);
return initializeFromResponse.Entity;
}
The following function allows us to retrieve all the quote lines in a collection that we will later use to create the order lines:
private EntityCollection GetQuoteLinesByQuoteId(EntityReference quoteRef)
{
string fetchXML = $@”<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>
<entity name=’p365i_quoteline’>
<attribute name=’p365i_quotelineid’ />
<filter type=’and’>
<condition attribute=’p365i_quoteid’ operator=’eq’ uitype=’p365i_quote’ value='{quoteRef.Id}’ />
</filter>
</entity>
</fetch>”;
return Helpers.Common.getDatabyFetchXML(_service, fetchXML);
}
Then, in the next function, we have to use the collection of quote lines from the previous function to create all the order lines. Again I use the InitializeFromRequest object, which uses the Field Mapping between the two tables (quote lines and order lines):
private OrganizationRequestCollection CreateOrderLinesFromCollection(EntityCollection quoteLinesColl, OrganizationRequestCollection requestCollection, Entity order)
{
foreach (var quoteLine in quoteLinesColl.Entities)
{
Entity orderLine = Helpers.Common.createEntityfromMapping(_service, quoteLine.ToEntityReference(), “p365i_orderline”, TargetFieldType.All);
orderLine.Attributes.Add(“p365i_orderid”, order.ToEntityReference());
requestCollection.Add(new CreateRequest() { Target = orderLine });
}
return requestCollection;
}
Using the createEntityfromMapping handler function, we created the Order and its lines “In Memory”. Now we can process the OrganizationRequestCollection object with another handler function called ExecuteBatchRequest. This function uses the executeMultipleRequest object and creates the orders and its lines in just one request.
So the following function can be in the same QuoteHandler class, but I recommend to create another class called common.cs and put this method there, this class is also in the same Core project:
public static void ExecuteBatchRequest(IOrganizationService service, ITracingService tracingService, OrganizationRequestCollection requestCollection, int split = 50)
{
String exceptionMessage = String.Empty;
List<List<OrganizationRequest>> splittedLists = requestCollection.ToList().ChunkBy(split);
tracingService.Trace($”Splitted {requestCollection.Count} into {splittedLists.Count} List with split setting of {split}”);
int i = 1;
foreach (List<OrganizationRequest> listRequests in splittedLists)
{
OrganizationRequestCollection newRequestCollection = new OrganizationRequestCollection();
newRequestCollection.AddRange(listRequests);
ExecuteMultipleRequest execRequest = new ExecuteMultipleRequest()
{
Settings = new ExecuteMultipleSettings()
{
ReturnResponses = true,
ContinueOnError = true
},
Requests = newRequestCollection
};
try
{
tracingService.Trace($”Execute Multiple Request {i} of {splittedLists.Count}”);
ExecuteMultipleResponse responseWithResults = (ExecuteMultipleResponse)service.Execute(execRequest);
tracingService.Trace($”Multiple Request Executed. Is faulted : {responseWithResults.IsFaulted}”);
i++;
}
catch (Exception ex)
{
tracingService.Trace($”Error {ex}”);
exceptionMessage += ex.Message;
}
finally
{
if (!String.IsNullOrEmpty(exceptionMessage))
{
tracingService.Trace($”Exception: {exceptionMessage}”);
}
}
}
}
Testing the Quote Approval
Now we have to go to our model driven app, open a quote and first look at the related quote line records, for this example there are 19 quote line records:
Now change the status reason to approved, remember that these are custom tables and the update of the status reason field could be manual or automatic:
After the quote is approved, the Order is created:
And the order lines are also created: