Sage CRM and DotMailer Integration

We have started work on a simple integration for Sage CRM and the DotMailer e-marketing platform. It is very much on the KISS (Keep It Simple Stupid) premise with the following features to be available from launch:

  • Upload of contacts from Sage CRM to DotMailer (firstname, lastname, email address and territory)
  • Updating of opt-out flags in Sage CRM from DotMailer

DotMailer has a far, far better interface for generating and sending e-marketing campaigns so no campaign management will be put into Sage CRM by this integration.

Watch this space – a register interest form will be added here in the next couple of days.

Pricing to be confirmed.

Remove password reveal in Internet Explorer

A feature which has found its way into smart phones has unfortunately made its way into traditional PC browsers as well. The password reveal icon.

From a security point of view, it is an odd feature to even consider having – inevitably, someone will type their password into such a box and walk away from the their PC before hitting the sign-in button and you can guess the rest.

Anyway, if, as a responsible web developer you want to disable this feature, the following in the CSS will get rid of it:

::-ms-reveal { display: none; }

Farstate FTP Interface

Farstate specialise in web based interfaces to FTP systems, allowing your user’s to create and manage their own FTP accounts, and easily share large files with others. The Farstate FTP Interface works with the ProFTPd FTP server and supports both standard FTP and FTP over SSL.

Have you ever wanted to run your own FTP server but want to allow your staff or contractors to create their own FTP accounts and not tie up your IT department with continual account creation? The Farstate Interface provides an intuitive, easy to use web based front-end to the ProFTPd FTP server which allows accounts to be created and shared between one or more contacts. When an account is created, all recipients are notified automatically by email.

Both temporary and permanent FTP accounts can be created. Automatic house keeping routines keep check of temporary accounts and old files to help keep disk space usage to a minimum and ensure that a continual build up of files is avoided.

Farstate FTP Interface can be provided as a software only package to existing ProFTPd installations or supplied as an appliance package where all you need to do is plug in the server and off you go.

Sage CRM – Performance Tips

Here are a few tips that may help increase the performance of your Sage CRM installation.

1. Clean out the activity table

  1. Go to Administration > Users > User Activity
  2. Click the Archive to File button
  3. Choose which records to archive (Records older than…)
  4. Click the Archive to File button

2. Turn off unneccessary notifications for orders and quotes

  1. Go to Administration > Customisation > Orders (or Quotes)
  2. Select the Notifications tab
  3. Uncheck any enabled notificatios that you don’t want anymore

3. Turn off or reduce logging when you aren’t troubleshooting

  1. Go to Administration > System > Logging
  2. Click Change
  3. Change the System Logging level to 1 – Minimum or 0 – Off
  4. Click Save

4. Use the dashboard screen as the default screen for users

  • Calendar takes the longest to load

5. Make sure that the Document Drop plug-in is installed

  • If it isn’t, whenever the user visits a page that has the document drop, a plug-in install message will appear.

6. Browsing history & temporary internet files

  • A good housekeeping task is to delete temporary internet files and browsing history on a regular basis.

7. Shrink SQL log file

  1. Open SQL Server Management Studio
  2. Run the query below (at your own risk and advise to backup the database first!)
ALTER DATABASE  SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(, 1)
ALTER DATABASE  SET RECOVERY FULL WITH NO_WAIT

Tips 1 – 3: Credit to Stacey Denunzio @ Axis Integrated Solutions for these tips. A fourth tip from Stacey to reduce the Outlook auto-sync interval was also suggested but I couldn’t this option in Sage CRM 7.1. [Original article in full]

Tips 4 – 6: Credit to Greytrix for these tips. [Original article in full]

Tip 7: Credit to Ronnie Martin @ Ignite. [Original article in full]

Sage CRM – Web Services & C#

Most of the documentation for Sage CRM & web services is for Visual Studio 2005 where one would add a web reference (“Add Web Reference”). In VS2008 onwards, you get a “Add Service Reference” option instead. I should add that this article focus on using Sage CRM web services in a C# ASP.NET project although I would imagine (but have not tested) that the same code will work in a Windows Forms project.

Logon

Before you can query, add or update records in CRM, your code will need to login:

private WebServiceSoapPort oCRMServiceZ = new WebServiceSoapPortClient();
logonRequest oLogonRequestZ = new logonRequest();
oLogonRequestZ.username = "admin";
oLogonRequestZ.password = "";
logonResponse oCrmLogonZ = oCRMServiceZ.logon(oLogonRequestZ);
SessionHeader oCrmSessionZ = new SessionHeader();
oCrmSessionZ.sessionId = oCrmLogonZ.result.sessionid;

Creating, Querying & Updating Records

Once logged in, you are free to create, update and query records in CRM. There is little documentation but as I find/work things out, I will add to the list below.

References:

Sage CRM – Create Communication via web services

Creating communications is one of the more useful things that I have found requirements for in day to day operations. For example, if you have an ASP.NET licensing application and want to record the license against a person or company in Sage CRM, then a communication is an ideal location.

Before you can create a communication, your code needs to “logon” to CRM. Best practice is to also logoff afterwards.

protected void BtnCreateComm_Click(object sender, EventArgs e)
{
communication oCommZ = new communication();

oCommZ.companyid = 1234;
oCommZ.companyidSpecified = true;
oCommZ.personid = 4567;
oCommZ.personidSpecified = true;
oCommZ.subject = "Test Subject";
oCommZ.description = "Test Description";
oCommZ.note = "Test Note";
oCommZ.userid = 1;
oCommZ.useridSpecified = true;
oCommZ.datetime = DateTime.Now;
oCommZ.datetimeSpecified = true;
oCommZ.channelid = 1;
oCommZ.channelidSpecified = true;
oCommZ.type = comm_type.TasksOnly;
oCommZ.typeSpecified = true;
oCommZ.action = comm_action.ToDo;
oCommZ.actionSpecified = true;
oCommZ.status = comm_status.Complete;
oCommZ.statusSpecified = true;
oCommZ.priority = comm_priority.Normal;
oCommZ.prioritySpecified = true;

ewarebase[] oCrmCommBaseZ = new ewarebase[1];
oCrmCommBaseZ[0] = oCommZ;

addRequest addreq = new addRequest();
addreq.entityname = "communication";
addreq.records = oCrmCommBaseZ;
addreq.SessionHeader = oCrmSessionZ;
oCRMServiceZ.add(addreq);
}

The above code creates a communication and adds it to the specified (hard coded in this example) company and person.

One point you may notice is that several oComm variables have a second similar line where that second line has Specified = true. When you specify variables that have controlled values, you also must state that that variable has been specified.

The code below is a complete, working example from a web app. These instructions assume Visual Studio 2010 – other versions should be OK. To test/use:

  1. Create a new C# web application (or use an existing one)
  2. Add a “Service Reference” (right-click on the name of the project in the solution explorer and select “Add Service Reference”
  3. Enter the URL to the WDSL file (will be something similar to http://myserver/crm/eware.dll/webservice/webservice.wsdl)
  4. Create a new web form
  5. Create a button and add a “Click” event handler for it
  6. Add the code as below. There are three main areas of the code to copy into your class (indicated below).
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using MyWebApp.CRMWebService;

namespace MyWebApp.Modules.CRMTest
{
public partial class Communication : System.Web.UI.Page
{
// 1. Copy next two lines below your class opening bracket:
private WebServiceSoapPort oCRMServiceZ = new WebServiceSoapPortClient();
private SessionHeader oCrmSessionZ = new SessionHeader();

protected void Page_Load(object sender, EventArgs e)
{
// 2. Copy next 5 lines into the Page_Load()
logonRequest oLogonRequestZ = new logonRequest();
oLogonRequestZ.username = "admin";
oLogonRequestZ.password = "";

logonResponse oCrmLogonZ = oCRMServiceZ.logon(oLogonRequestZ);

oCrmSessionZ.sessionId = oCrmLogonZ.result.sessionid;
}

protected void BtnCreateComm_Click(object sender, EventArgs e)
{
// 3. Copy the next lines into the click event handler. Change the company id
and person id to an actual company id and person id.
communication oCommZ = new communication();

oCommZ.companyid = 1234;
oCommZ.companyidSpecified = true;
oCommZ.personid = 4567;
oCommZ.personidSpecified = true;
oCommZ.subject = "Test Subject";
oCommZ.description = "Test Description";
oCommZ.note = "Test Note";
oCommZ.userid = 1;
oCommZ.useridSpecified = true;
oCommZ.datetime = DateTime.Now;
oCommZ.datetimeSpecified = true;
oCommZ.channelid = 1;
oCommZ.channelidSpecified = true;
oCommZ.type = comm_type.TasksOnly;
oCommZ.typeSpecified = true;
oCommZ.action = comm_action.ToDo;
oCommZ.actionSpecified = true;
oCommZ.status = comm_status.Complete;
oCommZ.statusSpecified = true;
oCommZ.priority = comm_priority.Normal;
oCommZ.prioritySpecified = true;

ewarebase[] oCrmCommBaseZ = new ewarebase[1];
oCrmCommBaseZ[0] = oCommZ;

addRequest addreq = new addRequest();
addreq.entityname = "communication";
addreq.records = oCrmCommBaseZ;
addreq.SessionHeader = oCrmSessionZ;
oCRMServiceZ.add(addreq);
}
}
}

Handling 404 Errors in ASP.NET (C#)

404 errors can make or break a website visit for someone – displaying the standard IIS error message is really a bit lazy and doesn’t give any indication to the visitor as to which site they are on or what to do next.

Errors can be handled via IIS but when creating an application, it is sometimes nice to keep everything under one roof. Fortunately, there is a rather nice solution… thanks to Pavel Kotlyar… The original blog post can be found here but for the sake of preserving the information, I have copied the solution below.

First, create a 404 page. Can be 404.htm or 404.aspx or any name you want really. For this example, I have gone with 404.aspx, using an aspx file because of controlling the website look and feel via master pages. In the Page_Load(), add the following line:

Response.StatusCode = 404;

Second, open the Global.asax file (if it doesn’t exist, create it!), find the “Application_Error()” bit and change to the following:

protected void Application_Error(object sender, EventArgs e)
{
Exception ex = Server.GetLastError();
if (ex is HttpException)
{
if (((HttpException)(ex)).GetHttpCode() == 404)
Server.Transfer("~/Errors/404.aspx");
}

// Code that runs when an unhandled error occurs
Server.Transfer("~/GenericError.htm");
}

Publish or debug your application, type in a non-existent page and if you should see your 404 page. If you are using Firebug (or similar), you can see the actual response code returned.

Sage CRM & Swiftpage Emarketing – The forbidden character!

I have spent hours trawling through line after line of exported CSV data looking for a reason why Sage CRM Emarketing kept spitting out the error “E-Mail blast creation failed. Error System.IndexOutOfRangeException: Index was outside the bounds of the array.” when trying to send an eblast. Repeated calls to the reseller and from them to Sage yielded little in the way of clues other than a “probably a data issue” response.

 

Looking through the exported group in Excel didn’t really help – we found the odd misplaced bracket and comma, but nothing that should have created the above error. Eventually, I exported the data to CSV format and opend in Notepad++, turning on the show all characters function.

This revealed a few erroneous line breaks from an old data import – naturally, I got very excited that the issue was now solved. We ran the Eblast again, sat back, and waited for the auto-replies and out-of-office messages to come flooding in… but no… a couple of minutes later, our friendly message was back.

Things were getting desperate. We started scanning the data for any character that had some chance of causing the error. Two records had double quotes in the company name, we removed the double quotes, pressed send and yes… it worked!!

So, although double quotes are a perfectly valid character to have in a company name, the Sage CRM Emarketing module (powered by Swiftpage) fails. So, moral to the story, leave out the double quotes.

Searching for secondary addresses in Sage CRM

One of the downfalls (in my view!) of the Sage CRM keyword search is that it only searches primary address, that is, the default address for a company record and the default address for a person record. Sage CRM does allow multiple addresses to be added for each company (and person) via the address tab (see screenshot). However, the additional addresses cannot be searched… unless…

The sales team are encouraged to search for companies by postcode so this solution only covers adding postcodes to the search index.

Sage do not encourage the direct editing of underlying data in the CRM database. However, in the real world, sometimes you need to. Only do the following if you are confident with TSQL and MS SQL Server. Not warranty or liability accepted or implied!!

  1. Create a custom field on the company entity. Choose multiline text as the field type. Good practice is to put your own prefix after the Sage field name prefix. E.g. Sage CRM always starts field names comp_ for a company, pers_ for a person, comm_ for a communication, etc. For your field names, use something like comp_memyfieldname, comp_meanotherfield, etc where “me” is your prefix. It really helps to identify your fields from the Sage ones. For the purposes of this example, this custom field will be called comp_mepostcodeindex,
  2. Create a stored procedure
  3. Next, you need to get a list of all postcodes for each company. You can do this by concatenating all matching addresses for each company into a single row for each company. A superb way to do this was posted on Stack Overflow and I have used that in this solution. Building on the SO code, the results are added to a temporary table so you will need to make sure that the user who runs the stored procedure and SQL Agent job have permissions to create and delete temporary tables.
  4. Next, an UPDATE statement can be run to update the comp_mepostcodeindex field with the list of postcodes held for each matching company. To trigger the search index process to index the updated company records, update the comp_updateddate field to GETDATE() (i.e. the current date & time)
  5. To schedule the stored procedure, create a SQL Agent job. If you don’t know how to do this, you probably shouldn’t be trying the above!

Stored procedure

Create a “New Query” window for the CRM database and then run the code below to create the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Farstate
-- Create date: 14/03/2013
-- Description:	Make postcodes in secondary addresses
-- indexable for the Keyword Search. Tested in Sage CRM 7.1
-- =============================================
CREATE PROCEDURE

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF OBJECT_ID('tempdb..#TempPCList') IS NOT NULL DROP TABLE #TempPCList

	SELECT DISTINCT ST2.[AdLi_CompanyID],
				   substring((Select ','+ST1.[Addr_PostCode]  AS [text()]
					From dbo.[vAddressCompany] ST1
					Where ST1.[AdLi_CompanyID] = ST2.[AdLi_CompanyID]
					ORDER BY ST1.[AdLi_CompanyID]
					For XML PATH ('')),2, 1000) [vAddressCompany]
		INTO #TempPCList
			 From dbo.[vAddressCompany] ST2;

	/*SELECT * FROM #TempPCList;*/

	UPDATE [Company]
		SET comp_mepostcodeindex = #TempPCList.[vAddressCompany], Comp_UpdatedDate = GETDATE()
		FROM #TempPCList
		WHERE [Company].[Comp_CompanyId] = #TempPCList.[AdLi_CompanyID] AND Comp_UpdatedDate >= CONVERT(date, GETDATE());
END
GO

Google Analytics and Webmaster Tools – Different Average Position for Keywords

Have you ever wondered why Google Analytics and Google Webmaster tools show different average positions for searched keywords? This is because by default, Webmaster Tools only shows searches via Googles web property. Google Analytics shows all web properties (web, images, videos and mobile). Generally, your keyword positions via image and video searches will be much, much lower than those from web searches.