Chatr.cc and the start of new experiments in higher ed

Today I close out a chapter of my life at Northeastern University.  Tomorrow I start a new chapter (and a new blog) at Granite State College.  The unifying factor?  Driving continuous improvement through the thoughtful application of technology.  And higher education.  And Salesforce CRM.

Speaking of Salesforce...  I hope that as companies begin adopting tools such as Chatter (and accessories like Chatr.cc), internal transparency translates into more engaged employees, customers and the public at large.  I'm thankful for all that I've learned at NU, professionally and academically, and I believe in the power of a social enterprise in both the Salesforce and the charitable senses of the term.

FileMaker Pro Equivalent to SQL Query or View

Have you ever wondered how to create queries or views in FileMaker Pro on Mac?  I have, and it's one of those odd learning curves that come with being used to straight-forward but fairly technical SQL statements that are customary in Oracle, SQL Server, MySQL and even Access.  Searching for the word "join" returns no relevant results in the FileMaker Pro 9 Help files.

Well, for other SQL developers out there, I hope this tutorial will shed some light on how to join two tables and then create a query or a view: FileMaker Pro: Creating a "Query" or "View"

In summary, here are the key takeaways from the tutorial:

• A SQL join is analogous to a layout in FileMaker Pro with relationships between tables.
• A SQL query or view is analogous to using Find Mode in FileMaker Pro with a layout.
• Simple filters can be applied in Find Mode using Symbols and the Omit checkbox.

Let me know what you think of the tutorial!  Personally, I was super excited to figure out how to import two lists from two different databases into FileMaker Pro in order to identify discrepancies between the two lists, like I would've done using Excel or Access.  Why would I use FileMaker Pro instead of Excel on Mac to do this?  Because Excel is unfortunately case-insensitive.

Auto-filling City and State with Postcode Anywhere and Marketo

First, the "product": an HTML script block that can be added to Marketo as a snippet for looking up the City and State/Province based on a given Country and Zip/Postal Code! All one has to do to use it should be to edit and change the Postcode Anywhere license key.

The background for this is that our Marketing department went live with Marketo last week, and that was an awesome accomplishment which involved replacing all of our previous Web-to-Lead forms with Marketo landing pages. With that done, we set our eyes on a next step: making our forms more approachable by automatically filling in a lead's city and state/province if they give us the country and zip/postal code.

For the lookup service, we chose Postcode Anywhere originally because they were listed on the Salesforce AppExchange. But then we realized that the app was for internal use after a lead already went into Salesforce, and what we actually want is for the info to be filled in before a form is even submitted. Plus, we want our leads to go into Marketo first, not Salesforce.

Knowing that our marketers should not have to know JavaScript to use this functionality, it was obvious that any real solution would have to be able to be dragged and dropped on to a Marketo landing page with minimal configuration, if any at all.

Thankfully, it appears that there are standard address fields in Marketo with standard and consistent name and id attributes. Once I discovered this common characteristic across all forms that we had created, it was simple to customize the JavaScript code template that Postcode Anywhere publishes to work with Marketo.

This is a great example of two companies, Marketo and Postcode Anywhere, making user-friendly integration a viable and attractive option.

SObject Utility Class Template

Have you ever had to get the ID of a record type for a specific object? Or have you needed to hard-code a particular picklist value into a Visualforce controller/extension or other Apex class? I've had to do both on a fairly regular basis, and it soon became apparent that stress-related health problems may arise if I ever have to refactor my code, or if a user requests a change in picklist values.

To address this issue, it seems that setting up a global constant accessible to all Apex classes would be hugely valuable in making sure that picklist values and record types (and other items) are consistently referenced in Apex.

For example, if I'm trying to set the Status of a Contract to "Activated", I could write the code as follows:

myContract.Status = 'Activated';

This is great if it's the only place I ever deal with the Contract Status field. But what're the chances of that? Instead, how about writing the code as follows?

myContract.Status = ContractUtil.ACTIVATED_STATUS;

Now I don't need to worry about all the myriad places where I've hardcoded the status value. If I ever need to change the status, I can update the constant in ContractUtil or look for all references to ContractUtil.ACTIVATED_STATUS.

Here's the sample code for a generic SObject utility class:

/**
 * Utility class with supporting methods for
 * a Salesforce SObject.
 *
 * Examples of supporting methods include getting
 * a Record Type ID, getting an expected picklist
 * value for a particular field, and conversion
 * to/from other objects.
 */
public class GenericSObjectUtil {

    /**
     * The String value that
     * represents an activated status, which goes
     * into the Status field.
     */
    public static final String ACTIVATED_STATUS =
            'Activated';

    /**
     * The expected default Record Type Name
     * for all users.
     */
    public static final String DEFAULT_RECORD_TYPE_NAME =
            'This SObject';

    /**
     * The String value that
     * represents a draft status, which goes
     * into the Status field.
     */
    public static final String DRAFT_STATUS =
            'Draft';

    /**
     * The map of RecordTypeInfo
     * objects retrieved by describing the
     * SObject, keyed by the Record Type Name.
     * 
     * This is stored to make
     * getRecordTypeId method calls
     * more efficient.
     */
    private static final Map recordTypeInfosByName =
            Schema.SObjectType.Contract.getRecordTypeInfosByName();

    /**
     * Retrieve the Record Type ID based on a
     * given Record Type Name.  If no match is
     * found, then return null.
     *
     * @param  name The name of the Record Type
     * @return      The ID of the Record Type,
     *              if found; null otherwise.
     */
    public static Id getRecordTypeId(String name) {
        return recordTypeInfosByName.get(name).getRecordTypeId();
    }   // public static Id getRecordTypeId(String)
}   // public class GenericSObjectUtil

Note: I know the above code may be cut off a bit due to width limits in Blogger, but you should be able to copy and paste the code into a text editor to see the full structure if necessary.

What do you think? Feedback on this implementation or other solutions will be appreciated!

Installing PostgreSQL 9.1.3 on Mac OS X Lion

Unsatisfied and undaunted by the foreboding discussion on installation troubles ("PostgreSQL 9.1 Installer Fails on OS X Lion"), I decided to follow the official PostgreSQL instructions to install the software from source. All so that I could build Ruby on Rails apps to be deployed to Heroku.

Note: PostgreSQL 9.0.5 appeared to have been bundled with my Lion installation, as seen with pg_config before installing 9.1.3. But I wasn't sure how well it worked since Apple provides zero documentation on this bundled installation, and initdb was not located in a known path.

So, in short, here are the steps I followed to install PostgreSQL 9.1.3 on Mac OS X Lion 10.7.3 from the source code.

# Make sure you have the latest version of
# GNU Make for Mac OS X. This can be downloaded
# through Xcode 4.3 by installing the Command
# Line Tools.

# Download the source code from the PostgreSQL
# website, and start this procedure in the
# expanded directory containing the source files.

./configure
make
sudo make install

# At this point, assuming installation was
# successful, create a new user to serve as the
# unprivileged user that will own the server
# process.

# Open System Preferences to create a new user.
# New Account:  Standard
# Full Name:    PostgreSQL Agent
# Account name: postgres

cd /usr/local/pgsql/
sudo mkdir data
sudo chown postgres data
sudo mkdir log
sudo chown postgres log

# At this point, we're done with configuration
# and ready to start the server process.

sudo su - postgres

# The following commands will be run as the
# PostgreSQL Agent user.

cd /usr/local/pgsql/
bin/initdb -D data/
bin/postgres -D data/ >log/logfile 2>&1 &

# To verify that the server is working properly,
# let's create a test database and see whether
# we can connect using the interactive terminal.

bin/createdb test
bin/psql test

If all went well, you should see something like the screenshot below.

Finally, we can move on to the fun stuff!

AppleScript to Copy Message to Clipboard in Outlook 2011

I've created an AppleScript for Outlook 2011 that will copy some key information as plain text and throw it on the clipboard! I expect this to save me a good deal of time in the months to come.

Now that we're using ServiceNow to record our interactions, I'm finding myself having to copy and paste lots of info from emails into plain text fields on an incident or task in ServiceNow.

Specifically, for diligent tracking of communications, I've been recording the following info:

• Subject
• From
• Sent
• To
• Cc
• Body

The problem is that copying and pasting all that information into a template that I have to retype every time are all very time-consuming and mundane operations. This script allows me to get a well-formatted snip of the key information with a few simple clicks. Whew!

EDIT: March 22, 2012

Okay, I've discovered a simpler alternative: Reply to the email you want to copy and then copy what you want. The automatically composed reply will contain the same header information that this script copies.

Oh, well, I guess I'll just take this as a fun exercise in learning AppleScript.

Test Class Template for Apex Triggers

Having written a lot of test methods to validate trigger functionality, I've developed a general template for writing trigger-related test methods that hopefully encompasses all of the steps needed. I believe that there must be a generic template that can be applied to 99% of the trigger test cases out there, and I hope this is a good start in that direction.

@isTest
private class GenericTriggerTest {

    /**
     * Plain English description of what is being
     * tested and why.
     */
    public static testMethod insertSObjects() {

        // Stage the test data.

        // Set parameters and expectations.
        // Which records am I using?
        // What do I expect to change or see
        // at the end of the test?

        // Validate pre-conditions.
        // Impersonate another user
        // if appropriate.

        //System.runAs(null);
        System.assert(false, 'TODO');

        // Start the test.

        Test.startTest();

        // Execute test operations.

        // Stop the test.

        Test.stopTest();

        // Validate the results.

        System.assert(false, 'TODO');
    }   // static insertSObjects()

    /**
     * Plain English description of what is being
     * tested and why.
     */
    public static testMethod updateSObjects() {
        System.assert(false, 'TODO');
    }   // static updateSObjects()

    /**
     * Plain English description of what is being
     * tested and why.
     */
    public static testMethod deleteSObjects() {
        System.assert(false, 'TODO');
    }   // static deleteSObjects()
}   // private class GenericTriggerTest

Feedback on the template will be much appreciated.