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



Comments (0)

Skip to main content