Creating a Mail Merge from Contacts in Highrise

I've started using Highrise from 37 Signals to manage sales prospects for the SQIP Patent Sequence Database. My requirements for a CRM system are pretty basic. Highrise fits the bill for now, but it's OK - not great.

One glaring problem for me is that I want to create a file of all contacts and their addresses for a physical mailing. In Highrise I record the address for each Company and then just record the company for each Person. When you dump your contacts out as a CSV file 'Person' records do not inherit the address of the linked Company... really, it doesn't...

So I wrote a Ruby script to create what I want from the CSV file. You can find that here: http://gist.github.com/258950

1. Dump your contacts from Highrise
2. Run the script on that file
$ ./merge_highrise_contacts.rb contacts.csv > mail_merge.csv
3. Load the output file into Excel or Numbers (09)
4. Save as a Worksheet
5. Follow the regular Mail Merge instructions for MS Word/Excel or Apple Pages/Numbers

You need the '09 version of Pages/Numbers to do a proper Mail Merge. I prefer Numbers to Excel for this as it handles international characters properly, which I need.

In Numbers you need to designate the Header line as such by selecting 'Convert to Header Row' from the pull down menu on the row 1 label.

I'm all for the 37 Signals 'Keep things simple' approach but there are features that are just too widely used to ignore. Not being able to build a mailing list is one. Not having Prefix and Suffix for Person records is another.

Many of my contacts have doctorates and I want to record the prefix 'Dr.' for those that do. The only way I can do that in Highrise is to include it in the First Name. This is a pain.

Screencasting on Mac OS X 10.6 Snow Leopard

I've been making several screencasts as tutorials for our SQIP Patent Sequence Database service.

If, like me, you're not that familiar with video technology and terminology, the process can be confusing - which compression settings? how many frames per second? etc.

With Mac OS X 10.5 (Leopard) you could do simple video editing in Quicktime Player if you upgraded to the Pro version. Ironically in Snow Leopard you can trim video clips in regular Quicktime Player, but without the flexibility and controls of the previous version - and there is no Pro version... so Snow Leopard is a step backwards. The version of iMovie in iLife 09 is great when it comes to editing clips, but is confusing in terms of exporting the finished movie.

Here are the steps that I use to get from my screen to a finished screencast on a hosting service that I can embed in a web page. Its not perfect, but it works for me...

Here are the main steps:
1. Set browser to 1024 x
2. Record screen activity and audio in iShowU
3. Load the movie into iMovie
4. Cut and splice together clips in iMovie
5. Export as MPEG-4 movie (.mp4)
6. Load into QuickTime Player and re-export as a streaming MPEG-4 file (.m4v)
7. Upload to screencast.com

I'm not going into all the details but here are the settings that work for me in each of these components:

1. Set my browser to a defined size (1024 x 768)

Create a bookmarklet with this string: 'javascript:window.resizeTo(1024,768)'

2. Record screen activity and audio in iShowU

Here are my settings - 'Apple Animation' for compression, capture size and frame rate are the most important.


3. Import in to iMovie09

Go to File -> Import... -> Movies... -> Optimize video: Full - Original Size

Not sure if you need to optimize video - not tried it unchecked. Importing takes a while, probably due to whatever 'optimize' involves.

4. Cut clips from your raw video and add to the new movie.

I'm not going into detail with this - look at the iMovie Docs. I find iMovie to be pretty easy to use once you get the hang of it. I would really like to see real time codes for the start and stop of clips and be able to set those directly, but I guess that is what Final Cut Pro is for.

5. Export as MPEG-4 movie (.mp4)

Here is where it can get tricky... pick the wrong settings and the output looks bad, or it takes *hours* to export, or the output file is huge... or ALL of the above!

I don't have this totally figured out but here is what works for me. Note that I'm going from 1024x768 raw footage to 640x480 output.


The important settings are:
- MP4 file format
- H.264 video format
- image size (640 x 480 VGA)
- IMPORTANT - frame rate - Custom - 5 frames per second (this is what you recorded the footage at)
- don't worry about the data rate and don't worry about the Streaming tab
- set the audio to mono

In my hands a 12 minute screencast is exported in a few minutes and yields a file around 25 MB.

6. Load into QuickTime Player and re-export as a streaming MPEG-4 file (.m4v)

Without this step, your users have to wait to download the entire file before they can start viewing. You must be able to do this within iMovie but I haven't figured it out yet.

First of all, view your .mp4 movie in Quicktime Player to make sure it works the way you want.

Then go File -> Save for Web... and Export versions for Computer (and iPhone if you want) - then 'Save'.

This will create a folder with several files - all I care about is the .m4v, but view the .html file for links and more info. Open the .m4v file in Quicktime Player to check that it is the same as the .mp4. Compare the files sizes - the .m4v may actually be larger than the .mp4.

7. Upload to screencast.com

I use screencast.com to host my screencasts. You can get a free account, which has been fine for my needs or pay a modest amount and get a much higher bandwidth limit.

It works out pretty well for me, but I do run into odd issues from Firefox on the Mac - things freezing in the admin interface - seems fine from Safari.

Once uploaded to screencast, look under the 'share' icon to get the code needed to embed a flash player in your own web page and try viewing the video. Having converted it to 'streaming' in the previous step you should see the video start pretty much right away while the rest of the data downloads.

If you skip this step then you will be stuck with a black screen for as long as it takes to download - no progress indicator! Not sure if this is a Flash problem or a Screencast one, either way it is not good.

BONUS - Title slides in iMovie

I make title slides in OmniGraffle that are the size of a frame (1024 x 768) and save these as PNG files. You can add these to you iMovie project by dragging and dropping and then changing the length of time they should be visible for.

But a big Gotcha for me, is that by default iMovie will try and apply a silly 'Ken Burns' transition to the slide making it grow (or shrink) slightly. This makes the text look rubbish in my examples.

Click on that 'clip' and use the small 'cog wheel' pull down menu to go to 'Cropping, Ken Burns and Rotation'. Now in the video preview panel (top right), click on the 'Fit' button to make the title slide fit the frame exactly - then click 'Done'

Here is an example of my screencasts embedded in a web page: Creating a New Account in SQIP

Good Luck

Announcing the Launch of the SQIP Patent Sequence Database

Craic is pleased to announce the launch of a web service that we've been working on for the past couple of years.

SQIP (pronounced 'skip') is a database of DNA, RNA and Protein sequences derived from issued patents and published patent applications, along with a sophisticated interface that allows users to load their own sequences, run searches against the database, evaluate matches and then view the patents associated with those matches.

It is targeted not only at biotech patent agents and attorneys, but also at business development and research staff in biotech companies.

SQIP has been built on many of the technologies that this blog has touched on. It has been a lot of work but by leveraging best-in-class software we've been able to build something that we think will change patent sequence searching.

You can sign up for a free account at http://sqipdb.com. You can explore all the features of SQIP, including searches with pre-loaded sequences, at no charge. You simply pay for searches with your own searches - no annual commitment, no per-user charges, no surprises.

Take a look at SQIP.

Ruby and Rails issues on Snow Leopard Upgrade

I had been holding off upgrading my MacBook to Snow Leopard as I figured there would be issues with the Ruby installation and some of the gems. I decided to go for it yesterday... yup, there were issues...

Snow Leopard comes with Ruby 1.8.7 already installed so it is tempting to use that but I follow Dan Benjamin's advice and install my own versions of MySQL, Ruby and Ruby gems in /usr/local. Dan has produced a series of excellent guides on installing Rails, etc on the Mac. HERE and HERE for example. However, his instructions do not always work for me. Here are the steps I needed to do get my setup running on my upgraded laptop.

The main issue is that you want Ruby and MySQL compiled as 64 bit applications. If you have Ruby as 32 bit and MySQL as 64 then things will break with cryptic messages.

I don't like MacPorts and, although I like installing binary packages, I prefer to compile Ruby etc. That way I retain full control on installing new versions.

MySQL:
1. Follow Dan Benjamin's advice on XCode, paths, etc
2. Dump your existing MySQL contents.
3. Download and compile MySQL as per Dan's instructions.
4. Load your data and check that MySQL is working as expected.

Ruby:
1. Don't compile the code as root/sudo - use sudo to run make install and gem install, but nothing else.
2. Make a list of your currently installed gems (gem list --local > gem.list)
3. Download and compile readline-6.0 (http://ftp.gnu.org/gnu/readline/). Supposedly you no longer need to do this, but I found it necessary.
4. Download Ruby into a user writeable directory in /usr/local/src or ~/src
5. Move the existing /usr/local/bin/ruby and /usr/local/lib/ruby (if you have them) - you shouldn't need to but it will make it easier to validate that the new version has been installed
6. Run ./configure with these options
./configure --enable-shared --enable-pthread CFLAGS=-D_XOPEN_SOURCE=1 --with-readline-dir=/usr/local
7. Follow with make, sudo make install
8. Check that you now have /usr/local/bin/ruby and check that it is compiled as as 64 bit by running 'file /usr/local/bin/ruby'. You should see '/usr/local/bin/ruby: Mach-O 64-bit executable x86_64'.
9. Now download and install rubygems as per Dan Benjamin.
10. Any gems that have C extensions need to be recompiled, but to be safe you should install all of them (You could copy them from your old installation but don't). I have a script for doing this which I should post.
11. The MySQL gem is different - you want to pass the path of the mysql dir to the gem install, like this:
sudo gem install mysql -- --with-mysql-dir=/usr/local/mysql
Note that you should not need to specify ARCH_FLAGS or anything else, which is recommended elsewhere - not needed.
12. Install Phusion Passenger if you are using that.
13. Check out your Rails apps. Use 'script/console' and try some simple finds on your models.

If you see this: NameError (uninitialized constant MysqlCompat::MysqlRes) it is most likely that your Ruby and/or gems are not compiled as 64 bit. You will see all sorts of posts where people have seen that error. I think most of them are the result of this mismatch.

To check any executable/library just do 'file ' and see if it says 32-bit or 64-bit.