September 15, 2010 
UPDATE: 2013-06-20 FileMaker 12 version of the demo is available that should work with Windows.UPDATE: 2012-08-07 Windows users with FileMaker 12 have an option to solve the JSON error message issue by swapping out the Web Viewer steps and instead employ the new Insert from URL script step. This will save a few lines of code and since it is not dependant on the Web Viewer framework, which in turn, relies on IE in Windows, the error message popping up should not be an issue. Mighty Data has some good information on Insert from URL is available here.UPDATE: 2011-10-10It has been pointed out that this demo does not work on Windows machines. It seems that Internet Explorer, the application that FileMaker employs in its Web Viewer on Windows machines is not capable of recognizing the JSON files returned from MailChimp. There is currently no workaround, but I will post here if there is any news.UPDATE: 2011-10-15 See the end of this blog entry for more suggestions for Windows users having the problem with JSON files.UPDATE: 2010-10-16 – The demo database updated to work with multiple data centers. It seems I left out a crucial step in making this work with the many MailChimp servers. My demo worked fine because my account happened to be on ‘us1’, but people who were on other servers were not so lucky. Many thanks to Dan Stein of Digital Software Solutions for pointing this out and working through a fix.UPDATE: 2011-11-26 Commentor Paul B has had some success employing MailChimp’s php syntax, instead of using the JSON format. See his comments at the bottom of Part 1.
The Demo database has been revised to include the ability to run updates on individual records. This was the missing piece in the demo that a few people commented on. The problem before was that you could subscribe and unsubscribe people from MailChimp, but if you wanted to keep their associated record history intact (what mailouts did they get etc.) you couldn’t because unsubscribing them was the equivalent of a delete.
See the previous two blog posts on this topic here:
Part 1 – an overview of why you might want to try to integrate FileMaker with MailChimp.
Part 2 – a longer description of how the Demo file works.
An important addition to the demo is the ability to run a batch action to grab the unique ID that MailChimp creates for each record. Once this ID is brought into FileMaker, you have a good mechanism to keep your records in sync.
The chart below details the steps and sequence in keeping the two data sets in sync.
FileMaker MailChimp Integration Process
If you perform these steps in this order, you stand a good chance of keeping things in sync.
Update script
The first step checks to see if the person record has a MailChimp Subscriber ID number assigned to it yet. If not, it will run the script to grab it. There is a potential hole in the system if you haven’t run the batch script to grab all these MailChimp Subscriber IDs yet. So remember to run that script right after uploading a batch of emails to MailChimp. Once they are processeed and accessible in MailChimp, take the time to grab these Subscriber IDs so future updates will make sense.
Main Processing step
This step is pretty similar to the Subscribe and Unsubscribe steps you saw in Part 2 of this blog series, with the exception that we are using the MailChimp Method called “listUpdateMember”.
listUpdateMember Method employed
Note that where we would ordinarily call the email address, we are instead calling the MailChimp Subscriber ID field.
Growl option for notification
I got tired of clicking away dialog boxes when I was testing and so instituted Growl messaging on the Mac platform. It employes a custom function for AppleScript Growl messages. Let’s hope FileMaker implements some sort of fading dialog boxes in the future.
“http://”& Settings::dataCenter &“.api.mailchimp.com/1.2/?method=listSubscribe&apikey=”&Settings::MailChimp Global API&”&id=”& Settings::MailChimp Mailing List API&”&email_address=”&Customers::Email&”&merge_vars[FNAME]=”&Trim(Customers::First Name)&”&merge_vars[LNAME]=”&Trim(Customers::Last Name)&$Groups&“&double_optin=false&output=json”This changes to the following:
“http://”& Settings::dataCenter &“.api.mailchimp.com/1.2/?method=listSubscribe&apikey=”&Settings::MailChimp Global API&”&id=”& Settings::MailChimp Mailing List API&”&email_address=”&Customers::Email&”&merge_vars[FNAME]=”&Trim(Customers::First Name)&”&merge_vars[LNAME]=”&Trim(Customers::Last Name)&$Groups&“&double_optin=false&output=php“
This approach doesn’t solve the IE problem with JSON, but may be another approach. The text returned does require a different approach to parsing the text. Please examine the source file that gets returned and parse accordingly.
Download Demo File
HomeBase Software 
Fantastic stuff here! thanks for all this work! A couple of questions – maybe I missed something from the three articles…
How or where do I import my existing email addresses into this? I have not played around with it yet, so maybe it is straightforward.
Does the update function handle unsubscriptions from clicking the unsubscribe link in the email? I know MailChimp can be set up to notify you of this, so I suppose you wirl simple run the update script.
Thanks again!
This is an open template, so you can import your email addresses by going under the File Menu and selecting “Import Records” and then selecting ‘File’.
It was not really designed to be a full-fledged system, more of a demo of the concept. The idea would be to talk the principles and scripts in this system and apply them to an existing database.
The idea is to let MailChimp handle unsubscribes, turning on notification so that a human operator also get these emails. When they come in, they would make a corresponding change in the FileMaker data set.
You could take this automation the next step, setting up a unique email account that FileMaker receives notifications from MailChimp and then process those mails via scripts. I did not take my solution this far, imagining at least some human input to keep the two data sets aligned.
Sorry to keep bothering – I’m getting into the documentation on this and beginning to implement it into my membership DB. My issue is that we have more than one email list. Groups seemed to solve this, but MC support tells me they do not support the ability to give people permission to only send mail to a specific group in your list. Therefore, I most likely will have to support two lists – giving send permissions to allow certain people to send mail to a list. Your documentation states that scrips would need modofication. Can you give me an idea of where to start?
The downside of having multiple lists is that you have people in your database that need to be on both lists, all your actions have to be done multiple times, once for each list.
That said, the subscribe to list functions are already in there and it would just be a matter of modifying the Method Get Lists script to handle the return of more than one list API key. Expand the data structure to have a data table for Mailing Lists, with at least two fields, one for the Mailing List Name and one for the Mailing List API key. When you send the Method Get List, parse result you get back into names into that database. Then build a structure to assign people to lists (instead of groups). You can employ the same model as I use for Groups, but instead assign people to Lists. Hope this overview helps.
Thanks very much for sharing this. I’m struggling at the moment to import new mailchimp subscribers into my Filemaker Database. Part of it is knowing how to parse out the returned data once I’ve constructed the url to get the subscriber info from Mailchimp. Can you shed any light on this?
Please review Part I of my blog entries on integrating MailChimp with FileMaker. There is a section on parsing the text out. I am employing a Custom Function called ExtractText which helps in the process. It is detailed in the blog post. There is also another Custom Function on briandunning.com’s list of Custom Functions callled ParseData which adds the flexibility of dealing with multiple instances of data within a string.
With these Custom Functions installed, it should be fairly straightforward to parse out whatever text you are looking for. Just examine the returned data, look for patterns in the data, where you can grab the desired text out from between two recurring strings. Hope this helps.
Thanks for this and apologies for not scrutinising your earlier post more carefully. Lesson for the future! Matt
This is really great stuff mate – fantastic work. Just need to get my masses of groups in my DB to work with multiple lists and this will be flying along…
If you are a Windows User and are stumped with the inability of IE to handle json output in the Filemake Webviewer, you can use the free BaseElements plug-in from Goya.com.au to retrieve the json output into a field or variable into your Filemaker database.
The plugin includes a function BE_GetURL ( ) which can be used to send the necessary commands to the Mailchimp API.
Using the fields in the demo file, you can completely bypass the need for the webViewer element using the plug-in to rerieve the data into the $xmlContent variable like this:
set variable [$xmlContent; value:BE_GetURL ( “http://”& Settings::dataCenter & “.api.mailchimp.com/export/1.0/list?apikey=” & Settings::MailChimp Global API & GetAsURLEncoded ( “&” ) & “id=”&Settings::MailChimp Mailing List API & GetAsURLEncoded ( “&” ) & “output=json”)]
I’ve not yet been successful using the Export API in a similar fashion – I get a 301 Moved Permanently response, which I don’t understand. If anyone can tell me what’s wrong here, would be appreciated. I need to get the full subscriber details back into Filemaker so I can sync it up with my database information.
HTH
Brian Rich
Helpful Systems
Warwickshire
Oops! Wrong set variable statement: The one below returns json output OK
set variable{$xmlContent; value:BE_GetURL ( “http://”& Settings::dataCenter &
“.api.mailchimp.com/1.2/?method=listMembers&apikey=”&Settings::MailChimp Global API&”&id=”&Settings::MailChimp Mailing List API&”&output=json” )]
My previous post used the Export API url which I can’t get working….
Brian
Hello, thank you for this script. I have a Mailchimp list, and I’ve already imported it into FileMaker Pro, and I just want to keep the two lists in sync. So I want to add your script to the list I already have in Filemaker. How would I go about doing this?
Another question, I don’t see a MailChimp ID for each record in my files. I do see LEID and EUID fields. My understanding is that the LEID is a list members unique ID (even when their email address changes) and the EUID appears to be a unique ID for a particular email address.
This suggests that these scripts may be out of date, is this true? If so, do you have updated scripts?
Suggest you read through the articles to understand the integration process. Mailchimp have indeed updated their API. This however is the latest version I have. Still seems to work. I would go with EUID.