July 10, 2010 
No FileMaker plug-ins are required with technique. A Web Viewer and a Custom Function do the heavy lifting to pass info back and forth between FileMaker and MailChimp.
Part 1 of 3 Parts — (See also Part 2 and Part 3 ) FileMaker Pro is a great place to store information about customers and clients. It also has some very useful tools for sending out emails to small groups of people. I have automated my own client billing system using FileMaker. I can generate a PDF of an invoice and attach it to an email addressed to the billing contact at my customers place of business with a bit of straightforward scripting and then the click of a button.
FileMaker recently added the ability to send mail directly via SMTP, rather than piggy-backing on your computer’s email client for sending. This is a great addition, but it is still lacking in some areas. There is no HTML mail capability and it is a send-only solution, with no ability to receive emails back into the system.
The lack of HTML mail capability for email marketing is a big deal. HTML mail is more compelling than plain text, with the ability to employ fonts, colors and images in your message. Basically, anything you could do on a web page about ten years ago, you can pull off in an HTML email. I am not sure about the <blink> tag…
There are some useful plug-ins for FileMaker that bridge this gap, (360Works Email SMTPit Pro Mail.it ) offering HMTL Mail and the ability to receive mail into FileMaker. There is a downside to using these plug-ins, in that you still don’t get WYSIWYG editing of the HTML Mail during the creation stage. The user is left back in the hand-coded days of yore. It is also risky to just use a straight ahead web page editor like Dreamweaver, iWeb or RapidWeaver to generate the code, because as mentioned previously, HTML email is stuck back in the 90’s. Different email clients will render HTML mail in different ways. Much of what you can create in those amazing CSS style sheets won’t work. Then there is the issue of what to do with images, do you encode them and include them in the message or store them on a remote server and then link to them? Plugins add complexity in maintaining your solution, you have to understand them, code them, keep them up to date and ensure that users have them installed for your solution to work. So there are problems.
Another issue is spam catchers. Most ISPs worth their salt will be monitoring your connection to see if your email sending is exceeding some limit. If you go over 500 or 1,000 emails in a short time, they assume that your computer has been taken over by hostile spam senders and they shut you down, blocking your ability to send more mail.
For all these reasons, the lack of HTML mail, the inability to receive return mail and complexity of plugins, the lack of WYSIWYG editing and spam filters, FileMaker Pro is not the best solution for mass mail-outs.
Fortunately, in the last few years there has emerged a new category of web service designed to help you with your email marketing campaigns. There are a number of alternatives for large mass email sendings, including MailChimp, Constant Contact and Campaign Monitor.
These web services usually offer some variation of the following capabilities:
* upload lists of email from spreadsheets, tab delineated text files or csv
* dynamic creation of web forms to facilitate users sign ups online
* list management, subdivide email addresses into various sub-groups for smaller more focused mailings.
* validity testing of emails
* WYSIWYG email campaign creation tools
* Mass email capabilities
* Campaign analysis tools
It sounds like a perfect marriage, use FileMaker for in-house tracking and MailChimp for email marketing. And yet, as soon as you move your list into something else, you have created a data island. The data starts to drift. Users get unsubscribed on the list, but not in your FileMaker database. How to deal with keeping things in sync between your FileMaker database and the external email campaign management service. I think I have found a way and there are no plug-ins required. I am working with a Web Viewer and one Custom Function. Read on to find out how it works.
Why MailChimp?
I selected MailChimp because it came well recommended by its users, and I liked their friendly, somewhat cheeky interface. Finally, their API turned out to be well documented, fairly comprehensive and best of all accessible from FileMaker’s Web Viewer. An API is an Application Programming Interface, basically methods with which you can interact programmatically with the website to get and send information back and forth. If you are already tied into another service, I encourage you to read on and see if the techniques I employ might have application in another system.
MailChimp’s WYSIWYG Mail Campaign Editor
MailChimp’s near WYSIWYG editor allows you to employ mail merge fields into your messages so you can personalize the content more.
I am not going to attempt to detail all the things that you can do with MailChimp or how to do it. They have lots of really good documentation on their website, including some nice easy to follow video tutorials. My focus here is to describe how to set up a few focused exchanges between FileMaker and MailChimp.
I will start with the example of writing a script to harness the MailChimp Lists Method. Sending a Lists request returns a list of lists that you have created on the MailChimp server. This technique is fairly basic and but the technique involved can be reused almost endlessly with the MailChimp API to get and set data in MailChimp. We need a few pieces of information to get started:
• A Global API Key — I will show you where to get this in the MailChimp settings
• API keys for any Lists you create — not actually needed in this first example, but it will show up in others
• A URL creation technique — how to string the URL together, concatenating FileMaker fields into the URL to get or set the data required.
• A Method to call up or place some piece of information — you will find a long list of these in the MailChimp API documentation.
• A FileMaker Script to send out the assembled URL — see below
• A named Web Viewer to target with the URL — add a FileMaker Web Viewer to your layout, give it a name.
• A Custom Function to help in parsing the returned result — helps to parse out the text that is returned in the MailChimp response to the URL we send.
• Various fields to place the returned information — some place to store the results as required.
MailChimp’s API Documentation
Full documentation for MailChimp’s API is available on their website at www.mailchimp.com/api
Go to the View 1.2 Docs for full details on all the Methods available.
MailChimp Global API
This is the starting place for all API interaction with MailChimp. It is the equivalent of a user name and password. Because you are sending this within FileMaker it is quite protected. Be careful how you use it on your website. Don’t expose this because it will open up your mailing list to abuse.
Once you are logged into MailChimp, go to the right place in the MailChimp Admin panel and click the button to generate your first Global API Code. If this code becomes compromised, you can expire it and generate a fresh one.
MailChimp List IDs
In the Settings area of your lists, you will find a unique ID for each mailing list.
Lists Method in API Documentation
Let’s have a look at the Lists Method in the API documentation.
JSON URL Technique
From the main Documentation page, we see the way to assemble the URL to get a List of Lists back.
http://api.mailchimp/1.2/?output=json&method=SOME-METHOD&%5Bother parameters]
It seems in their other examples, it is also possible to put the output at the end. I used this approach in creating my calculated URLs.
The Basic Technique in a FileMaker script
At last, here is the magic trick, this script is a simple version of the other Method scripts I created to integrate with MailChimp. The four steps are; assemble a URL with variables from FileMaker; send it to the MailChimp server through a Web Viewer; wait for a response; finally, parse the response into FileMaker fields.
1) Assemble a calculated URL string
2) Send a calculated URL at MailChimp,
3) Wait a while until you get something back,
4) Parse out what it sends back using a Custom Function
Pretty simple stuff really. Using the MailChimp Global API we can recycle this script into many different MailChimp Methods.
Set Web Viewer Options
On my layout, I have a Web Viewer named “MailChimpWebViewer”
Make the Web Viewer as small as you like.
The Web Viewer doesn’t have to be big, or even in the foreground to work. I actually hid it behind the square object on the layout. I turned off all the interaction functions for the Web Viewer to keep display activity to a minimum. The Web Viewer doesn’t need to interact with the user, as we are programmatically grabbing its content.
Set the Web Viewer to the calculated URL
Notice we are using “output=json”. You can also use “output=xml”, I just found the JSON data was simpler to manipulate. The Global field for MailChimp Global API is fed from a FileMaker Global field.
Set a field to the returned data
Here is an example of using the Custom Function ExtractText to pull out the text between the ‘name’ and the ‘comma’ that follows.
ExtractText ( $XMLContent ; “\”name\”:\”” ; “\”,\”” )
It is necessary to escape the quotation marks, because FileMaker also employs these to contain variables.
I am only using one List in my scenario, so I feed the result into two Global fields. I could just as easily be sending them to regular fields and looping through the results to gather a number of list names and Unique List IDs.
The Custom Function to extract the text
This Custom Function came from somewhere, I thought it was on Brian Dunning’s Custom Functions website, but I can’t re-find it. If you don’t want to type it out from the screen, there are other similar ones there, including ParseData which allows you to set the instance of the text you are searching for, useful if you are parsing a number of items out of the returned text.
Of course you can do the same thing in FileMaker, using just the text extracting functions, but these Custom Functions are much cleaner and easier to figure out what is going on. When do you think FileMaker is going to add a built-in function for extracting text, don’t we deserve this yet?
Using the same basic technique develop a number of MailChimp Methods
I have added scripts in FileMaker for a variety of Methods from the MailChimp API to; Add or Delete a Mail List Group; Return the Members of a List; Subscribe or Unsubscribe Members from a Group; Get Member Info; List Interest Groupings and Export a Batch of Emails ready for import into MailChimp, complete with their associated Groups.
There are a whole set of Methods for Campaign management, so conceivably, you could start your email campaigns from within FileMaker. I chose to keep it simple and focus on making it easy to subscribe and unsubscribe people from the list, add Groups to a List and keep track of email status info.
If you look through the Methods on the MailChimp API, you will see how you need to construct the URL that you send to MailChimp. You always start with the Global API key and then the Method name and whatever parameters are required.
For example here is the Subscribe Method in a URL:
“http://api.mailchimp.com/1.2/?
method=listSubscribe&apikey=”&Globals::MailChimp Global API&”
&id=”&Globals::MailChimp Mailing List API&”
&email_address=”&People::People~Email&”
&merge_vars[FNAME]=”&Trim(People::People~First Name)&”
&merge_vars[LNAME]=”&Trim(People::People~Last Name)&
$Groups&
“&double_optin=false
&output=json”
List Unsubscribe simply reverses this by using the listUnsubscribe Method “method=listUnsubscribe”
I would not recommend using the Subscribe/Unsubscribe Method described here to add large numbers of email addresses to your lists. You could conceivably create a Looping script that ran through a long list, but it would run very slowly. The lack of POST function in FileMaker’s web viewer mean that you are limited to the length of the acceptable URL. Instead, create an Export script to send a batch to a text file and upload that directly into FileMaker.
You may have noticed the $Groups variable in this calculation. I have used a loop in my script to gather all assigned Groups from a Group Tag portal. Putting them in comma delimited format allows me to assign and remove emails from different Groups within the greater List.
It is a good idea to spend some time with the MailChimp documentation and think through how you are going to set up your mailing lists. In the scenario I was working on, we had a big email list and chose to add them all to a single list, with multiple Groups. That way we only have to change the email address once, but can assign it to multiple Tagged Groups.
As you can see this one fairly straight forward technique can be employed in a number of ways to make the integration between FileMaker and MailChimp more seamless.
Clean up your list – Custom Function to find Valid Email Addresses
MailChimp is very concerned with not getting a name for spamming people and gives you points for how clean your list is, so it is a good idea clear out duplicate email addresses, get rid of bad addresses and to read up on the double_optin requirements.
MailChimp doesn’t like you sending to role-based email addresses such as ‘info@’ or ‘sales@’. You cannot add these types of addresses in a bulk import, although you can add them one at a time.
There is a good FileMaker Custom Function that is useful in testing the validity of email addresses called IsValidEmail(address)
Email Testing Services
A final note, because of the disconnect between the advances in HTML in web pages and HTML-rendering capabilities within email clients, it is a good idea to test your email before sending it to thousands of people. MailChimp has this testing built-in if you are using their Campaign creator, but if you are doing it some other way, it is a good idea to test before you send.
These websites offer testing services:
www.campaignmonitor.com
www.mailchimp.com
www.litmusapp.com
Stay tuned to this blog for Part 2 of this explanation, as I am working on a demo FileMaker file that illustrates these techniques in more detail.
Update: Part 3 of this topic covers using the Update method from MailChimp’s API to further refine the integration between the two systems.
Related Articles:
MailChimp Integration with FileMaker Pro – Part 2 of 4
FileMaker MailChimp Integration – Part 3 of 4
MailChimp Integration with FileMaker Pro 12 – Part 4 of 4
Related Articles
Digital Fusion: Sending Email from FileMaker Using Mandrill
HomeBase Software 
This is an AWESOME post. Do you plan on putting up a demo file? I’d love to muck around in the script steps and follow the script through the debugger to also follow what happens.
Cheers,
Denis
Hi Denis,
I am in fact working on a demo right now. I hope to have it online in the next couple of days. I built this integration for a customer and it took a little while to generalize it. I have the demo done, and am just building a walk through so it makes more sense. Watch for it to appear shortly.
Doug
Hi Doug
I’m doing the planning for a Customer Management System that needs to integrate with Mailchimp and also with a client facing web front end. It’s very reassuring to know that you’ve already cracked it. I’ve downloaded the demo file, thanks very much for the work it took to document.
Kevin
Hi Kevin,
Glad you are finding it useful. If you come up with any good innovations that you think should be in the demo, please pass them on, and I will try to incorporate them.
Doug
Hi Doug,
I’m also building a Filemaker-based CMS for my company and your demo will be of great use. Thanks very much!!
Justin
In his note of 10/10/2011 he says this does not work on windows because it cannot process the json. I got around this by setting the output to php instead. You have to be careful because the result adds in some additional text that I assume is something to do with data storage. For instance, instead of “name”:”test1″ it returns s:4:”name”;s:5:”test1″. So far this seems consistent. In other words it is always “name”;s:5. That means you only need to tweak the Custom Function inputs to get the results you need. I have only done this with the get lists method but so far so good.
Hi Paul,
thanks for this info! Did you get the whole setup to work for Filemaker on Windows?
And how did you parse the results?
Thanks for help!
I did not pursue this as all my clients are on Macs. The solution suggested by another commenter was to use the .php output instead of the json output. This is documented on the MailChimp API Website.
Great posting, thanks a lot. Just was about to research the possibilties integrating mail chimp into our system. Saved me lot of research, thank you!
Trying to get this working on FM12 on Windows. Can anyone share or show an example how its made with Insert URL?
Hi Jeff,
I just updated the FM-MailChimp Demo file for FM 12 using ‘Insert from URL’. This should work on Windows.
Great post!!! Do you know if this will work with FM Pro 10?
Give it a try.
I didn’t want to go through all the work just to find out that it didn’t.
Anyone else here had success?
Here is what it entails to test it: 1) Download the demo file. 2) Paste in your MailChimp API key 3) go to the sample data and click the subscribe button on one of the bogus data items. If that works, try the other buttons. I think you are talking about half an hour of work. It has been a while since I wrote this, but the basic technique employed, screen scraping using a Web Viewer, has been possible for a while in previous versions.