Want to search with a list of zip codes

[jimcrist]

Hi,
I've got CiviCRM installed and I imported contacts. Now I want to be able to search on a list of zip codes. For example, All contacts where the zip is one of these: 53711, 53719, 53595. The list might be a dozen or two dozen zips long. I tried using Search Builder, but couldn't get it to work at all?! Even searching on a single zip or a city...(e.g.  Contacts City Home = Madison. I keep getting this error: Sorry but we are not able to provide this at the moment. DB Error: no such field

The basic search works fine, as does the advanced search. So, two questions (1) searching on a list of zips (2) why isn't Search Builder working?

Thanks in advance...jim

[jimcrist]

I just did a debug / backtrace on the Search Builder and got this...it's greek to me!

backTrace

#0 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(149): CRM_Core_Error::backtrace()
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: no such field", -19, 16, (Array:2), "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#4 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-19, 16, (Array:2), "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#5 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...", "DB_Error", TRUE)
#6 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown column 'Home-address.city' in 'where clause'")
#7 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#9 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2434): DB_common->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#10 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1626): DB_DataObject->_query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#11 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(160): DB_DataObject->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#12 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(908): CRM_Core_DAO->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...", TRUE)
#13 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/BAO/Query.php(4424): CRM_Core_DAO::executeQuery("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#14 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/Selector.php(1076): CRM_Contact_BAO_Query->searchQuery(NULL, NULL, NULL, FALSE, FALSE, TRUE)
#15 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(81): CRM_Contact_Selector->alphabetQuery()
#16 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(106): CRM_Utils_PagerAToZ::getDynamicCharacters(Object(CRM_Contact_Selector), FALSE)
#17 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Utils/PagerAToZ.php(53): CRM_Utils_PagerAToZ::createLinks(Object(CRM_Contact_Selector), NULL, FALSE)
#18 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/Form/Search.php(937): CRM_Utils_PagerAToZ::getAToZBar(Object(CRM_Contact_Selector), NULL)
#19 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Contact/Form/Search/Builder.php(394): CRM_Contact_Form_Search->postProcess()
#20 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Form.php(261): CRM_Contact_Form_Search_Builder->postProcess()
#21 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75): CRM_Core_Form->mainProcess()
#22 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Contact_Form_Search_Builder), "refresh")
#23 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Search_Builder), "refresh")
#24 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Controller.php(339): HTML_QuickForm_Page->handle("refresh")
#25 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(327): CRM_Core_Controller->run((Array:4), (Array:0))
#26 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(76): CRM_Core_Invoke::runItem((Array:13))
#27 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#28 /home1/scwmtaor/public_html/wp-content/plugins/civicrm/civicrm.php(515): CRM_Core_Invoke::invoke((Array:4))
#29 [internal function](): CiviCRM_For_WordPress->invoke("")
#30 /home1/scwmtaor/public_html/wp-includes/plugin.php(496): call_user_func_array((Array:2), (Array:1))
#31 /home1/scwmtaor/public_html/wp-admin/admin.php(212): do_action("toplevel_page_CiviCRM")
#32 {main}

[jimcrist]

I don't think there's any Linux cron jobs set up for CiviCRM/WordPress, so that could be a problem? I'm not a Linux person...

[JonGold]

Hi Jim,

That shouldn't be the problem here - this is a SQL error.  You've posted the backtrace, but could you also paste the exact error?  I can see the SQL error, but it's cut off - pasting the full error will let us see the full SQL error. Also, maybe a screenshot of how you're using Search Builder here?  There's more than one way to do this search in Search Builder, one slightly more tedious but more foolproof.

[jimcrist]

Thanks Jon,
I've attached screen shots of the search criteria and the resulting error. Hopefully, that's the "full SQL error" you're referring to? If I do the same search using the Advanced Search I get 1733 records. My real goal is to be able to search for zip = 53701, 53711, 53590, etc.
Thanks!
Jim

[JonGold]

Hi Jim,

That's not quite what I meant - but how about trying your postal code search like in this screenshot?

[jimcrist]

Jon,
Awesome! Works like a charm. I could have sworn I tried Primary yesterday. Primary works. Home, which should work, gives me the DB Error: no such field. And the Regex zip|zip|zip works. That's exactly what I was looking for...thanks!
Jim