Thursday, October 11, 2007

Baby, I'm Amazed...

I know you may find this hard to believe, but I don't often find myself amazed... at myself. Sure, I put a lot into my work and go the distance for my clients, figure out things, push the envelope and all that, but there are not too many times where I lean back and say, "Ahhh, that was especially well done." So when I had one of those moments yesterday, I said to myself, "Self, you need to share that with your peeps." And, well, you are my peeps. So here we go.

If you have Comcast in your area you are familiar with their services. They have TV, Internet and phone service. Getting all three gets you the bundle price and you can save some money, not to mention have unlimited local and long distance calling. Nice. So there I was online and looking at my account when I noticed that in addition to getting your voice mail messages online from anywhere (how cool is that?) that they also track the last 90 days of calls. Fun. So I scroll through the list and notice that hey, none of these calls say who the caller is, even though there is a field for it. They all say unavailable. Odd, considering I have Caller ID service on the line. Anyway, as I look over the list I recognize many of the numbers, but many I do not recognize. So I think to myself, "Too bad I couldn't cross reference these numbers with my Address Book. I bet a lot of these numbers are in there."

I know what you're thinking. "This guy has waaaay too much time on his hands." Yeah, I know. But many of the things I have been able to do for my clients comes out of this wanting to figure stuff out. To continue. I downloaded the past 90 days of caller records in .txt format and then I drag them onto FileMaker Pro 8.5. A cool thing about FileMaker – if you drag and drop a compatible file type onto the icon, it will convert it into a database for you. So I did and poof! instant database. Then I was able to scroll through the records in list mode and get rid of all the blank records and dialed and unknown number calls. What was left was a list of received calls with numbers, length of time and date received.

Next, I had to access my Address Book info. Slight problem there. There was no way of exporting the data, except as a vCard. And FileMaker didn't know what to do with it, neither did Excel. Only mail apps. Dang. So I went over to my computer resource, VersionTracker.com and did a search for Address Book to FileMaker. Voila! Someone had already solved that problem for me by writing a free AppleScript that copied the data from AB into FM. Sweet! I downloaded that and started the process. I end up with another database with my AB info. Now I had all my info in FM but I needed to cross reference the phone numbers in the AB with the phone numbers in the caller records from Comcast.

So I created a new table in FileMaker within the current database and imported the data from the AB database. Using FM's relational ability, I created a new relationship based on the phone number field from both the AB and the Comast tables. Ran a report and... nothing! No matches. So I took a look at the data and realized that Comcast formatted all the numbers as (123) 456-7890 and I had it every which way in the AB – with parens, without, with dashes, without, even using periods. But now that all the info was in the database, I could use a filter to get what I wanted from both sets of data. I created a new calculation field in each table and ran a text filter on the phone number to give me just the numbers only and then modified the relationship to be between those field instead. Yeah, baby! Then it worked. When a phone number in the call record matched a number in the AB, it took the name from the AB and added it to the call record report.

Then I played with the report to add subtotals by phone number, a grand total, save space, sort by name and number, and of course, make it look cool. Now I can just download the latest info and generate a new report anytime I want. Good news. And now I also can keep better track of my phone time with my clients. Oh, oh...