CiviCRM Community Forums (archive)

Is there any way to use the API to do searches for empty records or wildcard/LIKE searches?

What I'm trying to do run an update on about 25% of our contact records (about 1000 records) so that one of the custom fields gets updated from hook_civicrm_post. If I try anything more than rowcount 100 it maxes out the memory usage. So I have to run it with limits and offsets.

Here's what I'm currently using:

civicrm_api('Contact','Get',array('contact_type' => 'Individual','custom_7' => '','version' =>3,'sort'=>'contact_a.id ASC','rowCount' => '100','offset'=>"$offset"));

 What I would like is to be able SQL-style search where custom_7 = '', but can that be done with the API?  The above statement seems to return all records, not the ones with the custom_7 field empty.  I can't find any documentation describing how to do this.

I had seen the API explorer but hadn't really played with it.  I think the explorer page could use some on-page documentation or encouragement, right now it looks like something I would make I think it would also help if the explorer displayed the resulting SQL statement as well.

Anyway  I have so far discovered that you can use wildcards just like in MYSQL, so 'custom_7=Van%' and 'custom_7=Vancouver' will both find all records with custom_7 field = Vancouver.  'display_name' seems to assume a wildcard, so that 'display_name=David' will find records for "David Alpha" and "David Beta".  Go figure, inconsistent. Maybe some other fields assume a wildcard as well.

I still haven't figured out how to query for records with empty fields. If I try 'custom_7=' it seems to work the same way as 'custom_7=%', that is, returns all records.  'custom_7=''' obviously shows records with the field containing two single quotes.

Any suggestions?