API Fun with Quickbase and Mailchimp

Things have certainly changed in the past 2-3 years as business process automation technologies have evolved from primarily being on premise technology stacks by companies like IBM, Microsoft, and Oracle requiring on site system administrators to keep them running…to a new public cloud technology model from companies like Salesforce, Intuit, Google, and Amazon. These new public clouds are focused more on providing solutions rather than infrastructure. More and more often nowadays the job of creating business process automation solutions entails piecing together a smorgasbord of technologies from a variety of vendors most of whom are in the cloud.  Whereas in the past developing these solutions required developers to know a great deal about the underlying operating systems…today the process is more about creating solutions without having to know anything about the underlying platform running the solution. Automating systems is now often more about writing small amounts of code to connect different cloud platforms thus creating unique and innovative solutions to business problems.

In this vein I recently had the opportunity to develop an interface between Quickbase and Mailchimp. Quickbase is a cloud platform from Intuit for building custom database applications and Mailchimp is a cloud based email marketing service.

In this business scenario Quickbase is a customer relationship management (CRM) system where new leads are collected into a Leads table via direct input, mass import, and web forms from company websites. Mailchimp is used for email marketing campaigns and periodic newsletters.

The problem was to create an interface between Quickbase and Mailchimp so new leads collected in Quickbase could be automatically synced with mailing lists in Mailchimp.

I chose to create a PHP script with the Quickbase API to query a Leads table in Quickbase and sync the result set with a  subscription list in Mailchimp. On the Quickbase side I took advantage of a PHP wrapper developed by Joshua McGinnis, a Software Engineer  at Intuit, Inc.  based out of  Waltham, Massachusetts. On the Mailchimp side I was able to take advantage of  the Galahad MailChimp Synchronizer, a PHP wrapper provided by Chris Morrell.

My PHP script simply had to read the result query array from Quickbase and write the data elements into a new array in the format required by the Galahad Synchronizer.

Once completed I ftp’ed the script onto a LAMP hosting account, setup a Cron job to execute it hourly, and tested by adding new Lead records into the Quickbase table.  Then an hour later confirming the new Leads are added to the Mailchimp list as expected.

This system assumes the list in Quickbase is the master so once a lead is removed from Quickbase it is also removed from Mailchimp.

Here’s the code if you are interested:

<?php
require_once 'Galahad/MailChimp/Synchronizer/Array.php';
require_once 'MailChimp/MCAPI.class.php';
require_once 'quickbase/quickbase.php';
// =============================================================================
//  SET THESE
// =============================================================================
$apiKey = '9072e9b2028444af-us1'; // mailchimp api key
$list = 'c150f6eba1'; // mailchimp list ID
$quickbase = new QuickBase('userid', 'password',true, 'bgmk5dsfc');
$res=array();
// setup the query to quickbase > field id 112 equals yes
$queries = array(
array(
'fid' => '112',
'ev' => 'ex',
'cri' =>'yes')
);
// quickbase api query with field id list
$res = $quickbase->do_query($queries,'','','10.6.7.3.110.111.24.23','');
$email_results=array();
// write array elements from $res into a new array $users in the format required  by Galad Synchronizer
 $j=0;
 foreach($res->table->records->record as $record)
        {
            $i=0;
            foreach($record->f as $value)
            {
                $field_name[$i]=$value;
                $i=$i+1;
            }
$users[$j] =
array('EMAIL' => strval($field_name[0]), 'FNAME' => strval($field_name[1]), 'LNAME' =>
strval($field_name[2]), 'MERGE3' => strval($field_name[3]),
'MERGE4' =>  'bgmk5dsfc', 'MERGE5' => strval($field_name[4]), 'MERGE6' => strval($field_name[5]),
'MERGE7' => strval($field_name[6]),
'MERGE9' => strval($field_name[7]),
);
$j=$j+1;
         }
// =============================================================================
//  THAT'S EVERYTHING
// =============================================================================
// call the synchronizer
$Synchronizer = new Galahad_MailChimp_Synchronizer_Array($apiKey, $users);
$Synchronizer->sync($list);

Force.com Dev 501 Class

The week of October 10th I was fortunate to have the opportunity to attend Force.com’s Dev 501 class in Atlanta. This class went beyond the declarative capabilities of Force.com covered in Dev 401 and expanded into APEX and Visualforce controllers.

Apex is an object oriented  programming language,  similar to Java and C#,  for building software as a service (SaaS) applications on top of Salesforce.com’s customer relationship management (CRM) functionality.  Apex gives developers access to Salesforce.com’s back-end database and metadata objects to create third-party SaaS applications. These 3rd party applications are offered for sale or free of charge in the Salesforce AppExchange marketplace, similar to the Apple and Android app stores.

Visualforce is a framework that includes a tag-based markup language,  similar to HTML or ASP.Net.

In the Visualforce markup language, each Visualforce tag corresponds to a user interface component, such as a section of a page or a field. The behavior of Visualforce components can be controlled by the same logic used in standard Salesforce pages, or developers can create  their own logic with controller classes written in Apex.

The Dev 501 class covers an amazing array of topics…just a few of which are APEX triggers, Force.com IDE Eclipse plugin, development sandboxes, SOQL queries, DML (data modeling language), deployment from sandbox to production organizations, consuming web services, and unit testing Apex classes.

Format of the class was lecture mixed with hands on exercises reinforced by formal questions answered out loud by attendees at the end of every lecture and exercise.  This was actually the most challenging formal training class I have attended since college…and also one of the best.

 

 

August Salesforce Meetup was the best one yet!

I’ve attended a few Salesforce User Group meetups in Atlanta over the past couple of years and being a big fan of Salesforce enjoyed each one. First unlike most other local technology meetup groups they are held in the morning not the evening. Second meetings are held at different venues around Atlanta…places like Home Depot, St Joseph Hospital, and Manhattan Associates. It’s interesting to get a glimpse inside all these different organizations.  Third they normally have a sponsor who provides breakfast and most important…coffee! Last but not least and the main reason I go to these meetings is the learning experience from 2-3 speakers on a wide variety of Salesforce and Force.com subjects. Talks are typically short 15-30 minutes each with demonstrations and time for questions. I always learn something new at each meetup but this August meeting was the best so far. The very first talk was by a pre-sales consultant (sorry can’t remember his name) from Informatica who walked us through a demonstration integrating two end points…Salesforce and an on-premise Oracle database. Coincidentally I’m starting a new project where I need to connect Salesforce to an on-premise Microsoft SQL Server and so needless to say I was transfixed by what I was seeing…the perfect solution to my current biggest problem! After the meeting upon returning to my office at Georgia Tech I immediately signed up for an account at Informatica, downloaded and installed the free lite version of their small footprint “agent”  onto my local Windows 2003 server where my SQL Server database is running. And by the end of that day I was syncing my Salesforce org with my local SQL Server…whoohoooo! Now that’s what I call getting value from a user group meetup!

Two 0ther presentations at the meetup covered exception reporting by James Martucci an instructor at Salesforce, and the Salesforce partner portal  by Venkat Polisetti, Senior Applications Developer at Fiserv Bank Intelligence Solutions. Both presentations contained useful information that I should be able to apply at work over the next few weeks.

Props to Venkat Polisetti who runs these meetings and always does a great job lining up speakers, coordinating with event sponsors,  and communicating the specifics out to group members.

 

 

 

Learning to build apps in the Cloud with Force.com

Just got home after a great week attending a Salesforce class, Building Applications Using Force.com and Visualforce. The class was held at MicroTek training center located at 230 Monroe street downtown Chicago. This is first of two main dev classes for Force.com covering mostly the declarative aspects of Force as well as Visualforce pages. By declarative I mean “point and click.” While Force.com contains a full blown object oriented programming language, APEX, and follows the MVC (model, view, controller) software architecture…this class didn’t cover programming…although we did get a good introduction to Visualforce pages.  Visualforce pages are a combination of html, javascript, and APEX tags that enable  the creation of custom pages (views) that utilize standard Force.com controllers. What we didn’t cover in this class was creating custom controllers with the APEX language.

For learning to develop custom controllers using APEX Salesforce offers another 5 day class,  Dev-501 – Apex and Visualforce Controllers…which hopefully I’ll get to experience sooner rather than later.

So now I’ve got a week of in depth training…a Salesforce account with 10 seats…a couple of big projects on the horizon at work where I can apply my newly learned Force.com skills…which makes me all set for an interesting Fall I think.

I stayed at the Hampton Magestic 22 West Monroe…a short walk from where my class was held at Microtek. (see photos below).  This was an outstanding place to stay…conveniently located in the middle of the Theater and Financial districts and only a couple of blocks away from Grant Park and the lake. Clean, reasonably priced…great breakfasts every morning too! I’m staying here every time I’m in Chicago from now on!

Every day after class I went for a run along the shore of Lake Michigan and the Chicago River (see pics below). Even though it’s August the temp each day was pleasant with a cool breeze blowing in from the lake…pure heaven coming from Hotlanta…especially this Summer one of the hottest on record.  And I wasn’t alone there were hundreds of other runners, walkers, and bikers on the same paths with me…very cool!

I tried to sample some traditional Chicago food…a slice of pizza and a hot dog…other than these two “samples” I went with fish and chips every evening. It wasn’t the healthiest week of my diet this year that’s for sure but I survived!

One observation about Chicago I have to mention…NO SMOKING inside any building…not a bar or pub or anywhere! And no smoking within 15 feet of any entrance either so you weren’t forced to hold your breath as you entered buildings. What a great idea! Who knows maybe Atlanta will get up to speed on eliminating this health risk one of these days too! But I doubt it.

Last but not least (well maybe so 🙂 I gained two Foursquare mayor ships in Chicago…Microtek and a small French restaurant along the Chicago river running path. It was a good week!

 

The "Bean" in Millenium Park was very cool!
The CTA blue line took me from O'Hare almost to my hotel door for only $2.25!!!

The Hampton Magestic was a great place to stay...highly recommend!

Street scene from the Microtek building at 230 Monroe Street
Fish and chips from Millers pub was absolutely yummy
The water fountains at millenium park were something different for sure!
Running path along the Chicago River
Cyrano's on the river along the running trail...I became mayor of this place! 🙂
Scene along the Chicago river running trail
Running path along Lake Michigan...cool breeze even in August.
Another scene along the Chicago River running path
Fantastic hot dog (Chicago Red Hot) at Halsted Street Deli
The "Bean" at millenium park
Everyone takes their pic in front of the Bean!

 

 

Is Social Media The New Web?

Walking into Whole Foods on Ponce de Leon the other day and saw this sign at the entrance. It reminded me how most of the 2011 Superbowl commercials listed only their Facebook page at the end of the commercial. Makes me wonder if social media sites like Facebook and Twitter are becoming the new web? An added point…while driving past Lenox Square Mall every day to work I notice the main sign out front lists their Facebook page and Twitter handle…not a website URL anywhere to be seen.

 

Google Southeast User Group Meeting and Summit July 25-26

Ken Ingle, President of the Google Apps Southeast User Group, kicks off the meeting

Enjoyed this great one and one half day event at the Google office in Atlanta. Who knew the Google offices in Atlanta are located as easy walk right down the street from my office at Tech Square and just across the street from the 10th street Marta station? The event was a combined Google Summit for non Google Apps customers and a Southeast User Group meetup for current App customers. Attendees were there from all over the southeast.

Day 1: The first afternoon covered Google’s current upcoming feature release roadmap which I can’t revile due to an NDA. However I can say the remainder of 2011 is going to see some exciting new features in apps.

The end of this first day Ken announced that event sponsor, Cloud Sherpas, had donated three kegs of beer…and Google provided hot wings, quesedas, a variety of cheese, summer sausage, salami, grilled veggies, and more that I can’t remember. It was awesome to say the least!

Day 2: After an informative keynote panel in the morning of the second day the summit attendees split off from the user group to get a deep dive on Google App features while the user group members broke into groups for discussion of specific topics of interest.

 

Here are a few photos I took of the event.

 

From the back of the room showing ping pong, pool, and foos ball tables. Glimpse into the West Coast culture?

 

Networking event started around 4:30 with lots of food and beer. Shown here are the hot wings, quesadas, and beer on tap.
Back of the room

 

Well equipped guitar hero area

 

 

 

 

 

 

Building Facebook Apps at the Atlanta PHP User Group July Meetup

Jason Bouffard, Lead Facebook and PHP developer at Definition6, was the presenter at July’s PHP User Group meetup held at Strongbox West.  This meetup was completely full with over 50 php developers. Free pizza and soft drinks were provided by event sponsor Definition6. Jason’s talk covered his experience developing and launching a Facebook app, True Blood Immortalize. His very creative presentation is located online here.

 

July PHP Meetup at Strongbox West
Jason Bouffard, Lead Facebook and PHP developer at Definition6, presenting at July PHP meetup

Atlanta CloudForce 2011

On May 19th CloudForce 2011 Atlanta was held at the downtown Hyatt. As always this event was outstanding with an informative keynote and interesting breakout sessions throughout the afternoon lasting until 5:30.  Registration began at noon and a nice lunch was provided in the exhibitor area up until 1pm when the keynote began. This year I had to come and go as I had meetings back at Georgia Tech I couldn’t miss. Good exercise as I walked the 1.5 miles back and forth to Tech Square several times this day. As you see in the photos I made sure I was there in the afternoon in time for the evening reception!

 

Keynote at Cloudforce Atlanta 2011
Registration area at the Atlanta downtown Hyatt
Salesforce Cloud projected onto the walls
Free beer and wine!
There were bars setup out doors too!
Last shot of the reception on my way out the door.
Out door patio at the Hyatt

 

Web Services Magic at the November PHP Users Group Meetup

The November 2010 Atlanta PHP Users Group Meetup held on November 3rd covered web service APIs for Twitter, Facebook, and Amazon.

Moses Ngone gave the fist presentation, an introduction to web services. Moses did a good job explaining the main components  of web services…access, request, and handle response. He then drilled down into more detail on  each component. First on access methods…none required, basic authentication, and OAUTH which is quickly becoming the standard. PHP requests consisting of curl or file_get_contents. And finally response takes the form of  xml, json(p), or serialized.

Next up Joey Rivera presented on the specifics on using web services of Twitter, Facebook, and Amazon. Joey’s presentation included lots of good code examples and useful tips like using the ZEND framework to handle all of the oauth magic for you. All of Joey’s code examples and presentation can be found here on his website.

The meetup was packed with only a handful of empty seats. I counted 32 attendees.

I always enjoy these technology meetups and seeing first hand the enthusiasm and passion of Atlanta’s technology community.

Funny thing after the meeting (which was held in room 577 at Colony Square) I stepped onto the Arts station Marta platform and to my surprise there stood my next door neighbors waiting for the same train. For anyone not living in Atlanta…this is a once in a lifetime coincidence I can assure you. So I even got a welcome ride home from the Marta station…a nice ending to a nice evening.

Practice Fusion’s Cloud Based EHR Solution Obtains ONC-ATCB Certification

Practice Fusion, a free Software as a Service EHR solution, recently announced obtaining ONC-ATCB certification in accordance with the applicable certification criteria adopted by the Secretary of Health and Human Services (HHS).

I have already discussed my interest in Practice Fusion in an earlier post. This cloud based system provides a free service to healthcare providers who are adopting Electronic Health Records in response to the Health Information Technology for Economic and Clinical Health (HITECH) Act.

Other interesting developments for the Practice Fusion team is partnership with EMF Financial Services, a health IT consulting company in Michigan, and a partnership will Dell to provide a bundled hardware/software EHR solution complete with three months of free financing. And on No 5th they will be holding the  first Practice Fusion User’s conference in San Francisco. And “surprise” the conference is completely free to attend.

No I don’t have any affiliation with Practice Fusion…but I do know a disruptive technology innovation when I see one. Health IT is an area where I have personal interest, hope, and concern about the ability of small providers to handle the cost, installation, and ongoing maintenance of an on premise solution. So when I first read months ago about Practice Fusion and their freemium pricing model…and being a huge fan/proponent of Google (the classic freemium pricing model)…I was immediately stuck by the idea that Practice Fusion may just be the silver bullet necessary for successful wide spread adoption of electronic health records by thousands of small physician practices throughout the USA.