Dynamics CRM SDK–Multiple Table Joins
Imagine the scenario in which you’re a manager and want to have a quick overview of how your employees are doing.
Using Dynamics CRM you would like to know for each of the users on the system the number of accounts he/she handles and the total estimated value of the opportunities opened by those accounts.
Because of business requirements you want this to be readily available at your desk with no need for fancy graphics or exports. You just want a simple overview of what is going on.
Now imagine you are the developer in charge of doing this.
You want a simple console app to show this to the user while spending the least amount of time on it possible.
Basically you already know that you want to make a 3-way join between users, accounts and opportunities. You go into the CRM SDK and find lots of examples. Problem is, they are just too complex for the task you have.
So, start small!
1. Setup a new project
2. Create a connectionString.
a. You’re short on time but there is no need to hardcode values! J
3. Create the class and main sub
4. Fetch the connection string
5. Connect to CRM service (and two other lines of code)
6. Fetch the data
7. Output data
8. Done!
On to the techie part of it…
Setup a connection string to CRM:
<connectionStrings>
<!-- Online using Office 365 -->
<add name="onlineOrg" connectionString="Url=https://yourorg.crm4.dynamics.com;
Username=user@yourorg.onmicrosoft.com; Password=yourpass; authtype=Office365"/>
</connectionStrings>
Connect to CRM:
// Get the connection string from config
string connectionString =
ConfigurationManager.ConnectionStrings["onlineOrg"].ToString();
// Connect to the CRM web service using a connection string.
CrmServiceClient conn = new
Xrm.Tooling.Connector.CrmServiceClient(connectionString);
// Cast the proxy client to the IOrganizationService interface.
IOrganizationService _orgService =
(IOrganizationService)conn.OrganizationWebProxyClient != null ?
(IOrganizationService)conn.OrganizationWebProxyClient :
(IOrganizationService)conn.OrganizationServiceProxy;
// Create the organization service context object to use strong types (early bind)
ServiceContext svcContext = new ServiceContext(_orgService);
You can use late binding if you like. For simplicity sake let’s use early bind – You’ll have to generate the types using svcUtil. In this case the MyOrganizationCrmSdkTypes.cs file was added to the project from the SDK helper code (we’re using OOB entities).
Fetch data from CRM:
/* Join users to accounts and opportunities and output an anonymous object with all entity data
This will contain a list where the size equals the number of opportunities in the system
User and account are not unique but instead duplicated as this will resemble:
USER ACCOUNT OPPORTUNITY
u1 acc1 op1
u1 acc1 op2
u1 acc2 op3
u2 acc3 op4
*/
var userStatsList = (from user in svcContext.SystemUserSet
join account in svcContext.AccountSet
on user.Id equals account.OwnerId.Id
join opportunity in svcContext.OpportunitySet
on account.AccountId.Value equals opportunity.AccountId.Id
orderby user.FullName
select new { user, account, opportunity }).ToList();
Group by user and count/sum values:
var userStatsValues = userStatsList.GroupBy(x => x.user.Id)
.Select(x => new
{
userName = x.First().user.FullName,
accounts = x.Select(y => y.account.AccountId.Value).Distinct().Count(),
totalVal = x.Sum(y => y.opportunity.EstimatedValue.Value)
}).ToList();
Note:
We need the distinct on accounts because we need to filter duplicates in cases where an account has more than one opportunity – check the table example above.
This will output the aggregated values by user. You could now export this to CSV or do additional processing.
Show data:
List<String> finalStats = userStatsValues.Select(x => $"{x.userName} is currently
handling {x.accounts} accounts with a total opportunity value of
{x.totalVal.ToString("C")}").ToList();
//Join list with line breaks for output purposes
String fullList = String.Join("\r\n", finalStats);
//Print output
Console.WriteLine(fullList);
Best Regards
EMEA Dynamics CRM Support Team
Share this Blog Article on Twitter
Follow Us on Twitter