Infusionsoft API Gotchas

There are a few gotchas about Infusionsoft we’ve learned over time, and this is where we document them.  Hopefully Infusionsoft will get them all resolved eventually, but for now they are issues that you need to be aware of when working with the API.

A Few Things That are Not Well Documented

Custom fields always start with an underscore in the API. For saving and retrieving you must prefix custom fields with an underscore. Performing like searches. You can use SQL-style like searches in DataService.query. Just put % signs in your value just like you would for a like search. Infusionsoft automatically sees these and does a like search instead of an exact match. Getting all records. To get all records, pick a field and do a like search on it. For example,  ‘Id’ => ‘%’. Custom field ~null~ and empty strings. When a record is created in Infusionsoft, custom field values without a specific value are either set to null or blank, depending upon how the record is created. To select records with a null value in a custom field, search for “~null~”.  To fetch all records that don’t have a value in a field yet you’ll need to do two queries: one where it checks the field name for “~null~” and another where you look for an empty string.

Basic API

Misnamed Tables.  Infusionsoft evolved from a piece of custom software to the tool that helps over 24k small businesses today.  As it evolved, the API was never updated to reflect the changing nature of some of the tables.  Here are some guides.  Opportunities are stored in the Leads table. Orders are stored in the Job table.  Subscriptions are stored in the RecurringOrder table.  Tags are called Groups in the API.  So, Tags are stored in the ContactGroup table, ContactGroupAssign stores what tags a contact has, and the Groups column in the Contact table is a comma seperated listed of tagids. Empty response. Infusionsoft has a bug where occasionally the response from an API call will be either blank or have an error about not being able to create a session. To overcome this, we’ve put some code in our SDK to retry calls that are safe to retry (like deletes and updates) anytime we get a bad response. Invalid XML entities in API responses. It is possible to save data in Infusionsoft that contains invalid XML entities and these are passed via the API, which causes the xmlrpc.inc parser most of us use for PHP (including the Infusionsoft SDK) to fail when parsing the XML-RPC response. To overcome this, our SDK includes a tweaked version of the xmlrpc.inc file that does a global search and replace on the XML response to replace the XML entities with a suitable replacement before parsing. The entities we currently replace are:  �   We replace these with a space. Connection errors. A few years ago we started seeing weird ssl errors.  At the time there seemed to be a bad router or something somewhere in their data center. Every few thousand we would run into an issue of not being able to connect to the API endpoint. You could view this with traceroute to see that there was one router that traceroute doesn’t get past every few dozen calls.  We’ve worked around this by putting code in our SDK to retry on all calls when a connection isn’t established.  The problem could be non-existent anymore, but SSL certificate. The CA for the Infusionsoft SSL certificate isn’t included in the most common cURL distributions, so you have to load your own ca bundle into cUrl, or disable verify peer (ssl will no longer protect fro man in the middle attacks if you do this). IDs not always sequential. Infusionsoft uses a multi-master MySQL database cluster. What this means is that there are two MySQL servers that at any time can both be the server for your application. The servers are setup to constantly update each other when one of them changes something. To prevent auto-increment collisions, each server in a multi-master generates IDs differently. For example, server A always creates IDs in the form of 7n + 1, server B is always 7n + 2. This ensures that no server will ever try to generate an auto-incremented id that another server would also try to create. So, how does this impact you? If the two servers get out of sync, or the app fails over to the backup server, it will start generating even numbered IDs. So you may get IDs in the sequence of 1, 3, 4, 5, 7, 8, 10, 12, 14, 16, 9, 11, 13…  We’ve also seen weird issues where a new record will get a really really old id for no explained reason. Case Sensitivity Infusionsoft made a change in their latest release (1.30.x) that validates fields, and while prior to the release fields were case in-sensitive, now they are case-sensitive. Case conventions to always correct. Several table fields have Id in caps (i.e. ID).  Here is a list as of April 2013:

• Company->CompanyID (This field is here because Companies and Contacts are the same table, I don’t think you can really have a company be part of a company)
• Company->OwnerID
• Contact->CompanyId
• Contact->OwnerID
• ContactAction->UserID
• Lead->ContactID
• Lead->UserID
• Lead->StageID
• Lead->StatusID

API invoicing and order creation. You can add order items that have more than 2 decimal places for the cost (tax is a place this comes into account sometimes via the API, or percentage discounts you calculate yourself). When infusionsoft charges the invoice it ignores the extra fractions of cents, and depending upon whether it rounds up or down, your invoice will show as not paid in the order view screen. So make sure you always round your price to 2 decimal places when there is a chance the price has more precision than 2 decimal places. Dates Infusionsoft services and tables sometimes specify date arguments/fields as type String instead of type Date. You have to send the dates different for Date vs String. If the argument/field is specified as type “date” it needs to be in a modified version of the iso 8601 format (but with no timezone designator, remember all dates and times are in Eastern time in Infusionsoft) which you can easily do in php with this code: date(‘Ymd\TH:i:s’). When a date is specified as a String, it expects it in regular MySQL format: date(‘Y-m-d H:i:s’). Most services specify a date type of “Date” whereas most Tables specify date as type “String”. You can’t charge a card for a partial amount. We’re working on a way in our SDK to create a separate order for the amount you want to charge, charge the card, and then add a manual payment to the invoice you’re trying to pay, and if the charge fails delete the temporary order, but it’s not fully implemented yet. You cannot edit existing order items (or delete them) via the API. This is a frustrating limitation of the API. Linked Contacts. Infusionsoft hasn’t exposed Linked Contacts via the API. This is a gotcha because it’s a powerful feature, that should be a cinch to expose (two tables?) but hasn’t been. Missing content-type. The legacy cart images don’t get a content-type when they are returned, but they are JPEGs. MySQL: latin1_swedish_ci character set limitation: You may receive this error: Failed to invoke method query in class com.infusionsoft.api.xmlrpc.XmlRpcDataService: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘<=>’ Attempted: 1 time(s).  Infusionsoft uses the default mysql “latin1_swedish_ci” character set for most (all?) of their tables.  Because of this, without encoding it yourself (which would look terrible inside Infusionsoft) you can’t store a string with any characters that are not in the mysql “latin1_swedish_ci” character set.  If you try via the API, you get the error listed above.  The only way around this is to replace the character that isn’t in “latin1_swedish_ci” with a character that closely resembles it but is a valid character.  So, we would replace the ū with u instead. Inaccesible Fields. The Contact SSN field is not read or writeable via the API currently. ProductOptionValue – You MUST set the OptionIndex field when you create new ProductOptionValue records to be unique for that ProductOption, or else Infusionsoft will just update the record with the same OptionIndex value. You can only opt-in someone once –  If you use the APIEmailService.optIn method to opt-in an email address, you can only do it once per email address.  So, if someone opts-out and then comes back and you need to opt-in their address via the API, you can’t.  You have to make a fake webform post with their email to get them opted back in.

Campaign Builder and the API

Products purchased via the API do not trigger the product purchased goal. – For whatever reason, Products purchased via the API will not trigger campaign goals.  Only products purchased via the Shopping Cart or an Order Form trigger the campaign builder product purchased goal. “API Call is made” goal is misleading – Frequently people think that this goal gives you the ability to perform an action when ANY api call is made.  This is not correct.  The Infusionsoft API Has a call named “FunnelService.achieveGoal” it takes 3 parameters: contactId, integrationName, callName.  The “API CAll is made” goal is only completed whenever the FunnelService.acheiveGoal call is made and the integrationName and callName used in the call match what is setup in the campaign builder goal.