Saratoga Technologies Microsoft CRM Blog

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

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.

Today we came across the great unsupported customization requests of Microsoft Dynamics CRM 3 which was a customized and filtered lookup box for Microsoft Dynamics CRM 4.0.

 Although lookup boxes are much easier to generate natively in Microsoft CRM 4.0 you just need to create a N:1 relationship to whatever entity you want your lookup box to pull information for.

For example on the opportunities entity you may want to not only have “potential customer”, but also have the ability to show a list of contacts to choose from that are specific to the selected customer. This is simply known as a filtered lookup, and here is an easy way to accomplish it.

After you have completed your many to one relationship (if one doesn’t already exist) on your customized entity you can easily with javascript filter the second lookup.

 You will first have to add the following onload event to your entities form.

document.FilterLookup = function(source, target)
{
    if (IsNull(source) || IsNull(target)) { return; }

    target.additionalparams = ’search=’ + name;
}

Then on the onchange event of your attribute (in our case potential customer) you will have to trigger the filter function as in the example below, so that it will in fact also filter the second lookup field on the form as shown in the Custom Lookup Filter Image.

// If company is defined pre-filter lookup with customers
document.FilterLookup(crmForm.all.customerid, crmForm.all.sti_contact_opp_linkid);

 This also works best if you additionally copy the script to the onload of the form as well, this is so that if the user decides to change the existing contact to another contact it will still function as originally intended.

Well… For the past couple of weeks (in my spare time) i have been taking the scribe integration training classes as a new partner for the product. I have seen alot of similarities to another solution that i have used in the past for CRM to GP integrations.

I really believe that scribe is way ahead of the game when it comes to the isolation of the collaboration engine and the idea of using software package adapters as i think that generally this is easier for technical and non technical users to digest in a decent and easy to understand interface.

I have deployed the CRM and GP adapters now in a test and production environment and i have now passed a few of the exams for becoming a certified partner. The only tests i have left are the adapter exams and i plan to attempt those after i have a chance to revisit the partner training center and go through the videos again.

My current integration is working well and i am typically a very visual learner so getting my hands dirty with the product is usually what works best for me, and has worked out well in this case also.

I look forward to discussing GP and CRM integrations with the product in future and i am just wondering when an integration solution will come out natively from Microsoft as well. I heard some rumblings at convergence and attended a session about the new integration adapter, but it looked as if it was still under development.

The best part about scribe is the fact that it doesn’t only have to be linked to GP and it is flexible enough to pull information in from just about any data source imaginable.

Well…. back to studying for now… just thought i would post an update.

Take Care!

For the past couple of weeks i have been involved with going through the Scribe Insight training. So far i have completed the Workbench, Server, and adapter training for GP and CRM.

 I must say that i really like the open ended adapters that allow such access into both GP and the CRM platforms through e-connect on the GP side and the services access on the CRM side. I will post more information about some projects i will be completing with the application in the near future, but all in all i am really psyched about it.

 I also saw today that Microsoft is also going to discuss integration between CRM and GP at this years convergence…. Ow! Ouch! Ouch! Horrible flashbacks of the biztalk adapter and the seamless integration for CRM 3.0 are lodged in my brain now.

Ok so maybe i am being a bit pessimistic here, but i will attend the sessions about the new integration at convergence anyway to see what is up!

I just finished watching a Partner Session entitled a Technical Deep Dive of CRM Solution Accelerators. This session was focused on the 8 new releases of Microsoft CRM 4 Acelerators.

 I think that the team has done a great job getting these out for release. The first releases are to partners and customers are slated for as early as the end of this week.

The Solution Accelerator demo videos are going to be posted on the PLC and Partnersource by the end of the week as well. There will also be additional collateral such as BDM and TDM decks, Whitepapers, etc on each solution accelerator from what i heard in the session.

It was funny because the sessions were so crowded that keeping a decent internet audio connection was quite horrible. I will keep you posted as i get to try these out for myself.