Answers.Body: <p>I wrote this little snippet of code in a larger extension, which will automatically append the postal code suffix to postal codes when exporting postal codes:</p>
  
  
  
  <pre><code>/**
   * On export, append the postal_code_suffix to the postal_code.
   */
  function tweaks_civicrm_export(&amp;$exportTempTable, &amp;$headerRows, &amp;$sqlColumns, &amp;$exportMode, &amp;$componentTable, &amp;$ids) {
    // Primary postal code will have a sqlColumns['postal_code'] = 'postal_code'.
    // Non-primary will have the location type appended - e.g. work_postal_code.
    foreach ($sqlColumns as $key =&gt; $column) {
      if (substr($key, -11) == 'postal_code') {
        // Primary postal_code.
        if ($key == 'postal_code') {
          // Update the primary postal code.
          $sql = "UPDATE $exportTempTable " .
          "LEFT JOIN civicrm_address ca ON $exportTempTable.civicrm_primary_id = ca.contact_id AND ca.is_primary = 1 " .
          "SET $exportTempTable.postal_code = IF(ca.postal_code_suffix, CONCAT(ca.postal_code,'-',ca.postal_code_suffix), ca.postal_code)";
          CRM_Core_DAO::singleValueQuery($sql);
        }
        // Non-primary postal code
        else {
          $locationType = str_replace('_', ' ', substr($key, 0, -12));
          $sql = "UPDATE $exportTempTable " .
          "LEFT JOIN civicrm_address ca ON $exportTempTable.civicrm_primary_id = ca.contact_id " .
          "JOIN civicrm_location_type clt ON ca.location_type_id = clt.id AND clt.name = '$locationType' " .
          "SET $exportTempTable.$key = IF(ca.postal_code_suffix, CONCAT(ca.postal_code,'-',ca.postal_code_suffix), ca.postal_code)";
          CRM_Core_DAO::singleValueQuery($sql);
        }
      }
    }
  }
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-15T23:38:35.350Z
Answers.LastActivityDate: 2019-11-15T23:38:35.350Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Body: <p>I send my addresses to a mailing house for a bulk magazine mailing.  They require my zip+4 in one field.  I can fix this in LibreOffice or Excel, but I have to be careful that my spreadsheet app doesn't eat the zip codes' leading zeroes.  Is there a better solution?</p>
  
Tags: <export><mailing>
Title: How can I export a zip code with the suffix in the same field?
ThreadText: # How can I export a zip code with the suffix in the same field?
  
  (Asked by: username_0 on 2019-11-15)
  
  I send my addresses to a mailing house for a bulk magazine mailing. They require my zip+4 in one field. I can fix this in LibreOffice or Excel, but I have to be careful that my spreadsheet app doesn't eat the zip codes' leading zeroes. Is there a better solution?
  
  **Tags:** export, mailing
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2019-11-15. Score: 2
  
  I wrote this little snippet of code in a larger extension, which will automatically append the postal code suffix to postal codes when exporting postal codes:
  /**
   * On export, append the postal_code_suffix to the postal_code.
   */
  function tweaks_civicrm_export(&$exportTempTable, &$headerRows, &$sqlColumns, &$exportMode, &$componentTable, &$ids) {
    // Primary postal code will have a sqlColumns['postal_code'] = 'postal_code'.
    // Non-primary will have the location type appended - e.g. work_postal_code.
    foreach ($sqlColumns as $key => $column) {
      if (substr($key, -11) == 'postal_code') {
        // Primary postal_code.
        if ($key == 'postal_code') {
          // Update the primary postal code.
          $sql = "UPDATE $exportTempTable " .
          "LEFT JOIN civicrm_address ca ON $exportTempTable.civicrm_primary_id = ca.contact_id AND ca.is_primary = 1 " .
          "SET $exportTempTable.postal_code = IF(ca.postal_code_suffix, CONCAT(ca.postal_code,'-',ca.postal_code_suffix), ca.postal_code)";
          CRM_Core_DAO::singleValueQuery($sql);
        }
        // Non-primary postal code
        else {
          $locationType = str_replace('_', ' ', substr($key, 0, -12));
          $sql = "UPDATE $exportTempTable " .
          "LEFT JOIN civicrm_address ca ON $exportTempTable.civicrm_primary_id = ca.contact_id " .
          "JOIN civicrm_location_type clt ON ca.location_type_id = clt.id AND clt.name = '$locationType' " .
          "SET $exportTempTable.$key = IF(ca.postal_code_suffix, CONCAT(ca.postal_code,'-',ca.postal_code_suffix), ca.postal_code)";
          CRM_Core_DAO::singleValueQuery($sql);
        }
      }
    }
  }
  
  
  ---
  
  