SugarChimp Compatibility With Customizations

If you have an add-on you would like to make compatible with SugarChimp OR you have a customization in Sugar that needs to work with SugarChimp, this document will help explain how to integrate those changes with SugarChimp.

For example, you may have a Sugar customization that runs straight sql queries on the Sugar database. If these queries update a Contact record, SugarChimp will not know that this change was made, so the data will not be updated on the Mailchimp side. The best way to handle this is to change your customization so that it uses the Sugar bean to make the change. This way SugarChimp will catch the Sugar logic hook that is fired and make the update in Mailchimp. For example:

$contact = BeanFactory::getBean('Contacts','abcd-1234-abcd-1234-abcd-1234');
$contact->first_name = 'Bob';
$contact->save();

In this example because the code is using a Sugar "Bean" to make the change, the logic hooks put in place by SugarChimp would catch this change and it would know to make the changes in Mailchimp. If you are running direct database queries, SugarChimp will not know the change is being made.

If you cannot change your customization to work like this, the information below will guide you through what is needed to make your customization compatible with SugarChimp.

Currently Integrated Add-ons

• Quick2List Dynamic Target Lists - keeps your lists up-to-date automatically based 
  on your defined conditions.

Want to Integrate Another?

If you want to integrate your Sugar Target List add-on with SugarChimp. The process goes something like this:

• Your add-on runs normally
• When your add-on detects changes need to be made to a Target List, it does so
• While it does this, if SugarChimp is installed & if the Target List is synced to a Mailchimp list, insert appropriate records into 'sugarchimp' queue table

For the SugarChimp installation check it may be best to check if the 'sugarchimp' table exists. You can do that with a query like this: SHOW TABLES LIKE 'sugarchimp'

You need to make sure whatever you do it works for both mysql and mssql, as well as making sure it works in the On-demand environment.

To check if the Target List is synced to a Mailchimp list, if the 'mailchimp_list_name_c' field exists on the 'prospect_lists' table and is not empty then it's synced. You can do something like this if you if $bean is a Target List bean object: if (!empty($target_list->mailchimp_list_name_c)) { ... }

Whenever you add someone to the list, you'll want to add either a 'UpdateMailchimpSubscriber' or 'UpdateMailchimpSubscriberGroup' job.
Whenever you remove someone from the list, you'll want to add a 'RemoveMailchimpSubscriber' job.

Here's the schema for 'sugarchimp':

Some sample data:

There are three types of jobs in the queue which are defined by the 'name' field: UpdateMailchimpSubscriber, UpdateMailchimpSubscriberGroup and RemoveMailchimpSubscriber

UpdateMailchimpSubscriber - When you add someone to a list. Important fields to insert: 
* mailchimp_list_id
* param1 (module name of the record: Prospects, Contacts, Leads)
* param2 (bean id of the record)

UpdateMailchimpSubscriberGroup - If a Sugar Target List includes Mailchimp Group fields in it's field mapping list, you'll want to queue a sync that includes a MailChimp Groups update. 
* mailchimp_list_id
* param1 (module name of the record: Prospects, Contacts, Leads)
* param2 (bean id of the record)

RemoveMailchimpSubscriber - When you remove someone from a list. Important fields to insert: 
* mailchimp_list_id
* param1 (module name of the record: Prospects, Contacts, Leads)
* param2 (bean id of the record)
* param3 (email address of the person being removed)

UpdateMailchimpSubscriberEmailChange - When you change a records primary email address. Required fields:
* mailchimp_list_id
* param1 (module name of the record: Prospects, Contacts, Leads)
* param2 (bean id of the record)
* param3 (the old email address, the one being replaced)
* param4 (the new email address)

This should be all you need to do to update the SugarChimp/Mailchimp side of things. Once you've added the records to the sugarchimp table, the SugarChimp scheduler will handle the rest. Also, you don't have to worry about changes that conflict each other. SugarChimp will process the jobs in the order they were put into the queue, so the last action is what will ultimately stick.

How to find the mailchimp_list_id and bean_id?

The mailchimp_list_id value is the ID of the list in Mailchimp. This gets stored in the mailchimp_list_name_c column of the prospect_lists table in Sugar. Each of your synced Sugar Target Lists will have a mailchimp_list_name_c value. It is possible for a Contact/Target/Lead/Account to be on more than one Sugar Target List, so you will want to get all mailchimp_list_name_c values that they are related to. You might use something like this to find those values:

SELECT DISTINCT mailchimp_list_name_c FROM prospect_lists pl
INNER JOIN prospect_lists_prospects plp ON plp.prospect_list_id=pl.id
INNER JOIN contacts c ON c.id=plp.related_id
WHERE c.id='guid of the contact'

The bean_id is the record ID (usually a GUID) in the contacts/prospects/leads/accounts tables. This is the unique identifier for the record you're wanting to sync to Mailchimp.