Waiting/Timed Workflow VS Windows Service

Developing some service or workflow to handle some periodic tasks is natural to CRM solutions. Last week, I have been looking for the best way to handle periodic tasks like sending reminders, creating tasks based on date/time, birthday notifies etc.

So I have an entity project schedule and I have thousands of records of this entity where I have to send email reminders. For each record I can have a waiting workflow. So I can have thousands of waiting workflows. While, I can develop a windows service that runs overnight and process records according to criteria and send reminders.

What about performance constraints for hundreds of waiting workflows VS one windows service instance running overnight?

So here are some merits and demerits for both techniques:

1. Workflows can be configurable with no code but we need to create a configuration mechanism for windows service and scheduled tasks.

2. We can control the best time to execute a windows service while workflows fired on the event matching time condition.

3. Any change in the logic can be rolled out with ease in case of windows service during off peak time while waiting workflow remain in waiting as per old logic. So it creates a big issue in case of maintenance.

4. If number of records is high then creating waiting workflows cause a strain on the system and degrade performance as compared to windows service.

5. If there is frequent update operation on the waiting workflow entity then every time a change occur workflow becomes active and then in waiting state. This can cause a strain to the live system.

Although waiting workflows will not consume any CPU or memory resources but overhead of handling change and strain on system during peak hours make them a less favorite as compared to windows service. Personally I feel much control and maintainability in windows service as compared to workflows.

File Attachment to Notes – Dynamics CRM 4.0

In Dynamics CRM 4.0, file attachment to entities is pretty simple as compared to CRM 3.0. UploadFromBase64DataAnnotationRequest and UploadFromBase64DataAnnotationResponse object are both deprecated. Instead a new attribute has been introduces in annotation object with documentbody that accepts string in base64 encoding. Please find the below sample:

 

Reading file data into Byte array and then converting it to base64. Since I am using in memory object of StringBuilder class so I have just used str.ToString() to return string.

 

Byte[] data;

 ASCIIEncoding encode = new ASCIIEncoding();

data = encode.GetBytes(str.ToString());

string b64 = Convert.ToBase64String(data);

 

Next, simply create the annotation entity and set the attributes as below:

annotation note = new annotation();           

           

note.subject = “Test Export”;

note.filename = “exported.txt”;

note.mimetype = “text/html”;

note.documentbody = b64;

 

note.objectid = new Lookup();

note.objectid.type = “new_export”;

note.objectid.Value = entityid;

note.objecttypecode = new EntityNameReference();

note.objecttypecode.Value = “new_export”;

                       

Guid annotationId = service.Create(note);

 

Auto Numbers in MSCRM

Last week I have been looking for the best approach to use auto numbers in Microsoft Dynamics CRM. After discussion with my peers I have come to the following possible options that can be used for auto numbering. Although these may not be the efficient techniques to get auto number work in CRM but they are all workable solutions.

Option 1:

Very simple, supported and mostly acceptable way of auto numbering is to read the attribute Max value and then add 1 to get next auto number. For example you have a custom entity named Project and you want to auto number Project_Ref_Number attribute.  But do not retrieve all records. Just retrieve the record with maximum number value by using following two properties of PageInfo Class in SDK and set descending order.

PageInfo.Count = 1;
PageInfo.PageNumber = 1;

One more consideration should be taken in account to register your auto number plugin to at PreCreate rather at PostCreate.

Issues:

In multi-user environment, this approach can come up with duplicate numbers in Project_Ref_Number when more than one user is creating project records.

Option 2:

This technique requires a bit more work but still supported way of doing things and all the time you will be getting 100% unique number.

You need to create a new table for project entity in a different database and not in CRM default database.

create table dbo.ProjectNumbers
(
 Project_Ref_Number int identity(1,1) primary key clustered,
 Projectid uniqueidentifier not null
)
go

Create a stored procedure for inserting a record into new database. This will increase the performance of your insert query.

create procedure dbo.proc_new_ProjectNumber

@ProjectId uniqueidentifier,

@Project_Ref_Number int output

as

insert into dbo.ProjectNumber

(

 ProjectId

)

values

(

 @ProjectId

)

select @Project_Ref_Number = scope_identity()

go

 

In your Plugin/Callout:

1.   Access you database using ADO.NET

2.   Execute stored procedure and get next number from returned results

3.   Set the value of the returned number to target entity attribute approperiatly.

4.   All Done

Issues:

1.   You have to setup database.

2.   You have to read and insert into external database.

Option 3:

This option used a lock mechanism on a text file placed somewhere at your webserver. You can create one file to store next number for all entities or you can create one file for each entity. In your Plugin/Callout:

1.   Put a lock on file.

2.   Read the file and read the number there.

3.   Update the number by adding 1.

4.   Release the lock.

5.   Use this number and set your target entity attribute appropriately.

string ProjectAutoNumber = “FilePath”

lock(this)

{

                                       

TextReader textReader = File.OpenText(ProjectAutoNumber);

AutoNumber = textReader.ReadLine();

textReader.Close();

AutoNumber = (long.Parse(AutoNumber) + 1).ToString();

TextWriter textWriter = File.CreateText(ProjectAutoNumber);

textWriter.WriteLine(AutoNumber);

textWriter.Close();

}

 

Issues:

1.   Resource locking

2.   File system Read/write cost

Although all these options work well but I am still looking for some more robust solution. Ideally I am looking for something like GUID. GUID’s are instantly available and 100% unique.

For your suggestions and improvements, please comment.

Proximity Search in MapPoint – Drawing Microsoft Dynamics CRM data on Maps

Proximity Search allows you to find businesses located around a reference point you provide. For example, hotels or restaurants can provide the nearest location of all automatic teller machines (ATMs). Or a manufacturing company can list all gas stations within a short drive from a supply center. Or a retail outlet can allow website visitors to search for stores that are open early or stores that handle return items. Or you can locate your potential customers nearby an existing customer.

Proximity Searches can be utilized in MSCRM to get maximum benefit of customer data. For example, you can draw your potential customers nearby an existing customer so that you can analyze and refer your potential leads and opportunities to existing customer to get maximum business benefits. In below section, I will demonstrate the idea by showing code snippets and pictures.

The FindServiceSoap.FindNearby method is a powerful feature of the MapPoint Web Service SOAP API. Using the FindNearby method, you can add the power of proximity searching to your Web site or solution. You can perform proximity searches on regularly-updated commercial data, such as Yellow Pages listings, which are included with a MapPoint Web Service subscription. Additionally, you can upload custom data and use FindNearby in custom store-locator and brand-finder applications.
For further information on how to use this method, please refer MSDN article at http://msdn2.microsoft.com/en-us/library/ms980179.aspx.

Here are the possible steps to follow in order to draw the above example.

Following code snippets will help you in understanding different steps involved in calling FindNearBy method.

Get Longitude and Latitude for Existing Customer using Find Service

//Find Address Longitude and latitude
FindServiceSoap FindService = new FindServiceSoap ();
FindService.Credentials = new NetworkCredential(“”, “”);
FindService.PreAuthenticate = true; 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);

Specifying Search Distance and Longitude and Latitude of Origin point and custom Database from where to search for nearby points

FindNearbySpecification myFindSpec = new FindNearbySpecification();//Specify the data source.
myFindSpec.DataSourceName = “Mappoint.InstallerAccounts”; 
//Specify the latitude and longitude.
myLatLong.Latitude = 47.63873;
myLatLong.Longitude = -122.131;
myFindSpec.LatLong = myLatLong; 
//Specify the distance.
myFindSpec.Distance = 10;

Limit the number of records returned by specifying the Range property

//Set the Range property.
myRange.StartIndex = 0;
myRange.Count = 10;
myFindOptions.Range = myRange; 
myFindSpec.Options = myFindOptions;

Return only the Properties you are interested in otherwise it will return all the properties from your custom database uploaded in MapPoint

//Return only the properties that we’re interested in.
myPropertyNames[0] = “Name”;
myPropertyNames[1] = “AddressLine”;
myPropertyNames[2] = “PrimaryCity”;
myPropertyNames[3] = “RelationshipType”;
myPropertyNames[4] = “longitude/latitude”; 
myFindFilter.PropertyNames = myPropertyNames; 
myFindSpec.Filter = myFindFilter;

Specify Filter Expressions to return desired data depending on the entity properties

//Specify the text of the Expression.
string myText = “(RlationshipType = {0}  AND ” +“Rating >  {1} “;
myFilterExpression.Text = myText; 
//Set the paramater values to match the placeholders
//in the Text
string.object[] myParameters = new object[2];
string myFirstParameter = “Installers”;
myParameters[0] = myFirstParameter;
int mySecondParameter = 25;
myParameters[1] = mySecondParameter;
myFilterExpression.Parameters = myParameters;
myFindFilter.Expression = myFilterExpression;
myFindSpec.Filter = myFindFilter;

 Perform Proximity Search using FindNearby Method

myFindResults = myFindService.FindNearby(myFindSpec);

Find result contains potential customers who reside nearby the existing customer. This also specifies their longitude and latitude data. Now you can simple follow my previous post regarding MSCRM integration with MapPoint to draw these potential customers and existing customer to Map. http://ayazahmad.wordpress.com/2007/06/12/mscrm-and-mappoint-integration-a-picture-is-worth-a-thousand-words/

Possible MapPoint Integrations with Microsoft Dynamics CRM1.       Store locators
2.       Branch Finders
3.       Drawing Customers to analyze Customers
4.       Lead/Opportunity Analysis per Region
5.       Driving Directions
6.       Order Tracking/shipment Tracking
7.       Finding Points of Interests in a specific route  

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.

  

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.