CiviCRM Community Forums (archive)

I had a discussion with lobo in IRC the other day regarding this issue, but I wanted to write it all out as I haven't quite figured out the best way to do this yet.

I am trying to use the search builder to find all records from a certain group of states. Unfortunately there is a limit to how many "also include contacts where"s you can have (seems to be a total of 9). Lobo's suggestion, along with the documentation on Search Builder[1] talks about using the IN operator. It seems to work great with the example of searching groups, but I get an error when trying to do the same with states.

The example is: Individuals -> Group(s) -> IN -> (1,3)
I'm searching: Individuals -> State -> Primary -> (1000,1004)

What I get is an sql error.[2]

Is there something wrong with my syntax, or is this a limitation for searching state data?
Thanks!
Colin

[1] http://wiki.civicrm.org/confluence/pages/viewpage.action?pageId=8370

[2] Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a LEFT JOIN civicrm_location ON (civicrm_location.entity_table = 'civicrm_contact' AND
                                                           contact_a.id = civicrm_location.entity_id AND civicrm_location.is_primary = 1) LEFT JOIN civicrm_address ON civicrm_location.id = civicrm_address.location_id  LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  WHERE  (  ( LOWER(civicrm_state_province.name) IN '(1000,1004)' AND contact_a.contact_type IN ('Individual') )  )  AND  ( 1 )   ORDER BY LEFT(contact_a.sort_name, 1)   [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(1000,1004)' AND contact_a.contact_type IN ('Individual') )  )]
    [type] => db_error
    [user_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a LEFT JOIN civicrm_location ON (civicrm_location.entity_table = 'civicrm_contact' AND
                                                           contact_a.id = civicrm_location.entity_id AND civicrm_location.is_primary = 1) LEFT JOIN civicrm_address ON civicrm_location.id = civicrm_address.location_id  LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  WHERE  (  ( LOWER(civicrm_state_province.name) IN '(1000,1004)' AND contact_a.contact_type IN ('Individual') )  )  AND  ( 1 )   ORDER BY LEFT(contact_a.sort_name, 1)   [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(1000,1004)' AND contact_a.contact_type IN ('Individual') )  )]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a LEFT JOIN civicrm_location ON (civicrm_location.entity_table = 'civicrm_contact' AND
                                                           contact_a.id = civicrm_location.entity_id AND civicrm_location.is_primary = 1) LEFT JOIN civicrm_address ON civicrm_location.id = civicrm_address.location_id  LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  WHERE  (  ( LOWER(civicrm_state_province.name) IN '(1000,1004)' AND contact_a.contact_type IN ('Individual') )  )  AND  ( 1 )   ORDER BY LEFT(contact_a.sort_name, 1)   [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(1000,1004)' AND contact_a.contact_type IN ('Individual') )  )]"]
)[/code]