Rise to distinction
Time & tide wait for none
About me
Human being potential undoubtedly remains an immeasurable asset. It grows with every new path, direction or attempt we make
Being a fellow of the school of learning, with each new one, I strive to further build my concepts. On experience front, I am:
- MS Dynamics CRM specialist, with hands-on technical experience since 2004
- Information technology consultant with keen interest in enterprise systems integration
- Enthusiast about working with lean process in systems’ development with continuous feedback towards improvement
- IT Professional, but certainly NOT a technology geek, believe in building on concepts
- Not a surface-only person, like to know the fundamentals
Some of my thoughts:
- Identify “Why” something is needed in first place, do not straightaway jump to “What” and “How” subject.
- There mostly exist more than just one single method about “How to achieve any objective”, but significance of “Why it is needed” is much higher
- “Keep it simple” — that keeps it best
- Where there is a will, there is a way
Firmly believe in “Live and let live”.
Connect to D365 TDS (SQL) Endpoint from .Net Core 3.1
Recently i needed to implement a timer based Azure function to query and process data in D365 CE tables. Due to increasing volume of data i needed apply filters while reading data i.e, either Fetch / Query Expression / WebApi or somehow make it work with SQL. Here TDS (Table Data Stream Protocol: read here) came in quite helpful as I could do much extended filtering in SQL than querying through Fetch or WebApi queries, saving all in-memory filtering complexity. So the next step was to connect to TDS endpoint for .Net Core 3.1 function code, as classic ADO.Net / SQL Linq DataExtensions is not supported.
Cutting the long story short, I needed to connect to TDS (SQL) endpoint for my D365 CE Organization (Environment) database from my .Net Core 3.1 Function code (C#), without ADO.Net. Reading a few different community sites / posts I found the way to work it out. The solution itself is quite easy, as long as one understands mechanics of OAuth 2.0 authentication (read more here) using Service Prinicpal (aka Azure App Registration).
Read more here, In order to understand the need and how to create SP (aka Azure App Registration).
Note: TDS endpoint can be enabled for specific environment (org) using Power Plaftform Admin portal (admin.powerplatform.microsoft.com). For specific Environment, browse to Settings -> Product -> Features -> TDS Endpoint. Try connectivity using SQL Server Management Studio, as also mentioned here in MS Docs.
Following code can be used to generate OAuth 2.0 AccessToken to be used later in making connection, where:
{tenantid} is the tenant’s id of the D365 environment – can be found from either Azure Portal or Power Apps Maker site (Session Details)
resourceId is the url D365 environment: https://<org>.crm4.dynamics.com
clientId is from Azure App Registration
clientSecret is from Azure App Registration
var ctx = new AuthenticationContext($"https://login.microsoftonline.com/{tenantId}");
var authenticationResult = ctx.AcquireTokenAsync(resourceId, new ClientCredential(clientId, clientSecret))
.ConfigureAwait(false)
.GetAwaiter()
.GetResult();
//Tip: cache authenticationResult based on authenticationResult.ExpiresOn and regenerate based on current time.
Following code shows how to use AccessToken from OAuth 2.0 AccessToken, where:
connectionString is built based on D365 org / environment:
Server=<org>.crm4.dynamics.com
using (var d365DbContext = new DbContext(connectionString))
{
((System.Data.SqlClient.SqlConnection)d365DbContext.Database.Connection).AccessToken = authenticationResult.AccessToken;
d365DbContext.Database.SqlQuery<object>("select count(1) from contact").FirstOrDefault();
}
Note: As of 15-Dec-2021 MS Doc (read here) it is possible to connect to TDS endpoing using Microsoft.Data.SqlClient 4.0.0 (SqlClient Github) as in code below. However EntityFramework 6.4.4 (for using with DbContext) does not, as yet, come with a provider that uses Microsoft.Data.SqlClient. Due to this, one either has to work without EF or use/build a custom provider. One such is available here, using EntityFramework Nuget package from Erik while using Microsoft.Data.SqlClient 2.1.3
using (var connection = new Microsoft.Data.SqlClient.SqlConnection(@"Server=<org>.crm4.dynamics.com;Authentication=Active Directory Service Principal;User Id=clientId;Password=clientSecret"))
{
var command = new Microsoft.Data.SqlClient.SqlCommand("select count(1) from contact", connection);
connection.Open();
var reader = command.ExecuteReader();
}
Hope this helps!
Search shared data across CRM entities
In one of recent project requirements, I implemented a solution to share Personal Email Templates. Requirement was to allow super users to share specific templates with users/teams in readonly or write permissions. Though language filter provides a way to limit templates for specific users, it is still possible for users to see organization visible templates. What we needed was to have personal templates that could be further shared, specially when defined by users, in higher businessunits, that shall be visible for usage to users below.
CRM SDK allows templates to be owned by users / teams, and / or can also be shared, however the templates lists / views interface is not as intuitive as compared to commonly used entities. My solution comprised of a CRM dialog combining with workflow activity code, to implement very specific business logic as per requirements. Implementing this solution was not a big trick, however the more surprising part came later, when it was time to verify whether the specific templates were shared with required users. One way was to login in multiple browsers with different users and other was to somehow query CRM. Honestly, i did not like either of those approaches. So i looked into our favorite: XrmToolBox. There is a nice plugin named “Access Checker”, but I still could not see a list of all records shared with with users & teams. And there I got the idea “why not build one such plugin myself”. So folks, I wrote my first plugin for XrmToolBox, (hopefully not the last).
You can find it here:
Search shared CRM entity records (data across PrincipalObjectAccess)
I am using POA (PrincipalObjectAccess) entity here, but of course there is no direct querying of CRM database to read from this table. If you are not aware of POA => It is one central entity / table where CRM maintains which entity’s which record is shared with which user / team in which access mode.
In order to access this plugin, make sure you are using latest version of XrmToolBox (i am using 1.2018.10.29). In Plugin Store, look for “Search data in PrincipalObjectAccess (POA)”. Select the plugin and install
Once installed, the plugin shall show (as below) in the main screen of XrmToolBox.
Open the plugin and explore, i hope you will find it interesting and helpful.
Note: once i started working on this plugin, I thought of a number of additional features to implement in this interface. I will keep working on improvements, however your suggestions / comments / critics will be extremely helpful in improving this
MS Dynamics CRM – Usage of Assign – Share – Queueing
Progressing further in the series of administration and security areas of MS Dynamics CRM, here is another one.
In this session we walk through three of the core features of MS Dynamics CRM application. We look at the notions of Assign, Share, Queues in application.
How do these features help users working with same records.
This session is the continuity of my previous webinars as below
MS Dynamics CRM Business Units & Users Administration (Part 2)
MS Dynamics CRM Business Units & Users Administration (Part 1)
MS Dynamics CRM- Marketing Lists & Campaigns for beginners
MS Dynamics CRM – Sales Cycle Overview
MS Dynamics CRM – A beginner’s walk through
Since 2004, I have been working as techno-functional consultant on MS Dynamics CRM application & platform. I started working with this product as a programmer and gradually moved into technical consulting and solutions architecture.
For further details, my profile is here
https://dk.linkedin.com/in/ameeds
i also blog, mostly technical stuff, here
https://ameedsheikh.wordpress.com/
MS Dynamics CRM Business Units & Users Administration (Part 2)
In this session, I present a walk through while diving deeper into following out-of-box features of MS Dynamics CRM Online.
- Users
- Business Units
- Teams
- Security Model
We look at the process of provisioning new users through O365 with CRM license and then configuring those users in CRM with security roles. We also briefly look at teams setup again in this session. I hope this helps you overcome the basic understanding needs for configuring these features with respect to your business requirements.
This session is the continuity of my previous webinars as below
MS Dynamics CRM Business Units & Users Administration (Part 1)
MS Dynamics CRM- Marketing Lists & Campaigns for beginners
MS Dynamics CRM – Sales Cycle Overview
MS Dynamics CRM – A beginner’s walk through
Since 2004, I have been working as techno-functional consultant on MS Dynamics CRM application & platform. I started working with this product as a programmer and gradually moved into technical consulting and solutions architecture.
For further details, my profile is here
https://dk.linkedin.com/in/ameeds
i also blog, mostly technical stuff, here
https://ameedsheikh.wordpress.com/
MS Dynamics CRM Business Units & Users Administration (Part 1)
In continuity of previous sessions, in this webinar we walk through out-of-box features available for Business Units & Users administration in MS Dynamics CRM.
My previous webinars can be found here:
MS Dynamics CRM- Marketing Lists & Campaigns for beginners
MS Dynamics CRM – Sales Cycle Overview
MS Dynamics CRM – A beginner’s walk through
Since 2004, I have been working as techno-functional consultant on MS Dynamics CRM application & platform. I started working with this product as a programmer and gradually moved into technical consulting and solutions architecture.
For further details, my profile is here
https://dk.linkedin.com/in/ameeds
i also blog, mostly technical stuff, here
https://ameedsheikh.wordpress.com/
MS Dynamics CRM- Marketing Lists & Campaigns for beginners
This webinar, being third in the series of my previous sessions, is focused on helping beginners understand out-of-box marketing lists & campaigns features in MS Dynamics CRM.
Stay tuned for taking a deeper dive into Campaigns execution in next coming webinars
My previous webinars can be found here:
MS Dynamics CRM – Sales Cycle Overview
MS Dynamics CRM – A beginner’s walk through
Since 2004, I have been working as techno-functional consultant on MS Dynamics CRM application & platform. I started working with this product as a programmer and gradually moved into technical consulting and solutions architecture.
For further details, my profile is here
https://dk.linkedin.com/in/ameeds
i also blog, mostly technical stuff, here
https://ameedsheikh.wordpress.com/
MS Dynamics CRM – Sales Cycle Overview
In continuity of previous webinar (MS Dynamics CRM – A beginner’s walk through), this webinar is a rather quick and focused session on out-of-box features under sales area in MS Dynamics CRM online.
I have 12+ years of techno-functional experience working with MS Dynamics CRM. I started working with this product as a programmer and gradually moved into technical consulting.
For further details, my profile is here
https://dk.linkedin.com/in/ameeds
i also blog, mostly technical stuff, here
https://ameedsheikh.wordpress.com/
MS Dynamics CRM – A beginner’s walk through
This is first in the series of webinars to follow afterwards. I wish to impart the knowledge that i have built upon, during my journey with Microsoft Dynamics CRM.
A number of things need to be improved in this, however the effort has to start from somewhere. I wish to stand corrected, where experienced audience find necessary. Hope to walk through on more and advanced topics in forth coming sessions.
For further details, my profile is here
https://dk.linkedin.com/in/ameeds
i also blog, mostly technical stuff, here
https://ameedsheikh.wordpress.com/
This is my first ever webinar, hope this can bring you some useful knowledge. I appreciate your patience.
Integrate LOB data details in CRM without loading large data volumes (aka.. virtual entity 8.x onwards)
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!!
New or Modified Input / Output Arguments do not appear in workflows and dialogs
Changing signatures of existing custom activities can run into problems.
Recently I stumbled upon a rather weird challenge with usage of custom activities in processes (workflows and dialogs). What seemed to be a much simpler change, spilled a couple of more hours than estimated :(.
“Signatures” of custom activity literally mean its Input & Output arguments. So modifying signatures is adding or changing existing arguments. Based on the extent of usage of existing activity, changing existing arguments types is likely to expand scope of change as requiring adjustments in existing processes.
However, adding new arguments is a rather simpler change, which is also the sceario here.
I added Output argument to an existing activity. After rebuild and deploy of same assembly, I struggled to see newly added argument in designer. Being OnPremise I had all the power so I tried a few options as listed below:
1. IISReset — Did not work, that was a shock!!
2. AsyncService restart — Did not work
3. Sandbox Service restart — Did not work
4. Combination of both 1 & 2 — Did not work
I could locate which workflows / dialogs were using the custom activity. Being just a couple, i tried removing the activity registration and redeployed. This was just to make sure that i was actually not missing anything in deployment. After redeploy my changes certainly appeared BUT!! that was of course not an acceptable solution. After a number of hit & tries, i eventually found a simple (trick) solution .
The trick:
Within plugin registration tool, after assembly changes are deployed, expand to the custom activity being modified. In the properties area on the right, change and save any of the editable properties:
Description, FriendlyName, Name, WorkflowActivityGroupName
Once saved, change the property back to same value as before and save again. This is just to revert changes
Reload the process where activity is being used to find all changes being reflected. Seems like, the process designer, does not get notified that custom activity is modified, on update of activities assembly only.
Properties of Custom Activity
Note: I have tested this behavior with both 2011 latest rollup & 2015 OnPremise with update 0.1 (version 7.0.0001.0129)
Rise to distinction 