Archive

Posts Tagged ‘Customization & Development’

Enhanced: FTP Server as Document Store for CRM4

Microsoft CRM can store files as annotation but is not a document management system at all. There are many ways to integrate CRM with Document Management Systems. My approach in this article is pretty much utilization of CRM as xRM. So this xRM based solution is built on top of powerful Dynamics CRM platform.

The idea behind this post is a cost effective way to create, persist, and distribute documents. Since document size can increase to GB’s so attaching documents as annotation is not a good idea and can clearly increase the size of database in days and degrade the system performance.

I am using FTP and FTPS protocol to manage documents. At CRM side i have created an entity representing document as a record and then associated this entity where ever required. So here is my entity.

I have document category and sub category (dependent picklist) to categorize and classify my document on FTP Server. I have a due date to trigger my document escalation/follow-up workflows. I have a status reason associated to a document (Draft/Approved/Pending/Rejected/On Hold/Reviewed). I have Upload and Download Button to save and retrieve document from FTP server. In the references section, I have the meta data attached to the document entity. The metadata could be a relationship to case/contact/account or any other custom entity. So in Case entity my document will appear as shown below:

I can instantly view the file without downloading it on local disk by just pressing download button and click open.

So now I can instantly Approve/Reject/On Hold/ Review in CRM by using ISV buttons depending on current user privileges.

I have a full log of actions on my document available with status, user performing action and dates.

I have a comprehensive log of correspondence on this document available as shown below:

I have configurable alerts regarding Pending document/document on hold or documents to be reviewed. They are available to me on daily/weekly/fortnightly/monthly etc. bases and they are right in my outlook with links to my document in CRM.

Also I can utilize these documents online in my customer portals (eService). My documents can be accessed online by using FTP/FTPs and utilized in various ways as below:

Support Portals

Online Product Catalogues

Online Newsletters

Online Product Notices

FTP Server as Document Store for CRM4

Microsoft CRM can store files as annotation but is not a document management system at all. There are many ways to integrate CRM with Document Management Systems. If you are looking for search within document, version control, and publishing workflow then this post is not for you.

The idea behind this post is to utilize CRM structural relationship model and build a Document Store to process documents and also a cost effective way create, persist, and distribute document. Since document size can increase to GB’s so attaching documents as annotation is not a good idea and can clearly increase the size of database in days.

I am using FTP and FTPS protocol to manage documents. At CRM side i have created an entity representing document as a record and then associated this entity where ever required. So here is my entity.\

 I have document category and sub category (dependent picklist) to categorize and classify my document on FTP Server. I have Upload and Download Button to save and retrieve document from FTP server. In the references section, I have the meta data attached to the document entity. The metadata could be a relationship to case/contact/account or any other custom entity. So in Case entity my document will appear as shown below:

 

So now I can apply approval workflows in CRM whenever a new document is created.

 

Also I can utilize these documents online in my customer portals (eService). My documents can be accessed online by using FTP and utilized in various ways as shown below:

Auto Complete Recursive/Waiting Workflows – Microsoft CRM 4

Microsoft Dynamics CRM provides very powerful workflow designer tools to create workflows for various business scenarios. Waiting workflows are one of them. Very often, it is required to create waiting workflow on dates. So if a date is changed the waiting workflow remains in waiting state and date change executes another waiting workflow. So this aggregation of workflows affects CRM Server performance and application functionality.

Recently I have created a custom workflow activity to kill these waiting workflows in a recursive workflow. So I would like to share my experience with all of you.

I am in a condition to create a waiting workflow to reschedule my activity periodically i.e. recurring appointments, Auto Reorder (Sales Order) etc. My workflow is fired on Create of activity/Order and on change of Next Due date/Next Order Date. So if the user has changed Next Due Date or Next Order Date, the workflows in waiting remains in waiting and also a new Waiting workflow is fired. My aim is to kill the existing workflow as it is invalid now and start a new waiting workflow with updated information.

My workflow name is Auto Reschedule Activity. Here is the description of my workflow:

Start:
                Wait for Next Due Date == Today
                                Reschedule Activity Accordingly
                                Recursive Call: Auto Reschedule Activity
End

What i am looking for is:

Start:
                Check if there is an existing workflow with same Workflow ID, Entity ID and in Waiting State.
                                Then Kill that workflow.
                Wait for Next Due Date == Today
                                Reschedule Activity Accordingly
                                Recursive Call: Auto Reschedule Activity
End

So I started with a custom Activity to fetch workflows in waiting state with same name and regardingobjectid:

I created a query object in my custom workflow activity:

QueryExpression query = new QueryExpression();
query.EntityName = “asyncoperation”;
 query.ColumnSet = cols;

And created 3 conditions as below:

ConditionExpression c1 = new ConditionExpression();
c1.AttributeName = “name”;
c1.Operator = ConditionOperator.Equal;
c1.Values = new Object[] {‘My workflow Name’}; //Workflow
ConditionExpression c2 = new ConditionExpression();
C2.AttributeName = “statecode”;
C2.Operator = ConditionOperator.Equal;
C2.Values = new Object[] {1}; //Waiting
ConditionExpression c3 = new ConditionExpression();
C3.AttributeName = “regadingobjectid”;
C3.Operator = ConditionOperator.Equal;
C3.Values = new Object[] {context. PrimaryEntityId}; //Waiting

So now I have all the waiting workflows regarding my activity. Ideally there will be only one workflow in waiting state all the time. This workflow will be responsible for rescheduling.

So now is the time to kill the outdated workflow. So here is the code for setting that workflow complete/cancelled.

foreach (BusinessEntity be in bec.BusinessEntities)
{
                    asyncoperation async = (asyncoperation)be;
                    async.statecode = new AsyncOperationStateInfo();
                    async.statecode.Value = AsyncOperationState.Completed;
                    service.Update(async);
}

You can improve this workflow utility to add lot more functionality to workflow designer.

Event on Many to Many Relationship (N:N) in CRM 4

Microsoft Dynamics CRM 4 does not expose any event when records are associated through N:N relationship. So if you want to perform some function when a record is associated to another record in N:N relationship, CRM simply excuses and you have to look into hacks and unsupported solutions.
Hack 1:
Aaron has come up with a solution to expose the Disassociate and Associate event using a SQL script. This is unsupported customization and Microsoft is not supporting these changes.
Hack 2:
Create a flag at the master entity named IsDirty and place that attribute at a hidden tab.
At onload event simply mark this attribute as true.
crmForm.all.oa_isdirty.DataValue = !crmForm.all.oa_isdirty.DataValue;
Now you can have an attribute that prompts user every time to save the form and you can plan a workflow or plugin that executes on update of this event.
This is a supported customization and can easily be upgragable.

Microsoft Dynamics CRM 4 does not expose any event when records are associated through N:N relationship. So if you want to perform some function when a record is associated to another record in N:N relationship, CRM simply excuses and you have to look into hacks and unsupported solutions.

Hack 1:

There is a cool hack by Aaron at http://consulting.ascentium.com/blog/crm/Post533.aspx.

Aaron has come up with a solution to expose the Disassociate and Associate event using a SQL script. This is unsupported customization and Microsoft is not supporting these changes.

Hack 2:

Create a flag at the master entity named IsDirty and place that attribute at a hidden tab.

IsDirtyFlag

 

  At onload event simply mark this attribute as true.

crmForm.all.new_isdirty.DataValue = !crmForm.all.new_isdirty.DataValue;

Now you can have an attribute that prompts user every time to save the form and you can plan a workflow or plugin that executes on update of this event.

This is a supported customization and can easily upgraded.

Generate Excel 2007 Sheets from MSCRM Data – OpenXML Show

Office Open XML is an XML-based file format specification for electronic documents such as memos, reports, books, spreadsheets, charts, presentations and word processing documents. The specification has been developed by Microsoft as a successor of its binary office file formats and was published by Ecma International as the Ecma 376 standard in December 2006. The format specification is available for free at Ecma International. 

Let me introduce you the technique I have used to generate the Excel 2007 sheet from CRM data. I have downloaded an Excel package. ExcelPackage provides server-side generation of Excel 2007 spreadsheets. See http://www.codeplex.com/ExcelPackage for details. 

Specifying Output Directory and Template Directory

 Output directory is where you place the generated excel file and Template  directory is where you place the template file. Template file is simply a .xlsx file without data. Use following code to specify both directories. 

DirectoryInfo outputDir = new DirectoryInfo(@”GenerateExceloutput”);
DirectoryInfo templateDir = new DirectoryInfo(@”GenerateExceltemplates”); 
FileInfo newFile = new FileInfo(outputDir.FullName + @”File-” + strRecordID + “.xlsx”);
FileInfo template = new FileInfo(templateDir.FullName + @”TempReport.xlsx”);
if (!template.Exists)         
throw new Exception(“Template file does not exist! i.e. template.xlsx”);
 

Write data to Excel File 

ExcelPackage contains library functions for accessing worksheet, cells etc. For details please have a look into the Excel package code. Uses following code to write data to individual cell in excel worksheet. 

using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))     

{           
try
           
{
               
 
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets["Sheet1"];
               
if (worksheet != null)
               
{
                   
worksheet.Cell(2, 9).Value = ClearText(strData);                   
                   
xlPackage.Save();
               
}
           
}
           
catch (Exception ex)
           
{
            }         
}
 

Adding Rows to Sheet          

worksheet.InsertRow(iRow); 

Return Excel File for Download 

Simply redirect the response object in ASP.NET to the excel file generated. 

Response.Redirect(“http://” + Request.Url.Host + “:” + Request.Url.Port + “/” + “GenerateExcel/output/” + newFile.Name); 

All cell styles, formates and pictures objects that you set once in Template will remain the same in the generated file. 

Please find below my sample quote geneation application. I have placed a button at Quotes toolbar named Generate Quote. It will generate quote report in excel 2007 with the Quote data from crm currently selected.

 pic1.jpg 

My program fetches data from MSCRM through web services and populated my Excel sheet. It then returns my generated excel sheet with options to open or download.

pic2.jpg

MSCRM and MapPoint Integration – A picture is worth a thousand words.

Microsoft Provides MapPoint Web Service to enrich application with mapping functionality. It allows you to integrate maps, driving directions, order tracking and proximity searches into a wide range of solutions.

For those of you who want to start using MapPoint for test and development purposes you can sign up for a free developer account here:

htt
ps://mappoint-css.partners.extranet.microsoft.com/MwsSignup/Eval.aspx

With a developer account, you get full access to the MapPoint Web Service APIs and staging environment, which you can use to build applications for trial, demonstration, and proof-of-concept purposes.

Overview

Overview

Microsoft Dynamics CRM Account entity already has longitude and latitude parameters for address. But mostly organizations don’t use those parameters. MapPoint web service provides FindAddress service to resolve addresses and returns longitude and latitude parameter as shown in the code snippet below. 


Find Address Longitude and Latitude FindServiceSoap FindService = new FindServiceSoap ();
FindService.Credentials = new NetworkCredential(“”, “”);
FindService.PreAuthenticate = true;
MyAddress mAddress = new MyAddress();

mAddress = (MyAddress)addresses[i];

FindAddressSpecification spec = new FindAddressSpecification();
spec.InputAddress = new Address();
spec.InputAddress.AddressLine = mAddress.Line1;
spec.InputAddress.CountryRegion = mAddress.Country;
spec.InputAddress.Subdivision = mAddress.StateProvince;
spec.InputAddress.PrimaryCity = mAddress.City;
spec.InputAddress.PostalCode = mAddress.Postalcode;
spec.DataSourceName = “MapPoint.NA”;
FindResults results = FindService.FindAddress(spec);

Populating Location and Pushpin Array 

Location and pushpins objects require longitude and latitude values for address. Following code is used to populate Location and Pushpin objects of MapPoint API. 


Location[] myLocation = new Location[addresses.Count];
myLocation[i] = new Location();
myLocation[i].LatLong = new LatLong();
myLocation[i].LatLong = results.Results[0].FoundLocation.LatLong;
Pushpin[] pushpins = new Pushpin [addresses.Count]; pushpins[i] = new Pushpin();
pushpins[i].PinID = “pin0″;
pushpins[i].IconName = “0”;
pushpins[i].Label = mAddress.Name;
pushpins[i].IconDataSource = “MapPoint.Icons”;
pushpins[i].LatLong = results.Results[0].FoundLocation.LatLong;
There is no simple way to provide hyperlink at Pushpins, although Pushpin label and icon can be set to custom settings. Getting image and automatic zoom based on the geography covered by the addresses

MapPoint Web Service provides Render Service API to automatic zoom based on the geography covered by addresses. Here is the code snippet to do this:

//Call MapPoint Render Web Service RenderServiceSoap RenderService = new RenderServiceSoap();
RenderService.Credentials = new NetworkCredential(“”, “”);
MapViewRepresentations mvRep = RenderService.GetBestMapView(myLocation,”MapPoint.NA”);
mviews[0] = new ViewByBoundingRectangle();
mviews[0] = mvRep.ByBoundingRectangle;
MapSpecification mspec = new MapSpecification();
mspec.Options = moptions;
mspec.Views = mviews;
mspec.Pushpins = pushpins;
mspec.DataSourceName = “MapPoint.NA”;

MapImage[] image = RenderService.GetMap(mspec);

So the final image can be displayed in some ASP.NET page to show in Microsoft Dynamics CRM. Please find below my integration of MapPoint web service with MSCRM. One can select Accounts to be plotted on MapPoint and click Show Accounts Map Button at Account Entity grid toolbar.  All selected accounts will be plotted on the MapPoint in a new web dialog with Invalid addresses at the bottom.  

Step 1 


 Here is the final Image generated in Web Dialog:
Step 2 For any comments or suggestion do let me know. Your suggestions and comments are valuable for me. Thanks!

MS CRM and SharePoint 2007 Integration

Here is a very nice article by one of my colleagues,  Rehman Gul, on MS CRM and MOSS 2007 integration. I thought to share this with all of you….you can also find the article on his blog @ http://rehmangul.wordpress.com/2007/05/08/ms-crm-and-sharepoint-2007-integration/ 

SharePoint 2007 provides integration with quite a few Microsoft products including Dynamics, SQL Server Reporting Services, PerformancePoint, etc. Today, we will specifically talk about SharePoint’s integration with MS CRM. Here is the press release from Microsoft announcing the ability of SharePoint to provide tight integration with MS CRM environment: 

http://www.microsoft.com/presspass/press/2007/mar07/03-12Day1DynamicsOfficePR.mspx

Lets have a walkthrough of how can we connect with MS CRM using SharePoint’s Business Data Catalog (BDC) and Web Parts.

First of all we need an XML definition file to connect with CRM Database. Writing these files is difficult and error prone. You can easily find tools over the internet that will provide you with the ability to create Application Definitions for MOSS 2007. One such tool could be found here.

Here is the file I am going to use for the purpose of this exercise. This sample metadata XML defines various entities for MS CRM 3.0 database. If you have downloaded Dynamics Snap-ins, you can get one such file already present on your disk. If you intend to use the file that I am using, remember to change the Server and Database Names in it. They have been marked within the file…changes are required at the line 27 and 29.First of all we need to import this definition file to MOSS (Microsoft Office SharePoint Server) 2007.

Go to central Administration. It is usually created at the port 12779. The Url should look something like this:

 

 http://moss:12779/default.aspx  

untitledb.jpg

On the left Navigation Column click “SharedServices1” link. If its not there, you can create one clicking “Shared Services Administration” link on the left navigation column. “SharedServices1” site should look like this:

untitled1b.jpg

Under “Business Data Catalog” click “Import application definition”. Following screen appears:

untitled2b.jpg

Browse and select the definition file from your disk. Keep the default settings and click “Import”. This will initiate the upload process and take some time.

untitled3b.jpg 

After the file has been uploaded you can view the file clicking “View applications” under “Business Data Catalog”.

untitled4b.jpg

Now go to the site where you want to provide CRM connectivity. Click “ Site Actions” and “Edit Page”.

untitled5b1.jpg

Click “Add a Web Part”.

untitled6b1.jpg

From the list select “Business Data List” web part and click “Add”.

untitled7b.jpg

Click “open the tool pane” link in the “Business Data List” web part.

untitled8b.jpg

Click “Browse” icon right next to the “Type” text box displayed on the right top.

untitled9b.jpg

Select any “Business Data Type” and click “Ok”. 

untitled10b.jpg

Click “Ok” at the bottom of the options displayed in the right column. 

untitled12b.jpg Here is what you should see:

untitled13b.jpg
Just add the value in the empty text box that you want to fetch from the CRM Database and click “Retrieve Data”. You are connected to MS CRM through your MOSS 2007.Hope you enjoyed this post….I’ll continue on SharePoint’s integration with other Microsoft Products….hope to see you again….it’s bye for now……:)

Follow

Get every new post delivered to your Inbox.

Join 59 other followers