Sometimes it’s obvious a company has a decent software product but there’s one part of their development they don’t put enough time and resources into.  For InfusionSoft, it’s their documentation, support, and their API.  It’s a pretty powerful tool for normal companies, like WordPress is great for most websites, but your company isn’t like most companies, is it?

All built-in InfusionSoft database table field names for any contact record and other records, like for campaigns, vendors, etc can be found at:  https://developer.infusionsoft.com/docs/table-schema/

Apparently, the two guys in the garage that cobbled this CRM together initially before handing it over to a larger team of developers now stuck in the initial constraints of the system they designed set some immovable and immutable rules that leaves any web developer with the sense of when using their API they’re dealing with some pretty old technology and design concepts that just won’t go away for the sake of better usability, speed, and stability.  The irony here is that if InfusionSoft improved their API, documentation and sped their platform up they would get more customers and make more money.

When searching for an InfusionSoft custom field that you want to check if it’s empty, you can use the search value of “~null~”, which the InfusionSoft developers created to define a blank field.

$queryArr = array('_customField'=> '~null~' );
$returnFieldsArr= array('Id','Email','LastName','Phone');
$resultsArr=$app->dsQuery("Contact",99,0,$queryArr,$returnFieldsArr);

This will return all contact records that were never populated with a value before, whatever the “_customField” for your system is.

Remember that ALL CUSTOM FIELDS in InfusionSoft are accessed with a leading underscore (“_”), by the way.   It’s not our place to ask why, that’s just the way they designed the system.

The code below will find all contact records at least once had the custom field populated but is currently empty or null:

$queryArr = array('_customField'=>'' );
$returnFieldsArr= array('Id','Email','LastName','Phone');
$contacts=$app->dsQuery("Contact",99,0,$queryArr ,$returnFieldsArr);

You only need to use the “~null~” parameter in these InfusionSoft API queries for your custom fields, of course, right?!  Why not “tilde null tilde”?!  Good grief…  For InfusionSoft’s standard fields, you can use the familiar empty string “” query and that will return all currently empty values, regardless if it they were ever populated or not.  InfusionSoft is a bit clunky and behind the times in some ways, in other ways, you may find it is a useful tool for your company.  I think it’s overpriced, but most CRMs are.  Look into Open ERP first, I think it’s still free though I see it was taken over by Odoo.  InfusionSoft’s just not meant to be strong and flexible for companies that have challenges more diverse than the cookie cutter solutions they provide, companies with many different groups of contacts, customers, and data sets may find their API is too slow to interface with their database and systems.