Word 2013 templates not compatible with Office Accounting 2009

I’ve just discovered that our custom Invoice templates that we had defined in Office 2007 / 2010 no longer work in Word 2013, when we try to export an Invoice the XML is left in tact. Editing the template yields:

This is fully documented by Microsoft at http://support.microsoft.com/kb/2761189 and basically says it’s the US government’s fault!

I guess we’ll go back to Word 2010, at least it’s not all grey…

Passive (PASV) FTP doesn’t work despite setting an allowed port range in IIS7.5 (Microsoft FTP Server Windows Server 2008)

There are lots of forum posts out there explaining that you must set the port range and the IP address. Two things they don’t say (and are not obvious):

1. The port range is not set at the site level, it’s set at the server level. In IIS manager select the server name then on the right pane select FTP Firewall Support:
   
2. After setting the port range you MUST restart the Microsoft FTP Service. Start->Run->services.msc
   

There’s still a lot more that needs setting up, the above are just some additional pointers in addition to the other sources out there.

Column ‘Mycol’ cannot convert between Unicode and non-Unicode when exporting simple tables to Excel in SSIS

I’m sure that anyone who has used SQL Server Integration Services (SSIS) for more than a day has hit this really annoying ‘feature’. The problem is basically that the source / destination of MS Excel in SSIS defines all strings as Unicode. If the table you are importing to or exporting from has non-unicode (e.g. varchar vs nvarchar) fields then you’ll see error, after error saying “cannot convert between Unicode and non-unicode…”. The solution is to use a Data Conversion transformation however this is extremely tedious especially as it changes the name of every column and requires click after click after click to do something which frankly should be implicit or at worst automated.

So we end up in a situation like this:

 

Luckily Todd McDermid has written a component called “Replacing Data Conversion” that can automate the conversion for us, this is easy to configure:

 

Four clicks later we have a happy script:

Download at http://rdc.codeplex.com/ background info is at http://toddmcdermid.blogspot.co.uk/2010/08/convert-several-columns-in-ssis-with.html

Cheers Todd, you’ve saved me a lot of tedious work.

Problems Scanning with Canon Lide under Windows 7 / Windows 7 x64

Every time I went to scan with MS Office Document Imaging for my Canon LIDE 25 under Windows 7 x64 I got the error “Unable to initialize scanner. Check scanner connection and scanner software”.

It appears this is due to the driver installer not modifying the path which the Canon twain driver requires. Follow the instructions at http://weblogs.asp.net/jeffwids/archive/2009/11/02/canon-scanner-unable-to-open-twain-source.aspx and the problem goes away.

 

Field Naming convention for Salesforce.com and database tables

UPDATE Jan 2022 – Check-out the revised version of this post on our company blog at https://provenworks.com/field-naming-convention-for-salesforce-and-database-tables/

Below is a variation of a naming convention that I use within database and Salesforce.com systems that I manage, to be clear I am talking about the API name or underlying table names and not the labels that are exposed to users. I would like to stress that the most important thing is to have a naming convention what that convention actually is is of secondary importance. You will know that you have a good convention when you get the field/API name correct >90% of the time without actually knowing the table well simply because you know how it would have been named by its purpose.

Name your fields

If you’re reading this then you’ve probably already determined that you need to think about the naming of your fields. On a new field creation Salesforce.com first asks for the label. It then creates an API name based on this label, thus you can get:

Label=”Last 2 letters of Mother’s maiden Name”,
API name = “Last_2_Letters_of_Mother_s_maiden_Name__c”

Note that the spaces and apostrophe all become underscores making the ‘s’ stand on its own and the API name overly long (those underscores aren’t helping us at all). Also if the label changes (e.g. “2”->”two”) then confusion ensues as using this ‘convention’ the developers will always assume that the API name matches the label.

Naming conventions

Field names should use Upper Camel Case or Pascal casing, excerpt from http://en.wikipedia.org/wiki/CamelCase.

“CamelCase (also spelled “camel case” and sometimes known as medial capitals[1]) is the practice of writing compound words or phrases in which the words are joined without spaces and are capitalized within the compound — as in LaBelle, BackColor , or iMac. The name comes from the uppercase “bumps” in the middle of the compound word, suggestive of the humps.”

Try to avoid using abbreviations unless they are very widely used i.e. Id is fine instead of Identifier, Pro_Serve is not a good abbreviation of Professional Services as it is not consistent in its number of characters for each word and “Serve” is not a known abbreviation of “Services”. See later about verbosity in naming.
Acronyms are treated as if they were words, so SFDC Account Id would become SfdcAccountId – whilst this one isn’t intuitive it is necessary to properly separate the words out correctly. If SFDC were treated as all capitals many tools would (like SSRS) convert it to “S F D C Account Id” which is ‘more’ wrong. In this example “SalesforceAccountId” would be best.

Grouping

The underscore (_) character can be used after a field prefix to logically group fields together for example:

Software_Amount becomes Amount_Software
Maintenance_Amount becomes Amount_Maintenance
Training_Amount becomes Amount_Training

This grouping means that when fields are sorted alphabetically logically related fields appear together, very useful in implementations with >150 fields. Grouping is easily achieved by slight word re-arrangement for example; although we would usually verbally say “Date Created” here we name the field CreatedDate.

Grouping (underscore) isn’t absolutely necessary for field ‘pairs’ for example CreatedBy and CreatedDate or Contact.AccountId and Contact.AccountType (as the two fields will always be populated together). However if you anticipate other fields being added to this theme then grouping can be beneficial and makes reviewing the column/field list much easier.

Choice of words & Verbosity

This only outlines the conventions for naming, the actual choice of words is a task best proposed by one individual and then reviewed by others to ensure that the words are a good reflection of the purpose of the field without extra clarification. It is very rare that an individual gets the naming right alone. Bounce the ideas and then finally have the actual text review for typos (!!!).

However great the temptation to “quickly create a field” you should always resist, spending literally a few minutes carefully considering the naming will at a minimum save you time later and more importantly prevent a field being misinterpreted and thus wrong business decisions being made.

Remember it is better to be unambiguous and have a long field name than a short field name that is open to interpretation. If a developer complains that it takes too long for them to type a long field name then I’d suggest they’re in the wrong profession if they struggle typing a dozen extra characters.

Virgin Media D-Link DIR-615 Router doesn’t support VPN-out (PPTP)

My mum has recently been given a D-Link DIR-615 wireless-N router from Virgin Media with firmware revision V1.00VG and HW revision D4. This was working fine until today when I tried to VPN-out using standard Windows Networking and got it hanging at “Authenticating Username and Password”.

It appears that this is a common complaint with Virgin’s firmware and at the time of writing (9 Jan 2011) no updated firmware is available to resolve it. I’ve searched various forums and found no solution, my guess is that it is NATing the TCP1723 but not passing through the GRE.

I solved this by using the open-source DD-WRT firmware which I put on all of my routers (Buffalo / Linksys) usually anyway. I hadn’t done this previously as Virgin won’t support it and if it breaks most likely won’t replace it either – you’ve been warned. On the flip-side the last equivalent router I bought (Buffalo) was £22 from eBuyer so I wouldn’t worry too much if you need to buy an equivalent to replace it.

Steps to get it working (correct as of 9 Jan 2011) – print / save this page before getting started!

1. Go to www.dd-wrt.com
2. Go to Router Database
3. Enter “DIR-615”, click the HW revision that corresponds to the sticker on your router
4. Download the “factory-webflash” file to your harddrive
5. Now, using a wired network connection go in to maintenance and upgrade firmware. Specify the file you just downloaded. Note you can use a wireless connection but it’s safer to use a cable.
6. After a minute or so the router will be flashed with the DD-WRT firmware. This means that all of your settings will have been reset. If you are connecting wirelessly then a network called dd-wrt will appear with no password which is your router
7. Go to http://192.168.1.1 to get in to the setup.
8. Setup a username & password as prompted
9. Setup your wireless network

You should now find you can VPN out as you should’ve been able to originally. Why VM supply routers like this is beyond me.

When is a Salesforce / Dynamics / any other CRM Field no longer useful?

One of the many advantages of modern CRM systems is that it is very easy for business users to add additional fields. One of the main disadvantages of modern CRM systems is that it is very easy for business users to add fields without giving thought to the ‘bigger picture’. In this blog post I will discuss my approach for determining when to remove that field from the system.

If you don’t read any further than this the basic principal is that when a field is no longer trustworthy there is point keeping it in your production CRM system.

Can we trust this field?

What do I mean by that? Well a field is untrustworthy if you (and no one else) can’t find a consistent process for populating or maintaining that field despite reasonable efforts to do so. Let’s take an example of an ‘Account’ field that says “Number of Employees locally”:

1. Who collects/populates it? Is it the Sales Rep? Is it the marketing department ? Is it from a 3^rd party data provider?
   – Don’t know and can’t find out? – back it up and delete it!
2. When is it collected? At time of record creation? When the Account becomes a customer? When there’s an opportunity?
   – Remove it
3. When was it first ‘put into production’? This is vital if we are doing any kind of historical analysis.
   – If the field is only there for trend analysis, the data appears patchy then it’s useless.
4. When was it last updated? What’s the worst case?
   – no historical value? lose it.
5. Do we actually understand what the field means? Is it unambiguous?
   – it’s useless

Do we understand what the field means?

I will write a follow-up post on field naming conventions, however what we must do is look at the field name / label and determine if the question used to populate the field is at all ambiguous? Even if the IT/IS/Sales Ops department understand the meaning of the field do the people who are actually populating it? What about users who primarily speak different languages?

An example of an ambiguous field I saw recently was on an account and just said “MSP Customer” where MSP means Managed Service Provider. Due to my client’s channel sales model it was not clear if this field meant the account was a “Managed Service Provider” themselves or if this account was effectively an indirect customer since they purchased through a Managed Service Provider.

What is the purpose of having a field anyway?

I think that this is the most important point, we create a field for either (or both) of the following purposes.

1. To mark a record as being at a certain stage in a process.
2. To record data for later analysis (reporting).

If a given field is not adding any value to a current business process and it has no historical value for the reasons listed above, back it up and delete it before someone makes a business decision based on its content!

SSRS Report Builder gives “Failed to preview report.” – “Specified method is not supported.”

This one had me stumped for a little while. It appears to be as a result of using a report that uses a Report Model that in turn is using a data source that uses the Native SQL Server driver which is presented just as “Microsoft SQL Server on the data source”:

This should be changed to OLE DB and an appropriate Provider= entry made:

This was with Report Builder 3.0 / SQL Server 2008 R2 but I would imagine it affects previous versions aswell.

Can’t burn CD/DVD under Windows Server 2008 R2

I use run Windows Server 2008 R2 and today thought I had a hardware issue as all of my CD burning software was saying “Connect a burner and restart the player” (Windows Media Player) or “No recorders available” (Infrarecorder).

After some hunting about I found the following post at http://blogs.technet.com/b/askcore/archive/2010/02/19/windows-server-2008-r2-no-recording-tab-for-cd-dvd-burner.aspx

“By default accounts other than localsystem\administrator (regardless of the groups it belongs to) on server SKUS are considered to be “remote desktop” and so have the same security restrictions that come with remote desktop sessions.

In order to burn CD/DVD’S you can do one of the following:

• Login as localsystem\administrator
  
  
• Run a 3^rd party burning utility elevated
  

Note: You must have the Desktop Experience pack installed to get the built in Windows ISO burning applet.

Scott McArthur
Senior Support Escalation Engineer
Microsoft Enterprise Platforms Support”

I first tried the second solution but I assume that since Infrarecorder is fully Windows 7 aware there is no “Run As Administrator” option, yes, I could do RunAs from the command line but as I’m recording off of the network this still isn’t elegant.

So I gave in and logged on as local administrator and it all worked again. I’m not going to bitch too much as really I know most people won’t be burning DVDs on a server OS and I’m pretty unusual in using it as my desktop OS. Hopefully

Function to Convert 15char Salesforce.com IDs to 18char ones in T-SQL (SQL Server)

If you’ve ended up here then you’ve already experienced the pain that is the fact that internally Salesforce use 18char IDs for everything, unfortunately the user is always presented with 15char versions. The additional 3 characters on the 18char version allow it to be compared case insensitively and so is much safer for handling within for example SQL Server when using a case insensitive collation.

The code below is based on the description and Dave‘s Javascript version found in this forum post.

I hope you find it useful:

* Note updated 13 June 2010 following feedback from David Eisaiah Engel

Download the SQL script here.

-- =============================================
-- Author:        Joel Mansford
-- Create date:     Jan 2010
-- Description:    Converts 15char SF Ids to 18char ones
-- History:
--  June 2010 - issue with over-running and incorrectly producing ']' as a character with input such as '0033000000dGpDN'
-- =============================================
CREATE FUNCTION fnConvertToCaseInsensitiveId
( @InputId CHAR(15)
)
RETURNS CHAR(18)
AS
BEGIN
-- Hacky way to raise an error but it works and there’s no alternative!
DECLARE @ErrorStringReally INT
IF LEN(@InputId)<>15

SET @ErrorStringReally =‘Input Salesforce Id must be exactly 15 characters input was “’+ @InputId+‘”’

DECLARE @OutputId CHAR(18)

DECLARE @Hash VARCHAR(3)

SET @Hash = ”

DECLARE @Chunk tinyint

DECLARE @ThisChunk CHAR(5)

DECLARE @CharPos tinyint

DECLARE @ThisHashDigit tinyint

— Split string in to 3 chunks of 5chars

SET @Chunk = 1

WHILE @Chunk<=3

BEGIN

SELECT @ThisChunk = RIGHT(LEFT(@InputId,@Chunk*5),5)

SET @ThisHashDigit = 0

SET @CharPos = 1

— Iterate over the chunk

WHILE @CharPos<=5

BEGIN

IF ASCII(SUBSTRING(@ThisChunk,@CharPos,1)) BETWEEN 65 AND 90 — If Uppercase

— then add a binary ’1′ digit in the appropriate position, otherwise it’s still 0

SET @ThisHashDigit +=POWER(2,@CharPos–1)

SET @CharPos+=1

END

IF @ThisHashDigit>=26

— Digits 0-9, minus 26 as SFDC have numbers come ‘after’ letters

SET @Hash +=CHAR(@ThisHashDigit+48–26)

ELSE

— Letter ‘A’

SET @Hash +=CHAR(@ThisHashDigit+65)

SET @Chunk+=1

END

SET @OutputId = @InputId + @Hash

RETURN @OutputId

END