How to delete a non-primary address from a Customer through API

Hi Chris,

Please try use the below script

<root
    xmlns:dt="urn:schemas-microsoft-com:datatypes">
    <session email="" alias="">
        <state bc_name="customer_request">
            <action name="data">
                <main>
                    <row number="1">
                        <company_id>{company_id}</company_id>
                    </row>
                </main>
                <address_xref>
                    <row>
                        <is_primary>Y</is_primary>
                        <address_id>{new_address_id}</address_id>
                    </row>
                </address_xref>
                <address_xref>
                    <row delete="true">
                        <address_id>{old_address_id}</address_id>
                        <bo_search_keys>
                            <item>
                                <name>address_id</name>
                                <value>{old_address_id}</value>
                            </item>
                        </bo_search_keys>
                    </row>
                </address_xref>
            </action>
        </state>
    </session>
</root>

Regards,

Joshua

No joy unfortunately.

 

succeeded:false

Error Message:: At least one Address must be defined.

​​​​​​​

Can you screen shot the Customer Address tab?

This XML file does not appear to have any style information associated with it. The document tree is shown below.
<root>
<row>
<is_primary>Y</is_primary>
<table_key>NZ_Address_Test_Customer</table_key>
<table_name>company</table_name>
<address_id>929766</address_id>
<address_type_id>BWork</address_type_id>
<address_address_id>929766</address_address_id>
<location/>
<attention/>
<zip>1010</zip>
<state_prov_id>Auckland</state_prov_id>
<country_id>NZ</country_id>
<comment_text/>
<formatted_address/>
<address_address_type_id/>
<address_1>200 Victoria Street West</address_1>
<address_2/>
<address_3>200 Victoria Street West</address_3>
<address_name/>
<person_id/>
<city>Auckland</city>
<email_id/>
<fax_phone_id/>
<phone_id/>
<tzone_id>New Zealand Standard Time</tzone_id>
<tax_location_code>NZ</tax_location_code>
<pref_ship_method/>
<to_date/>
<fr_date/>
<longitude>174.7568027</longitude>
<latitude>-36.848334</latitude>
<last_change_by>cjohnstoHO</last_change_by>
<geocode_match_level>Manual</geocode_match_level>
<address_last_change_by>cjohnstoHO</address_last_change_by>
<last_change_date>2024-09-13 12:48:05 PM</last_change_date>
<address_type_descr>Business Address</address_type_descr>
<history_address_id>929766</history_address_id>
<cc_cst_new_address_1/>
<cc_cst_orig_address_1/>
<cc_cst_orig_city/>
<cc_cst_orig_zip/>
<cc_cst_orig_state_prov_id/>
<cc_cst_orig_country_id/>
<cc_address_type>Business Address</cc_address_type>
<cc_country_descr>New Zealand</cc_country_descr>
<cc_is_changed>False</cc_is_changed>
<cc_is_geocoding_changed>False</cc_is_geocoding_changed>
<cc_override_gl_level_change>N</cc_override_gl_level_change>
<cc_state_descr>Auckland</cc_state_descr>
<cc_type_of_delivery_address/>
<cc_validate_address_coding>Y</cc_validate_address_coding>
<gl_level_change>N</gl_level_change>
<cc_is_geocoding_changed_manually>False</cc_is_geocoding_changed_manually>
<cc_changed_from_load_def>False</cc_changed_from_load_def>
<cc_manual_longitude>181</cc_manual_longitude>
<cc_manual_latitude>181</cc_manual_latitude>
</row>
</root>

Hello,

yesterday I also did a cleanup related non primary addresses. I used an SQL request to find out the inactive address_id’s and deleted them with below API. Maybe that helps you.

API:

<root
    xmlns:dt="urn:schemas-microsoft-com:datatypes" initiator="import"
    xmlns="http://astea.com.schema.bc.customer_request">
    <session email="" alias="">
        <state bc_name="customer_request">
            <action name="data">
                <main>
                    <row number="1">
                        <company_id dtype="string" in_var="" out_var="">7221H</company_id>
                    </row>
                </main>
                <address_xref>
                    <row number="1" search_key="is_primary;N" delete="true">
                        <table_key dtype="string" in_var="" out_var="">33738997</table_key>
                        <table_name dtype="string" in_var="" out_var="">company</table_name>
                    </row>
                </address_xref>
            </action>
        </state>
    </session>
</root>

SQL request:

select company_id,is_primary,address_xref.address_id

from address_xref  with (nolock) 
left join address with (nolock) on address.address_id = address_xref.address_id
left join company with (nolock) on company.company_id = address_xref.table_key

 where table_name = 'company' and is_primary = 'N' and parent_company_id = 'whateveryouwant'

Regards

Torsten

---- Edit----

But I think the following API would meet your use case more:

<root xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <session email="" alias="">
    <state bc_name="customer_request">
      <action name="data">
        <main>
          <row number="1">
            <company_id dtype="string" in_var="" out_var="">{company_id}</company_id>
          </row>
        </main>
        <address_xref>
          <row number="1" delete="true">
            <is_primary in_var="" out_var="">N</is_primary>
            <table_key dtype="string" in_var="" out_var="">{company_id}</table_key>
            <table_name dtype="string" in_var="" out_var="">company</table_name>
            <address_id dtype="int" in_var="" out_var="">{old_address_id}</address_id>
          </row>
          <row number="2">
            <is_primary in_var="" out_var="">Y</is_primary>
            <table_key dtype="string" in_var="" out_var="">{company_id}</table_key>
            <table_name dtype="string" in_var="" out_var="">company</table_name>
            <address_id dtype="int" in_var="" out_var="">{new_address_id}</address_id>
          </row>
        </address_xref>
      </action>
    </state>
  </session>
</root>

Hi Chris,

The problem is that you need to add the new address 919110 first, then you can delete the old address.

The screen shot shows that you have only one address.

Below is the API to add new address and delete the old one

<root
    xmlns:dt="urn:schemas-microsoft-com:datatypes">
    <session email="" alias="">
        <state bc_name="customer_request">
            <action name="data">
                <main>
                    <row number="1">
                        <company_id dtype="string">NZ_Address_Test_Customer</company_id>
                    </row>
                </main>
                <address_xref>
                    <row number="1" search_key="address_id;919110">
                        <is_primary>Y</is_primary>
                        <address_id>919110</address_id>
                    </row>
                </address_xref>
            </action>
        </state>
        <state bc_name="customer_request">
            <action name="data">
                <main>
                    <row number="1">
                        <company_id dtype="string">NZ_Address_Test_Customer</company_id>
                    </row>
                </main>
                <address_xref>
                    <row delete="true">
                        <address_id>929766</address_id>
                        <bo_search_keys>
                            <item>
                                <name>address_id</name>
                                <value>929766</value>
                            </item>
                        </bo_search_keys>
                    </row>
                </address_xref>
            </action>
        </state>
    </session>
</root>

I use the import utility with a SQL script to find sites with non-primary addresses and then use an API to delete.

select top 5000 adx.table_key
,adx.address_id
from address_xref adx
join address ad on adx.address_id=ad.address_id
join company cy on adx.table_key=cy.company_id
where adx.table_name = 'Company'
and adx.is_primary <> 'Y'
and cy.is_active ='Y'

<root xmlns:dt="urn:schemas-microsoft-com:datatypes">
    <session email="" alias="">
        <state bc_name="customer_request">
            <action name="data">
                <main>
                    <row>
                        <company_id>{table_key}</company_id>
                        </row>
                    </main>
                <address_xref>
                    <row delete="true">
                        <is_primary out_var="" in_var="">N</is_primary>
                        <table_key out_var="" in_var="">{table_key}</table_key>
                        <table_name out_var="" in_var="" dtype="string">company</table_name>
                        <address_id out_var="" in_var="">{address_id}</address_id>
                        </row>
                    </address_xref>
                </action>
            </state>
        </session>
    </root>