Rise to distinction
Time & tide wait for none
Integrate LOB data details in CRM without loading large data volumes (aka.. virtual entity 8.x onwards)
Posted by on August 24, 2015
Synopsis
Every integration solution has its own set of requirements making it stand different from others. However more or less in almost all CRM integrations scenarios exist where data is migrated into CRM entities only to be available readonly irrespective of user’s constellations and privileges. One such usual requirement is details of sales from ERP to be made available in CRM. Sales details are typically large volume of records with further reference links within sales, for example end-user sales through distributors and retailers.
Scenario:
Users wish to see sales details, within CRM, where periodic sales aggregates are maintained on distributors (accounts). As part of data integration (periodic batch), the aggregates are kept in-sync, which are good enough for reporting needs within CRM. However, at times more curious audience wish to see details for sales within pre-defined periods e.g, weekly, fort-nightly, monthly, quarterly etc.
Model diagram
Solutions:
Two typical soltuions for this problem are:
Data integration
- Integrate sales details in custom entity referenced by specific distributor & periodic sales.
Custom UI
- Custom pages / contorls with lists to show data from ERP, in CRM forms
No matter which of above is taken, this data is readonly for all CRM users, including system administrors.
An alternate solution with CRM out-of-box views
- Power of Plugins & Business logic extensibility: I recently designed a simpler solution without any custom UI but a couple of plugins to address this scenario. This is a typical “lazy approach as a solution designer” 🙂 :
Model and UI
- Design a custom entity representing sales period for account, so it is N-1 to account. This entity has the attributes where data is integrated for aggregated sales by period for an account
- Design a custom entity representing sales details within a period, so it is N-1 to sales period. This entity has all those attributes which hold values in usual data integration solution. However in this solution data is not integrated into this entity.
- Adjust existing or create new security roles in which users can only read these entities (deep prefered)
- Add a subgrid of sales details in sales period form, preferred to create a custom view with needed column. Make sure to show only related records of sales period in subgrid.
Plugins
1. RetrieveMultiple: Write a retrieve multiple plugin & register on Post Operation stage (40) of RetrieveMultiple messsage on sales details entity. The theme of this plugin is
- Intercept view query execution, take query from InputParameters
- Fetch records from extenral system based on account and sales period
- Fill EntityCollection in OutputParameters with data from external system to return to view
Sample code as below (not complete code):
if (executionContext.MessageName == "RetrieveMultiple")
{
Guid periodId = Guid.Empty;
if (executionContext.InputParameters.ContainsKey("Query"))
{
object inputParam = executionContext.InputParameters["Query"];
QueryExpression query = inputParam as QueryExpression;
if (query != null)
{
if (query.Criteria != null && query.Criteria.Conditions.Count > 0)
{
ConditionExpression condition = query.Criteria.Conditions.Where(item => item.AttributeName == "cst_periodid").FirstOrDefault();
if (condition != null && condition.Operator == ConditionOperator.Equal && condition.Values != null && condition.Values.Count == 1)
{
object value = condition.Values[0];
if (value != null)
{
periodId = new Guid(value.ToString());
}
}
}
}
//need to make sure that subgrid is being loaded for the parent record, this will thus skip if the query is not triggered for a specific account
if (periodId != Guid.Empty)
{
if (executionContext.OutputParameters.ContainsKey("BusinessEntityCollection"))
{
object outputParam = executionContext.OutputParameters["BusinessEntityCollection"];
EntityCollection entityCollection = outputParam as EntityCollection;
if (entityCollection != null && entityCollection.EntityName == cst_sales.EntityLogicalName)
{
//Here based on period, fetch its parent account and fetch sales details records from external system based on account-salesperiod combination
List list = new List();
for (int i = 0; i < 33; i++)
{
cst_sales mock = new cst_sales
{
cst_salesid = Guid.NewGuid(), //this id needs to be translated from record key of external system, the reverse transalation of this id will be performed when user clicks a record in view to load in CRM form, so in Retrieve message the external system can be queried based on those keys
cst_name = "Mock " + i,
cst_DateTime = DateTime.Now,
cst_WholeNumber = i + 1000,
cst_OptionSet = new OptionSetValue((int)cst_salescst_OptionSet.Item2)
};
if (periodId != Guid.Empty)
{
mock["cst_periodid"] = new EntityReference(cst_period.EntityLogicalName, periodtId) { Name = "Mock Period" };
}
list.Add(mock);
}
if (query.PageInfo == null)
{
list.ForEach(item => entityCollection.Entities.Add(item));
}
else
{
int pageNumber = query.PageInfo.PageNumber;
int recordsPerPage = query.PageInfo.Count;
list.Skip((pageNumber - 1) * recordsPerPage).Take(recordsPerPage).ToList().ForEach(item => entityCollection.Entities.Add(item));
if (entityCollection.Entities.Count < recordsPerPage)
{
entityCollection.MoreRecords = false;
}
else if (list.Skip(pageNumber * recordsPerPage).Take(1).Count() > 0)
{
entityCollection.MoreRecords = true;
}
}
}
}
}
}
}
2. Retrieve: Write a retrieve plugin & register on both Pre & Post Operation stages (20, 40) of Retrieve messsage on sales details entity. The theme of this plugin is
- In Pre-Operation:
-
- Intercept record id, (of mock record as above), and put it into a shared vaiable
- Read or Create (if not found) a “DUMMY” record of sales detail and set its guid in Target InputParameter.
-
- This is needed because the Mock record (injected in view) does not exist in CRM which aborts the platform stage (30) of retrieve, thus not proceeding to PostOperation stage (40). Once system fetches dummy record in platform stage, the PostOperation stage step is also executed.
- In Post-Operation
-
- Look for shared variable as set in PreOperation stage as that is the guid of mock. Reverse translate this guid to keys of external system to fetch sales data.
- Fill BusinessEntity in OutputParameters with data from external system to return to CRM form
Sample code as below (not complete code):
if (executionContext.MessageName == "Retrieve")
{
//pre-operation of retrieve is needed to fetch a dummy record when user clicks a record in the view
//during this stage a dummy record's guid (which exists in crm db) is set into target,
//so platform operation (i.e, stage 30) does not fail, if platform operation fails the post-operation of
//retrieve is not executed
if (executionContext.Stage == 20)
{
if (executionContext.InputParameters.ContainsKey("Target"))
{
object inputParam = executionContext.InputParameters["Target"];
EntityReference target = inputParam as EntityReference;
Guid mockTargetId = target.Id;
if (target != null)
{
IOrganizationService systemService = customPluginContext.OrganizationServiceFactory.CreateOrganizationService(null);
using (DataContext dataContext = new DataContext(systemService))
{
Guid dummyId = Guid.Empty;
var dummy = dataContext.cst_sales.Where(item => item.cst_name == "DUMMY").Select(item => new cst_testentity { cst_testentityId = item.cst_testentityId }).FirstOrDefault();
if (dummy == null)
{
dummyId = Guid.NewGuid();
dummy = new cst_sales
{
cst_name = "DUMMY",
cst_salesId = dummyId };
dataContext.AddObject(dummy);
dataContext.SaveChanges();
}
else
{
dummyId = dummy.Id;
}
target.Id = dummyId;
//target.Id = new Guid("43BAE32A-1247-E511-80EC-00155D008406");
}
}
executionContext.SharedVariables.Add("MockTarget", mockTargetId);
}
}
else if (executionContext.Stage == 40)
{
if (executionContext.SharedVariables.ContainsKey("MockTarget"))
{
object sharedVariable = executionContext.SharedVariables["MockTarget"];
if (sharedVariable != null)
{
Guid mockObjectId = Guid.Empty;
if (Guid.TryParse(sharedVariable.ToString(), out mockObjectId))
{
//mockObjectId is the guid that we built for each record in the view
//this id needs to be built in a way that the value of external record could be extracted
//and then extracted value could then be used to lookup record values from external source
if (executionContext.OutputParameters.ContainsKey("BusinessEntity"))
{
object outputParam = executionContext.OutputParameters["BusinessEntity"];
Entity entity = outputParam as Entity;
if (entity != null)
{
cst_sales sales = entity.ToEntity();
sales.cst_name = "Mock";
sales.cst_DateTime = DateTime.Now;
sales.cst_FPNumber = 133.55;
sales.cst_WholeNumber = 400;
sales.cst_DecimalNumber = new decimal(93.247);
sales.cst_MultiText = "This is multi-text mock";
sales.cst_OptionSet = new OptionSetValue((int)cst_testentitycst_OptionSet.Item2);
sales.OwnerId = new EntityReference(SystemUser.EntityLogicalName, executionContext.InitiatingUserId);
}
}
}
}
}
}
}
Sequence of invokation
Sequence of call flow for loading LOB details
Conclusions
- Dynamics CRM is purely model driven solution, however once an entity is defined in model, RetrieveMultiple & Retrieve messages can be hooked to show data that does not exist in CRM database.
- Due to total serverside supported approach, this solution leverages CRMs out-of-box views to show data in UI
- Further building on this approach it should be possible to show charts & dashboards, based data that is only available in connected LOB systems, CRM only needs to hold referencing keys of that data.
- It usualy pays to “lazy load” the data :). Only query when it is really needed.
- Avoid over-killing your data integration scheme
Improvements
- Looking for a way to avoid translation to guid and reverse translation to source keys
- Avoid reading dummy record in PreOperation, and cache it somehow
- Implement paging in such a way that external system only returns the data for page instead of paging in plugin
Hope you find it of some use for your implementation scenarios!!
Rise to distinction 
Thanks Ameed, great article! I have tried implementing your code and the retrievemulitple works well and populates the subgrid with ‘mock’ records. However, for the retrieve part, when I try open a mock record, I get a ‘Record is unavailable’ exception. I am using CRM 365 online, any ideas on what could be causing this.
Ignore my previous comment, my dummy record was not being created. it is now working. Thanks
Cool