Skip to main content


Showing posts from April, 2009

Design for Exceptions

This post is part of an ongoing seriesDesign for ExceptionsSpreading the Sheets and Letting Relations InForming a New RealityThere's more to come... we're starting simple and working up... this will slowly become more complex to include SQL Server, ASP.Net web apps and ArcGIS.

One of the biggest mistakes I run into every day is databases and spreadsheets which weren't designed for the exceptions. I get it, most of your data fits in your model. But then you run into data that doesn't fit your world view and you call me. And that's when things get ugly.

We need an example.

Let's use ZipCodes. We all know how these work right? Each city has a zip code. Here's some examples from here in Southwest Idaho:
CityZipCodeEagle83616Kuna83634Idaho City83631
That wasn't so hard was it? Our spreadsheet is done!

Not so fast cowboy. What do we do with Meridian? It has three Zips: 83642, 83646, 83680.

You've seen this before though and you got it taken care of don&…

An Inquiry into IN Query

I had to select a ton of records today for a project in ArcGIS. You know the normal method, hellish SQL repetition: ID = 1 OR ID = 5 OR ID = 6 OR ID = 12 OR ID = 14 OR ID = 27 OR ID = 41 OR ID = 43

The past few months I've been working a lot in SQL Server and this experience made this repetition seem like too much work. So I got lazy and I made an inquiry into IN query: ID IN (1,5,6,12,14,27,41,43)

And it just worked. There's no documentation in the main help file that even mentions it. If you do open the ArcGIS Help and search on "SQL Reference" nearly half way down you find a section on Subqueries. Here they explain that IN queries are supported in geodatabases and EXISTS is supported as well! Who knew? How was I supposed to know this if I hadn't gotten lazy?

Here's what you'll find in the ArcGIS Help File:
NOTE: Coverages, shapefiles, and other non-geodatabase file-based data sources do not support subqueries. Subqueries done on a versione…

Making Macro Security Warnings Disappear

You know those annoying Microsoft Office Macro Security Warnings? There is a way to make them disappear, at least for you.

Here's the Steps.
Find the Microsoft Office Tools folder generally under Start > All Programs > Microsoft Office > Microsoft Office ToolsSelect Digital Certificate for VBA Projects (if you don't have such a link see this article) Fill out the form and name it "Whatever makes you happy"Open your Microsoft Office Application and Open Visual Basic EditorNavigate to the Macro that is causing you WarningsGo to Tools > Digital Signature and click ChooseHighlight the Certificate you just created, e.g. "Whatever makes you happy"Restart the Microsoft Office Application
This time when you get the security warning choose, "Always Trust this Publisher"No more messages!

Attachment Reminder - and more for MS Outlook

I just did it again. We don't like to admit it, but we all have. You write a long letter describing the attachment, press send and then 10 seconds later remember you didn't actually attach the message.

I finally decided to do something about it.

Turns out it isn't too hard. Chiefly because Jimmy Peña at Code for Excel and Outlook already did all the hard work of writing up an excellent MS Outlook Etiquette Check Macro that does all the dirty work for you.

What's left for you to do?
In MS Outlook go to Tools > Macros > Visual Basic EditorUnder the Project Panel (far left) Browse to Project1 > Microsoft Office Outlook Objects > ThisOutlookSessionDouble-click ThisOutlookSesson to Open (if you haven't been here before this will be a big blank canvas)Visit Code for Excel and Outlook Etiquette Check Code and select "Copy to Clipboard" at the top of the code. Or you can also copy from the code I've modified below if you prefer.Go back to Visua…

HowTo Remove Annoying Advertisements and Take Back Your Browser

I finally got completely fed up with Yahoo Mail today. Consistently the ads would stall the browser and slow down response times, never mind just being annoying.

I've known about Greasemonkey for years but never had the incentive until today to see if I could nuke these annoyances.

It was embarrassingly easy.

Step 1: Get a real browser. Download Firefox.
Step 2: Download and install the Greasemonkey Add-on.
Step 3: Install the Yahoo Mail Cleaner script for Greasemonkey.

Viola! No more ads. And better yet, this isn't a black box, it's ludicrously simple javascript:

// ==UserScript==
// @name Yahoo Mail cleaner
// @namespace
// @description Removes ads from Yahoo Mail (AJAX)
// @include http://**
// ==/UserScript==

if (window.wrappedJSObject.kPartner) {
window.wrappedJSObject.kPartner.bucket = 0;

One if block. It's embarrassing that I lived with ads that long.

But why stop there.

I don'…