CiviCRM Community Forums (archive)

Hi.

I'm trying to create mailing labels using v3.1.2. All contacts have full address info filled in. When I hit 'make mailing labels' with 'primary address' selected, I get:
Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, contact_a.do_not_email  as `do_not_email`, contact_a.do_not_phone  as `do_not_phone`, contact_a.do_not_mail  as `do_not_mail`, contact_a.do_not_sms  as `do_not_sms`, contact_a.do_not_trade  as `do_not_trade`, contact_a.is_opt_out  as `is_opt_out`, contact_a.legal_identifier  as `legal_identifier`, contact_a.external_identifier  as `external_identifier`, contact_a.nick_name  as `nick_name`, contact_a.home_URL  as `home_URL`, contact_a.image_URL  as `image_URL`, contact_a.preferred_communication_method  as `preferred_communication_method`, contact_a.preferred_mail_format  as `preferred_mail_format`, contact_a.source  as `contact_source`, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, contact_a.job_title  as `job_title`, contact_a.birth_date  as `birth_date`, contact_a.is_deceased  as `is_deceased`, contact_a.deceased_date  as `deceased_date`, contact_a.mail_to_household_id  as `mail_to_household_id`, contact_a.user_unique_id  as `user_unique_id`, contact_a.employer_id  as `current_employer_id`, addressee.value as addressee_id, addressee.label as addressee, email_greeting.value as email_greeting_id, email_greeting.label as email_greeting, postal_greeting.value as postal_greeting_id, postal_greeting.label as postal_greeting, gender.value as gender_id, gender.label as gender, individual_prefix.value as individual_prefix_id, individual_prefix.label as individual_prefix, individual_suffix.value as individual_suffix_id, individual_suffix.label as individual_suffix, contact_a.addressee_display as addressee_display, contact_a.addressee_custom as addressee_custom, contact_a.email_greeting_display as email_greeting_display, contact_a.email_greeting_custom as email_greeting_custom, contact_a.postal_greeting_display as postal_greeting_display, contact_a.postal_greeting_custom as postal_greeting_custom, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) AS current_employer, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.street_number as `street_number`, civicrm_address.street_number_suffix as `street_number_suffix`, civicrm_address.street_name as `street_name`, civicrm_address.street_unit as `street_unit`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, civicrm_address.name as `address_name`, civicrm_county.id as county_id, civicrm_county.name as `county`, civicrm_state_province.id as state_province_id, civicrm_state_province.abbreviation as `state_province`, civicrm_state_province.name as state_province_name, civicrm_country.id as country_id, civicrm_country.name as `country`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_email.is_bulkmail as `is_bulkmail`, civicrm_im.id as im_id, civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_openid.id as openid_id, civicrm_openid.openid as `openid`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_county ON civicrm_address.county_id = civicrm_county.id  LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)  LEFT JOIN civicrm_openid ON ( civicrm_openid.contact_id = contact_a.id AND civicrm_openid.is_primary = 1 ) LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id  LEFT JOIN civicrm_option_group option_group_addressee ON (option_group_addressee.name = 'addressee') LEFT JOIN civicrm_option_value addressee ON (contact_a.addressee_id = addressee.value AND option_group_addressee.id = addressee.option_group_id )  LEFT JOIN civicrm_option_group option_group_email_greeting ON (option_group_email_greeting.name = 'email_greeting') LEFT JOIN civicrm_option_value email_greeting ON (contact_a.email_greeting_id = email_greeting.value AND option_group_email_greeting.id = email_greeting.option_group_id )  LEFT JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender') LEFT JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id)  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix') LEFT JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id )  LEFT JOIN civicrm_option_group option_group_suffix ON (option_group_suffix.name = 'individual_suffix') LEFT JOIN civicrm_option_value individual_suffix ON (contact_a.suffix_id = individual_suffix.value AND option_group_suffix.id = individual_suffix.option_group_id )  LEFT JOIN civicrm_option_group option_group_postal_greeting ON (option_group_postal_greeting.name = 'postal_greeting') LEFT JOIN civicrm_option_value postal_greeting ON (contact_a.postal_greeting_id = postal_greeting.value AND option_group_postal_greeting.id = postal_greeting.option_group_id )  WHERE  (  ( contact_a.id IN (298 ) )  AND contact_a.is_deceased = 0 )  LIMIT 0, 1  [nativecode=1054 ** Unknown column 'civicrm_address.name' in 'field list']
  
(DEBUG ERROR SHOWN ONLY - OTHERWISE POST BECOEMS TOO LONG)


With 'Home address' selected it changes to:
Sorry. A non-recoverable error has occurred.

DB Error: no such field

Database Error Code: Unknown column 'Home-address.name' in 'field list', 1054

and 'work' generates:
DB Error: no such field

Database Error Code: Unknown column 'Work-address.name' in 'field list', 1054

- plus similar code

How do I sort this out?

Thanks!

Vincent[/code]

Hi Donald

Seems to work fine on the demosite (unsurprisingly)

Code for the home address error on my install:
Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT DISTINCT(contact_a.id) as contact_id, contact_a.display_name  as `display_name`, contact_a.do_not_mail  as `do_not_mail`, contact_a.is_deceased  as `is_deceased`, addressee.value as addressee_id, addressee.label as addressee, contact_a.addressee_display as addressee_display, contact_a.addressee_custom as addressee_custom, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code as `postal_code`, civicrm_country.id as country_id, civicrm_country.name as `country`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`, `Home-address`.id as `Home-address_id`, `Home-address`.street_address as `Home-street_address`, `Home-address`.supplemental_address_1 as `Home-supplemental_address_1`, `Home-address`.supplemental_address_2 as `Home-supplemental_address_2`, `Home-address`.city as `Home-city`, `Home-state_province`.id as `Home-state_province_id`, `Home-state_province`.name as `Home-state_province`, `Home-address`.postal_code as `Home-postal_code`, `Home-country`.id as `Home-country_id`, `Home-country`.name as `Home-country`, `Home-address`.name as `Home-address_name`, `Home-county`.id as `Home-county_id`, `Home-county`.name as `Home-county`, `Home-address`.postal_code_suffix as `Home-postal_code_suffix`  FROM civicrm_contact contact_a
LEFT JOIN civicrm_address `Home-address` ON (`Home-address`.contact_id = contact_a.id AND `Home-address`.location_type_id = 1)  LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id ) 
LEFT JOIN civicrm_county `Home-county` ON `Home-county`.id = `Home-address`.county_id 
LEFT JOIN civicrm_state_province `Home-state_province` ON `Home-state_province`.id = `Home-address`.state_province_id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id 
LEFT JOIN civicrm_country `Home-country` ON `Home-country`.id = `Home-address`.country_id 
LEFT JOIN civicrm_location_type `Home-location_type` ON (  ( `Home-address`.location_type_id = `Home-location_type`.id )  )  LEFT JOIN civicrm_option_group option_group_addressee ON (option_group_addressee.name = 'addressee') LEFT JOIN civicrm_option_value addressee ON (contact_a.addressee_id = addressee.value AND option_group_addressee.id = addressee.option_group_id )  WHERE  (  ( contact_a.id IN (298,2,416,424,236,359,107,365,175,110,29,246,324,215,34,158,376,387,53,413,417,410,257,180,157 ) )  AND civicrm_address.location_type_id IN (1) AND contact_a.do_not_mail = 0 AND contact_a.is_deceased = 0 )  GROUP BY contact_a.id LIMIT 0, 25  [nativecode=1054 ** Unknown column 'Home-address.name' in 'field list']
    [type] => DB_Error
    [user_info] => SELECT DISTINCT(contact_a.id) as contact_id, contact_a.display_name  as `display_name`, contact_a.do_not_mail  as `do_not_mail`, contact_a.is_deceased  as `is_deceased`, addressee.value as addressee_id, addressee.label as addressee, contact_a.addressee_display as addressee_display, contact_a.addressee_custom as addressee_custom, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code as `postal_code`, civicrm_country.id as country_id, civicrm_country.name as `country`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`, `Home-address`.id as `Home-address_id`, `Home-address`.street_address as `Home-street_address`, `Home-address`.supplemental_address_1 as `Home-supplemental_address_1`, `Home-address`.supplemental_address_2 as `Home-supplemental_address_2`, `Home-address`.city as `Home-city`, `Home-state_province`.id as `Home-state_province_id`, `Home-state_province`.name as `Home-state_province`, `Home-address`.postal_code as `Home-postal_code`, `Home-country`.id as `Home-country_id`, `Home-country`.name as `Home-country`, `Home-address`.name as `Home-address_name`, `Home-county`.id as `Home-county_id`, `Home-county`.name as `Home-county`, `Home-address`.postal_code_suffix as `Home-postal_code_suffix`  FROM civicrm_contact contact_a
LEFT JOIN civicrm_address `Home-address` ON (`Home-address`.contact_id = contact_a.id AND `Home-address`.location_type_id = 1)  LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id ) 
LEFT JOIN civicrm_county `Home-county` ON `Home-county`.id = `Home-address`.county_id 
LEFT JOIN civicrm_state_province `Home-state_province` ON `Home-state_province`.id = `Home-address`.state_province_id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id 
LEFT JOIN civicrm_country `Home-country` ON `Home-country`.id = `Home-address`.country_id 
LEFT JOIN civicrm_location_type `Home-location_type` ON (  ( `Home-address`.location_type_id = `Home-location_type`.id )  )  LEFT JOIN civicrm_option_group option_group_addressee ON (option_group_addressee.name = 'addressee') LEFT JOIN civicrm_option_value addressee ON (contact_a.addressee_id = addressee.value AND option_group_addressee.id = addressee.option_group_id )  WHERE  (  ( contact_a.id IN (298,2,416,424,236,359,107,365,175,110,29,246,324,215,34,158,376,387,53,413,417,410,257,180,157 ) )  AND civicrm_address.location_type_id IN (1) AND contact_a.do_not_mail = 0 AND contact_a.is_deceased = 0 )  GROUP BY contact_a.id LIMIT 0, 25  [nativecode=1054 ** Unknown column 'Home-address.name' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT(contact_a.id) as contact_id, contact_a.display_name  as `display_name`, contact_a.do_not_mail  as `do_not_mail`, contact_a.is_deceased  as `is_deceased`, addressee.value as addressee_id, addressee.label as addressee, contact_a.addressee_display as addressee_display, contact_a.addressee_custom as addressee_custom, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code as `postal_code`, civicrm_country.id as country_id, civicrm_country.name as `country`, `Home-location_type`.id as `Home-location_type_id`, `Home-location_type`.name as `Home-location_type`, `Home-address`.id as `Home-address_id`, `Home-address`.street_address as `Home-street_address`, `Home-address`.supplemental_address_1 as `Home-supplemental_address_1`, `Home-address`.supplemental_address_2 as `Home-supplemental_address_2`, `Home-address`.city as `Home-city`, `Home-state_province`.id as `Home-state_province_id`, `Home-state_province`.name as `Home-state_province`, `Home-address`.postal_code as `Home-postal_code`, `Home-country`.id as `Home-country_id`, `Home-country`.name as `Home-country`, `Home-address`.name as `Home-address_name`, `Home-county`.id as `Home-county_id`, `Home-county`.name as `Home-county`, `Home-address`.postal_code_suffix as `Home-postal_code_suffix`  FROM civicrm_contact contact_a
LEFT JOIN civicrm_address `Home-address` ON (`Home-address`.contact_id = contact_a.id AND `Home-address`.location_type_id = 1)  LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id ) 
LEFT JOIN civicrm_county `Home-county` ON `Home-county`.id = `Home-address`.county_id 
LEFT JOIN civicrm_state_province `Home-state_province` ON `Home-state_province`.id = `Home-address`.state_province_id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id 
LEFT JOIN civicrm_country `Home-country` ON `Home-country`.id = `Home-address`.country_id 
LEFT JOIN civicrm_location_type `Home-location_type` ON (  ( `Home-address`.location_type_id = `Home-location_type`.id )  )  LEFT JOIN civicrm_option_group option_group_addressee ON (option_group_addressee.name = 'addressee') LEFT JOIN civicrm_option_value addressee ON (contact_a.addressee_id = addressee.value AND option_group_addressee.id = addressee.option_group_id )  WHERE  (  ( contact_a.id IN (298,2,416,424,236,359,107,365,175,110,29,246,324,215,34,158,376,387,53,413,417,410,257,180,157 ) )  AND civicrm_address.location_type_id IN (1) AND contact_a.do_not_mail = 0 AND contact_a.is_deceased = 0 )  GROUP BY contact_a.id LIMIT 0, 25  [nativecode=1054 ** Unknown column 'Home-address.name' in 'field list']"]
)
[/code]

All now seems well - thanks very much.

For anybody else who needs to create a new database for import, you can run a fresh install, then (using phpMyadmin in my case), copy the strucutre of your new db to a new-new db, THEN do your imports.

Spent a while working that one out, but got there in the end.......

Vincent