Saratoga Technologies Microsoft CRM Blog

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!