CiviCRM Community Forums (archive)

Yes, I have been wanting this functionality too. It's very useful for us to keep track of people's invalid addresses because a lot of times people will donate using checks that have old addresses (and we don't want to enter those addresses in again only to have them deleted again).

What I have done is created a new location type called "NoLongerValid". When we get our returned mail, a volunteer goes through it and marks all the addresses as the location "NoLongerValid" (as opposed to "Home", "Work", etc). Then I have a sql script that runs from the nightly cron that takes these NoLongerValid addresses and moves them in to the notes. Here is the script:

/* put NoLongerValid addresses into the notes */

set @invalid_location_prefix = 'NoLongerValid';
insert into civicrm_note
select
  NULL as id,
  'civicrm_contact' as entity_table,
  a.contact_id as entity_id,
  a.note as note,
  coalesce(min(log.modified_id), a.contact_id) as contact_id,
  a.log_date as modified_date,
  'Bad Address' as subject
from (
  select
    addr.id as address_id,
    addr.contact_id as contact_id,
    concat_ws(', ', street_address, supplemental_address_1,
      supplemental_address_2, supplemental_address_3,
      city, state.abbreviation, postal_code) as note,
    max(log.modified_date) as log_date
  from civicrm_address addr
  left join civicrm_state_province state
    on state.id = addr.state_province_id
  join civicrm_location_type loc
    on addr.location_type_id = loc.id
  left join civicrm_log log
    on log.entity_table = 'civicrm_contact' and log.entity_id = addr.contact_id
  where loc.name like concat(@invalid_location_prefix,'%')
  group by addr.id
  ) a
left join civicrm_log log
  on log.modified_date = a.log_date and a.contact_id = log.entity_id
  and log.entity_table = 'civicrm_contact'
group by a.address_id;

delete addr from civicrm_address addr
join civicrm_location_type loc
  on addr.location_type_id = loc.id
where loc.name like concat(@invalid_location_prefix,'%');

It's definitely a work-around for the missing functionality of being able to set an address as invalid though

(Also as a side note, I'd like to be able to attach a note to an address, see when an address was last updated, and check boxes for each address about whether that address should be used in bulk mailings.)