Database wizardry

Last week I wrote about making street signs for the burn, and I mentioned that each camp now has a street address that makes it easier for Rangers/emergency personnel/random hippies to find them.

What I didn't mention is that the board member whose idea this was also suggested that we provide each camp with specific directions on how to drive to their campsite upon arrival at the burn. While it was completely possible for me to write those instructions for the 40 camps we have at Euphoria, there was no way that I was going to do any such thing for the 150 camps we have at Alchemy in the fall, so it was clear I needed to automate the process.

tl;dr: I created three calculated fields in the placement database (I use FileMaker Pro for all my database needs) which could be strung together for a text that I could then mail to each camp.

Here's how.

First, here's the map:

(The numbers are for a legend, not the street addresses. Those are in "blocks."

The first field was simple, just an intro giving the camp address, i.e., "3 Old Men is at 101 The Hill."

The second field was the tough one: I had to get the hippie to the street, and then tell them which intersections to drive past until they reached their "block."

The solution was the calculation known as CASE: you can tell the database to look at some information and depending on what it finds there, do a thing.  Think of it like this: you and I, if we slowed our brains down enough to look at what we're thinking when we look at the map, would be saying,

"If it's the case that the street we're looking for is Downtown Avenue, then start by turning left onto Effigy Road — turn left onto Upper Perimeter  —  go past High Street — go past Center Camp  —  turn left onto Downtown Avenue."

We would also be thinking, "But if it's High Street we're looking for..." etc. etc.

And that's how CASE works: test the field with the street name, and depending on what it is, include some prewritten directions.

BUT WAIT THERE'S MORE!

Inside each CASE for streets — yes, you can nest these calculations — you put another CASE which tests for the street number, which then triggers which directions to include.  After all, if you and I were telling someone whose camp was in the 300 block of Upper Perimeter, we wouldn't need to tell the hippie to go past Center Camp or Downtown Avenue. They'd need to go past High Street and then start looking for their camp.

It sounds complicated, but once you sketch out the logical bones of the thing, then it's just typing:

As I look at it now, I wonder if I could reverse the street number CASE calculation and use it to keep adding instructions, i.e., <100, go here, <200, add another here, etc. — rather than have a complete set of instructions for each filter.  Oh well, it works. I don't have to make it more elegant until August.

The third calculated field I am particularly proud of, because it looks at whether the street address is odd or even and then tells the hippie that the camp is on the left or right, EXCEPT for the 100 block of Boulevard or Upper Perimeter, because the hippie has to turn right and the numbers would be on the other side:

And the end result?

Pretty slick, huh? All I had to do then was munge the three fields together into one final field, then create a script that would email each camp leader with instructions to their camp and tell them to make sure everyone in their camp gets a copy.

And that, class, is why you use a database and not a spreadsheet to manage your data.