The other day I was at a customer site and they asked if there was a way to see a "rollup" of all the products a particular account has purchased, other than going though their invoices one-by-one. So as an exercise I decided to have a crack at a solution for this.
Since I have been doing a lot of web-based projects for MSCRM and creating a
new web site each time, I decided to create a new VS 2005 project for all my CRM
web apps and I called it CrmApps.
So what we need to do is pull all the products from all the invoices for a
particular account, however the information we need is contained in 3 separate
MSCRM SQL tables. So instead of creating a complicated SQL query, I decided to
build a new SQL View with the information I need.
The tables we need to pull data from are:
InvoiceBase: stores the
account id and related invoice IDs
InvoiceDetailBase: stores the invoice details for the invoice IDs
ProductBase: stores the product
details for the products on the invoices
I used SQL Servers built in View Builder tool for this and I have to say, it was pretty easy. Just open SQL Enterprise Manager, open the MSCRM database, right-click on Views and Select "New". Add the tables described above as needed and you'll end up with something like shown below. I named my view "JohnzProducts".
Isn't that great, very easy!!
So next we need a Visual Studio project. I have been
building new VS 2005 projects for each example lately, so I decided to create
one all encompassing app, called CrmApps, and just add web pages to it as I
need. So I opened VS 2005, created a new web site, and then created a new IIS
virtual directory called CrmApps.
REMEMBER: if using VS 2005 you must put your VS 2005 applications in a different
app pool than any .NET 1.1 apps (like for instance, MSCRM :) and also select
ASP.NET v2.0.XX
Ok so now we need to work on our form, I named my form "products", very clever! I next added a gridview control to the form to display our products.
All the code will be in the "Page_Load" method, and is shown below:
using
System;public
partial class _Default : System.Web.UI.Page
protected void
Page_Load(object
sender, EventArgs e)
{
//This first bit of code reads the QueryString to
grab the MSCRM account id
int loop1, loop2;
// Load NameValueCollection object.
NameValueCollection coll =
Request.QueryString;
// Get names of all keys into a string array.
String[] arr1 =
coll.AllKeys;
for (loop1 = 0; loop1 <
arr1.Length; loop1++)
{
String[] arr2 =
coll.GetValues(arr1[loop1]);
for (loop2 = 0; loop2 <
arr2.Length; loop2++)
{
if (arr1[loop1].ToString().Equals("oId")
|| arr1[loop1].ToString().Equals("id"))
strAccountId = arr2[loop2].ToString();
}
}
strSqlQuery =
"select Name, Quantity from JohnzProducts where
AccountId = '" + strAccountId +
"';";
try
{
sqlCon =
new
SqlConnection(conStr);
sqlCon.Open();
SqlCommand myCommand =
new
SqlCommand(strSqlQuery,
sqlCon);
grdProducts.DataSource = myCommand.ExecuteReader();
grdProducts.DataBind();
sqlCon.Close();
}
catch (Exception
sqlEx)
{
//Do
something
}
finally
{
sqlCon.Close();
}
}
}
Next step is to build the code and then deploy the project to your IIS application folder. For example, I do my development in <My documents\Visual Studio 2005\Web Sites and then I deploy to C:\Inetpub\wwwroot\<project folder>, in this case it's C:\Inetpub\wwwroot\crmappps
So teh last step is to add our application to MSCRM, I decided to use an IFRAME on a new Tab. So open MSCRM, click Settings->Customizationand double-click the Account entity. Click Forms and Views, and then double-click the form row to open teh form.
I added a Tab called "Products", and then a section called "products Ordered" and then added an IFRAME to that section. As per the screenshot below, I named my Iframe "products", pointed it at my application URL, and Checked the "Pass record object-type code and unique identifier as parameters" box - You MUST check that box.
You should also check the "Automatically expand of rows to use available space" box on the "Formatting" tab.
So when finished you should have something like this:
Click "Save and Close", "Save and Close", and then make sure "account" is high-lighted and click "Publish".
And that's it, we are done. Now open up MSCRM->Sales->Accounts and open an account that you know has Invoices ( :) ) and you should see something like this:
STANDARD DISCLAIMER: This is a POC, feel free to comment, make it better, etc!
Please note that all postings and code samples on this site are provided "AS IS" with no warranties, and confers no rights. Use of any included script or code samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm