Massive turn out at 3am for the Microsoft 2010 PDC keynote live at Microsoft

Local foursquare checkin in Hornsby. Yep Ian got busted!

During our Microsoft CRM 4 development a consultant implemented a plugin that calls a webservice to validate an ABN whenever this field was changed in CRM.

As I was doing the data migration of 10,000 plus customers and was going to have to validate this data before going into the new system rather than after and trapping an error I decided to use a SQL function to validate this data which also allowed me to identify bad records to be updated and could easily dump out to a spreadsheet for someone to fix.

Based on the rule for an Australian ABN that validates via a check digit on the number, I created a SQL Server function that I could use in my query to test at query time.

First the rules to verify an ABN:

1. Subtract 1 from the first (left) digit to give a new eleven digit number
2. Multiply each of the digits in this new number by its weighting factor
3. Sum the resulting 11 products
4. Divide the total by 89, noting the remainder
5. If the remainder is zero the number is valid

Weight factors (for Step 2)

SQL Server ABN Function

Usage:

Valid ABN for my favourite drink company - Red Bull Australia Pty Ltd :)

SELECT dbo.udf_IsValidABN(‘67 085 840 259’) as IsValid

Returns 1 as correct ABN

SELECT dbo.udf_IsValidABN(‘67085840259’) as IsValid

Returns 1 as Correct ABN without SPACES

SELECT dbo.udf_IsValidABN(‘69 085 840 259’) as IsValid

Returns 0 as Not a valid ABN

Here is the sql for the function.

CREATE FUNCTION [dbo].[udf_IsValidAbn] (@ABN VARCHAR(20))
RETURNS BIT
AS
  BEGIN

     -- Strip out spaces in ABN
      SELECT @ABN = REPLACE(@ABN, ' ', '')

      DECLARE @Valid BIT
      DECLARE @1 INT
      DECLARE @2 INT
      DECLARE @3 INT
      DECLARE @4 INT
      DECLARE @5 INT
      DECLARE @6 INT
      DECLARE @7 INT
      DECLARE @8 INT
      DECLARE @9 INT
      DECLARE @10 INT
      DECLARE @11 INT
      DECLARE @RESULT INT
      DECLARE @FINALRESULT INT

      SET @Valid = 0

      IF Len(@ABN) <> 11
        BEGIN
            RETURN @Valid
        END

      SET @1 = ( LEFT(@ABN, 1) - 1 ) * 10
      SET @2 = Substring(@ABN, 2, 1) * 1
      SET @3 = Substring(@ABN, 3, 1) * 3
      SET @4 = Substring(@ABN, 4, 1) * 5
      SET @5 = Substring(@ABN, 5, 1) * 7
      SET @6 = Substring(@ABN, 6, 1) * 9
      SET @7 = Substring(@ABN, 7, 1) * 11
      SET @8 = Substring(@ABN, 8, 1) * 13
      SET @9 = Substring(@ABN, 9, 1) * 15
      SET @10 = Substring(@ABN, 10, 1) * 17
      SET @11 = Substring(@ABN, 11, 1) * 19

      SET @RESULT = @1 + @2 + @3 + @4 + @5 + @6 + @7 + @8 + @9 + @10 + @11

      SET @FINALRESULT = @RESULT % 89

      IF @FINALRESULT = 0
        BEGIN
            SET @Valid = 1
        END

      RETURN @Valid
  END

GO

-- Don't forget to set appropriate permissions. As this is a standard calculation have set to public

GRANT EXECUTE ON [dbo].[udf_IsValidABN] TO [public] AS [dbo]

GO 

I also implemented the same thing to validate Australian company ACN as it used a slightly different algorithm. Will also post this function up if anyone is interested just reply in comments.

Since Outlook 2000 (possibly earlier) you could create an Outlook Template file (.oft) by creating a message and saving as a Outlook template.

There have been many suggestions such as adding to the Outlook toolbar or double clicking the Template file from a folder with your saved templates.

Lets take this one step further with Windows 7 jumplists and integrate into the toolbar.

Figure: Jumplists are shown by right clicking on icon on toolbar

First lets create a template by creating a New email

Figure: A Meeting Agenda is a good template to have

Once we have created this Click File > Save As and store in either the default template location of under your documents

Figure: Outlook 2010 Save Dialog but this will work in Outlook 2003/2007 as well

Change the File type to .oft (Outlook Template file)

Figure: Save As dialog. Change the save as type 

Now open the folder in Windows Explorer where you saved the template and drag onto the Outlook icon pinned onto your taskbar (Pin Outlook if you haven’t already first). If you drag onto a blank part of the taskbar you will get same result.

Now when you right click Outlook you can select the Template pinned to the top of the jumplist and with 1 click open Outlook and launch new email with your template.

Happy trails!

At the recent Sydney.Net User Group TJ Gokcen was showing a bug that required him to launch a Silverlight Website in Chrome to reproduce.

The workaround to launch Chrome by changing default browser was quite cumbersome and I mentioned a great Visual Studio 2010 Extension that makes it easy to Start > Debug into a different browser at the click of a button. This is great for checking CSS etc in different browsers rather quickly.

To install you can either go to the following URL or Open up the VS2010 Extension Manager by clicking Tools > Extention Manager

Figure: Under tools Menu is the new VS2010 Extension Manager

Click Online Gallery and Search for “Browser switcher” then click on the World of VS Default Browser Switcher and install.

Figure: Searching for Extensions in VS2010 is ever so easy

Once installed it will require a restart of VS2010 and then you need to turn on the toolbar by Right Clicking a blank area of the toolbar and selecting Default Browser Switcher (it would be nice if it did this automatically on install) 

Figure: Adding the toolbar to VS2010

Now you have a toolbar that you can click the appropriate browser icon and F5 to Start Debugging which will launch the ASP.NET Website in that browser.

Note: Even though it shows other browsers such as Opera and Safari which I do not have installed, it is smart enough to now allow you to select these if not installed :)

Figure: New toolbar with browser icons

I so wish Blizzard would do a full length feature as this intro is rockin good. It’s too bad I gave up the Warcrack 3 years ago.

(Source: youtube.com)

What an awesome looking keyboard. Might be a purchase with the new desktop PC looking to build