Saratoga Technologies Microsoft CRM Blog

Looking at the problems with file storage and CRM i yet again find myself looking for a better way to manage files and file storage through the wonderful CRM Interface. The main premise again sticking to the slogan of everything all in one place.

For this situation we have seperate network fileservers that handle our data storage on the network, and it wouldn’t make sense putting all that information into the CRM Database. Not to mention it isn’t indexable or searchable, so the value add for that would be really low.

The problem that i have ran into is that i do not want to have to change coding that i had previously from another post when my clients switch from Sharepoint Services and full sharepoint server in their Microsoft CRM implmenetations. So i have devised a different way to handle this that will also assist with future CRM 4 (IFD) and offsite Sharepoint Installations.

I first created a custom attribute in Microsoft CRM named SPFOLDER, and placed it on the company tab in Microsoft CRM. This was a simple bit flag that i will use for a CRMworkflow to determine if folders have already been created on our sharepoint server or not for a particular company.

For displaying on the company form you can add a simple check to see if the bit flag is set to yes or no. If it is set to true, then show the folder on the server through a simple iframe in CRM, named SPFiles. (as in the example below)

if (crmForm.all.sti_spfolder.DataValue == true)
{
crmForm.all.IFRAME_SPFiles.src = “http://fileserver/Client%20Data/Forms/AllItems.aspx?RootFolder=%2fClient%20Data%2f” + crmForm.all.accountnumber.DataValue;
}

if (crmForm.all.sti_spfolder.DataValue == false)
{
crmForm.all.IFRAME_SPFiles.src = “http://crmserver1/SPIntegration/default.aspx?accid=” + crmForm.all.accountnumber.DataValue;
}

I have structured the folder storage by account number because we have multiple names for some companies that are similar so i found account numbers work best, but you could adjust for whatever makes sense in your organization. In this example we use Dynamics GP inhouse and it is connected with CRM so the information is populated automagically!!!

So now that we have a simple way to handle how to display the sharepoint data, we simply need to have an event to create the folder structure.

Using a simple http webrequest we can generate these folders into Sharepoint from the data we have, and this will allow us to display the customers specific data folders as well.

HttpWebRequest request = (System.Net.HttpWebRequest)HttpWebRequest.Create(”http://fileserver/Client%20Data/” + lbl_CustomerName.Text + “”);

NetworkCredential netCredentials = new NetworkCredential(”sharepoint”, “Pass@word1″, “MEDTECH”);

request.Credentials = netCredentials;

//Send Request

request.Method = “MKCOL”;

HttpWebResponse response = (System.Net.HttpWebResponse)request.GetResponse();

response.Close();

 This will allow you to create company specific storage areas in Microsoft CRM. I have included some pictures as well to display how this looks in CRM on my machine.

After looking at the new CRM4 adapter for GP10 i had a few questions and most likely if you are reading this article then you had some questions as well and i hope that this article helps. I was glad to find out that this version is a completely new release and not a port from the older biztalk integration for CRM 3.0 and GP9.

The first thing i wondered was what are the 8 entities that integrated by default in the adapter? These are: Customers, Customer Addresses, Items, Price Levels, Sales Orders, Sales Invoices, and Contacts.

There is an SDK that is going to be out for the adapter Q4 2009, so additional integrations from other data sources will be possible. At the current time the adapter does not support hosted CRM, but since the adapter is developed as a webservice to webservice integration i would imagine that should follow shortly.

I found this great diagram depicting the integration paths for the adapter from the Microsoft Development Blog.

My next question was “The adapter is available but to who?” It is available to partners for no charge and Customers that have Microsoft CRM Software Assurance Plan and an active Microsoft Dynamics GP Enhancement Plan.

What versions of GP10 and CRM 4 does the adapter support? The customer will have to be on at least Dynamics GP10 Service Pack 4 and CRM update Rollup 5.

Where can i get it? This is the download link for Partners:
https://mbs.microsoft.com/partnersource/deployment/resources/productreleases/MDGP10_CRMAdapter.htm

Overall i think that this adapter will provide a resource for GP and CRM users that want to integrate data. I will be interested in seeing the additional products that come after the SDK is released and as the product matures.

One simple but useful idea that was posted on the Microsoft Dynamics CRM Team Blog was how to use an IFrame to show a map of an accounts business location (http://blogs.msdn.com/crm/archive/2009/04/15/microsoft-dynamics-crm-iframe-magic.aspx).

We decided to take this idea one step further. Since our in-house technicians go out to several client locations in a given day, it would be nice to give them a set of map directions to go from client to client. Using Dynamics GP and Enterprise Scheduler in-house (along with Field Service Anywhere), we have a database of scheduled appointments as well as the client addresses.

First thing we did was to create a SQL view of all of the appointments for that day. The view includes the Tech ID and the client’s address.

Next, we created a .NET app that takes the Technicians ID as a passed parameter, pulled all of the appointments from the view using the Tech’s ID, ordered them by appointment date (done in SQL view), then created a URL for google maps with all of the addresses.

This next part can be done in many ways. You do not even need to do anything in CRM, just create a web form to allow the user to select a Tech ID, and call the .NET app above. We decided to embed it in CRM, and this is how we did it. We created an entity called Technician Service Route, made the “Name” field of a record the name of the technician (and made him the owner). We created a field for the Tech ID (which we manually enter), and added a tab for an IFRAME to link in the .net app. Pass the tech id to the .NET app, and all done. We also made the Route tab the first tab on the entity form.  Now, when a technician opens up the Technician Service Routes, they have just their record, open it up,  and there’s his map for the day.

Technician Service Route

One request we had recently was to show all the notes for a company, in order for a Sales rep to get a snapshot of anything currently going on with the client. This was to include all notes for the Account, Opportunities, tasks, appointments, and leads. This was to be viewable on the main company screen.

In order to accomplish this, we needed to first create SQL views of all the different notes we were going to need with the information we wanted. Instead of doing one massive view of all of the notes, we did a view for each type. For instance, for Opportunities, we created a view called Opportunity_Notes.  It should be noted that since we are creating views, this is an unsupported way of getting the needed data.  Here is the SQL script for the Opportunity_notes that we used:

SELECT dbo.AnnotationBase.ObjectId AS OpportunityId, dbo.OpportunityBase.AccountId, dbo.AccountBase.AccountNumber,
dbo.OpportunityBase.Name, dbo.AnnotationBase.NoteText, dbo.AnnotationBase.Subject, dbo.AnnotationBase.CreatedOn,
dbo.SystemUserBase.FullName AS CreatedBy
FROM dbo.OpportunityBase INNER JOIN
dbo.AnnotationBase ON dbo.AnnotationBase.ObjectId = dbo.OpportunityBase.OpportunityId INNER JOIN
dbo.AccountBase ON dbo.AccountBase.AccountId = dbo.OpportunityBase.AccountId INNER JOIN
dbo.SystemUserBase ON dbo.SystemUserBase.SystemUserId = dbo.AnnotationBase.CreatedBy
WHERE (dbo.AnnotationBase.IsDocument = ‘FALSE’) AND (dbo.AnnotationBase.NoteText IS NOT NULL) AND (dbo.OpportunityBase.AccountId IS NOT NULL)
ORDER BY dbo.AnnotationBase.CreatedOn

From there, we created a .asp page to create the screen to combine all of the notes. Pretty simple, just made a connection to the database, and used DataLists with SqlDataSource to create our record views.

One problem we did have was that our various headers (ie: OpportunityNotes) would show up, even if there were no Opportunity Notes for that Company. To hide the headers, we added a Selected event to each SqlDataSource. Within that event, set a variable to “block”. Then within each datalist, use a DIV with a Style of “display” set to the variable.

Lastly, within the Account Form, we added an IFrame. Within the Form Onload, we set the IFrame source to the .asp that we published on the CRM server, with the Customer Number as a passed variable in the URL. We use the Customer Number from GP, which is unique, and used that as the passed variable instead of the GUID.

Here’s a screenshot of the final product.  Happy coding!

Well i set out this week to take a dive into the Microsoft Sharepoint API and find a helpful way that i could provide additional value add by providing a way to commonly share files across Windows Sharepoint Services 3.0 (Like on a dedicated fileserver) and Microsoft CRM.

 After finding many helpful documents and whitepapers on the feat i decided that the easiest way to integrate the 2 would consist of a few c# .NET services. The first service i will call the Document Synchronization Service.

This is really no more than a small c#.net console application that checks the customers that exist in CRM, finds the fileserver that sharepoint services is installed on, and then automagically creates the folder structure that is needed for consistant document storage on the network per customer.

 So off the top it went something… a like so…

using (SPSite oSiteCollection = new SPSite(http://nicholasc-2008/Client%20Data/Forms/AllItems.aspx)){//Testing ONLY… this is for later as this code will next have a data and service connection to the MSCRM db.

string folderName = “Customer Name Here”; SPList oList = oSiteCollection.AllWebs[""].Lists["Client Data"];SPListItem newFolder = oList.Items.Add(“”, SPFileSystemObjectType.Folder, folderName);newFolder.Update();}

The result is the following screen.

Ok, so one of the main features in Microsoft CRM is to be alerted when specific things occur within the platform. For example when a specific item that you are selling has a renewal that is upcoming, possibly yearly maintenance, or anything and everything.

 So you have a ton of options, i mean literally you could create workflows that sat around and waited to be fired in Microsoft CRM. But if you do not want tons of workflow processes hanging around there is a different approach that i suggest.

In this example a customer was looking for a way to be updated when a specific renewal was within so many days of expiring.

So in .Net this was really easy to create a console application that runs on whatever interval that you decide (Daily, weekly, monthly, etc.) That will search out and send information or an alert that something is happening in the system.

Just a note as this is not specific to only Microsoft CRM, it could be Dynamics GP, or any  type of database that you can connect to across your entire organization.

So on with the .Net Code right? Ok.

//Pull Renewals that are expiring in the next 0-30 Days completely Configurable

string connString = @”server = CRM;integrated security = true;database = SaratogaTechnologiesIncTEST_MSCRM”;string sql = @”select * from FilteredServiceRenewalsForService where sti_serviceexpires > dateadd(day, datediff(day, 0, getdate()), 0)and sti_serviceexpires <= dateadd(day, datediff(day, 0, getdate()), 30)”;

SqlConnection conn = new SqlConnection(connString);//Mailing Loop, Start for Application to Mail Service Reports

try

{ //Open MY Connection to Microsoft CRM DS

conn.Open();

SqlDataAdapter da = new SqlDataAdapter(sql, conn);DataTable dt = new DataTable();//Fill Data in DT

da.Fill(dt);

foreach (DataRow row in dt.Rows){

foreach (DataColumn col in dt.Columns)Console.WriteLine(row[col]);//Default Message Information

//Pause Mailer for 5 seconds (Optional)

//System.Threading.Thread.Sleep(5000);

/* CRM DataSet Fields – From View* 20 – Company Name

string compname = “”;string Service = “”;

string ServiceStart = “”;string ServiceExpires = “”;

string SKUPART = “”;string USERS = “”;

string RenewalDetails = “”;string RenewalStatus = “”;

string SalesPersonName = “”;string SalesPersonEmail = “”;

string EmailAddress = “”;string URLStringVAR = “”;

string URLString = “”;/*Set Variables to values */

compname = (row[23].ToString());

URLString = “http://crmserver1:5555/SaratogaTechnologiesInc/userdefined/edit.aspx?id={”;URLString = URLString + URLStringVAR;

URLString = URLString + “}&etc=10007#”;System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();System.Net.Mail.

SmtpClient smtp = new System.Net.Mail.SmtpClient(“exchange”);message.Subject = Service + ” Renewal Alert For “ + compname.ToString() + “”;message.From =

new System.Net.Mail.MailAddress(renewals@clientsite.com);message.IsBodyHtml = true;message.Body = SalesPersonName +

“, Microsoft CRM has found a “ + Service.ToString() + ” renewal upcoming for “ + compname + ” <br><br> <u>Service Renewal Details:</u><br><br> <b>Renewal Type:</b> “ + Service.ToString() + “<br> <b>Renewal Details:</b> “ + RenewalDetails + “<br> <b>Expires on:</b> “ + ServiceExpires + “<br><br> You can take a look at the service renewal details at the link below.<br><br> <a href=’” + URLString + “‘>View Renewal in CRM</a><br><br> Please remember to update Microsoft CRM after you have completed contacting the client, so that you are not notified again going forward.<br><br> Also, do not reply to this email address as this does not go to anyone at this time.” ;//Send Sales Person Notification about Upcoming Service Renewals

string recipientemail = SalesPersonEmail.ToString();string bcc = “nicholas.cole@saratogaus.com”;

Like i said this is for a simple custom renewal application, but can be applied to literally anything across your organization.

Enjoy!

Although there are some nice dashboard functionality out there that exists in CRM i  really like the animated charting controls by a company named FusionCharts. The charts themselves are flash movies that you can deploy by copying the chart that you want to use into your Visual Studio 2005 project.

In this example i am using c#.net and have created a .net 2.0 project. There are 3 ways that you can utilize the reports. The first is to pull data from an existing .xml file. Another way is that you can load the report through javascript.

The method that i have chose is to utilize the RendorChartHTML control in a c# codebehind file.

First i created the sql query i was going to use and populate a data set, in this case i am creating a monthly service call chart for the existing year.

string connString = @”Persist Security Info=False;Integrated Security=False;Server=(Server Name);initial catalog=STIN;user id=(id);password=(password);”;

string sql = @”SELECT MONTH(entdte) AS ‘Month’, Count(entdte) FROM dbo.svc00200 where Year(entdte) = 2009 GROUP BY YEAR(entdte), MONTH(entdte)ORDER BY YEAR(entdte), MONTH(entdte)”;

SqlConnection conn = new SqlConnection(connString);

Now that is completed i will create an xml string that i can use to pass as an argument to the flash report chart later. This is done with a simple for loop, i will also pass in the values i need in the loop to populate the xml data string.

foreach (DataRow row in dt.Rows){

//Start For Loop

foreach (DataColumn col in dt.Columns)Console.WriteLine(row[col]);

//Add Month to xml String Information strXML += “<set name=’” + row[0].ToString() + “‘ value=’” + row[1].ToString() + “‘ color=’AFD8F8′ />”;

Lastly i will call the RenderChartHML control to load and display the chart on the c#.net page.

return FusionCharts.RenderChartHTML(“FusionCharts/FCF_Column3D.swf”, “”, strXML.ToString(), “MonthlyServiceCalls”, “400″, “300″, false);

And then the chart loads into the page. In my example i have the site loaded out side of an iframe, but if you wish you can easily place it inside of an IFRAME in CRM. 

One of the developments that we accomplished for a client was a custom task and appointment report, and since this comes up quite often. This was mainly because the layout of Microsoft CRM tasks printout was really lifeless by default.

 For example it didn’t have additional information such as the information about the task at hand, who the salesrep was meeting with, what the company’s phone number and contact numbers where in an easy printable format.

 We created a better solution by utilizing a simple .net site that contained a SQL recordsource control that binded to a details list and modified the default template to look as the reps desired. We just had to create a customized SQL view of the data to pull from.

We then added additional navigation in CRM for easily selecting at any time the type of reports to print so that a salesperson could literally open CRM, print their tasks and appointments (with full details) that they could take on the road with them, write on during the day, then enter details back into the system the next day or later that afternoon.

I got this idea from the Microsoft CRM team blog and their guest blogger David Jennaway. The idea of acceptance and user reporting for CRM is really basic, but it provides a trackable way to determine if users are accessing the system. Here is the article.

 http://blogs.msdn.com/crm/archive/2009/04/08/crm-usage-reporting-unleashed.aspx

 A couple things that i found in the article to ‘tweak’ was defiantely adding a couple check constraints onto the SQL log table that you will be using this for. In my case there are quite a few users generating activity and that would be a very large file. Thank goodness for sql table compression in 2008 right… we will see.

If you haven’t created a check constraint before it is really easy, and you will save your tables from a bunch of useless rows of data. Here is a quick example below.

USE [CRMACCEPTANCE] 

ALTER TABLE [dbo].[inetlog] WITH NOCHECK ADD CONSTRAINT [CK_inetlog_username] CHECK (([username]<>‘MEDTECH\scribe’)) 

This is basically because i use scribe integration that is always writing rows into the database and these could cause significant storage use during the period of a month.

For those of you who are attempting to migrate Microsoft Dynamics CRM services to another remote server to help increase your I/O, performance or for whatever reason you like, you may find frustrated during this process.

 There are a few things that you need to do first to ensure that you have a smooth upgrade. First off i would definately check out the following article to ensure that a path that is defined is the easiest for the job.

 http://support.microsoft.com/kb/952934

 This will assist with moving your database of Microsoft CRM 4, and reporting services. However, there is also a few steps that are not included in this document.

Just make sure that after you deploy your reporting services reports to the new migrated server that you have also double checked the login on the mscrm datasource in the reportserver directory.

You can get errors that you will not be able to connect to CRM datasource and more. Turns out that after a couple of hours fighting with checking IIS security, SQL Service accounts, CRM deployment settings, SRSS groups and regedit settings that it turns out this is the most often overlooked.

So simple, but it can also be very aggrivating. Till next time!