Quick Tip: Using eConnect to update Customer or Vendor Addresses with a blank Address Line 3

David Meego - Click for blog homepageI had a case this week that was asking a fairly commonly asked question about integrating address information using eConnect. The scenario is that we want to update a customer or vendor address and the previous address had three lines in the address and the new address only has two lines. When the update is processed, Address Lines 1 and 2 have been updated, but Address Line 3 has the old data and has not been cleared.

So how can I update Address Line 3 with blank data?

Background

Let's start with a little more background information.

Using the <taCreateVendorAddress> node, we can create a new vendor address with 3 lines. Below is the example XML used:

<eConnect>
  <PMVendorAddressType>
    <taCreateVendorAddress_Items>
      <taCreateVendorAddress>
        <VENDORID>ACETRAVE0001</VENDORID>
        <ADRSCODE>WAREHOUSE</ADRSCODE>
        <UPSZONE></UPSZONE>
        <SHIPMTHD></SHIPMTHD>
        <TAXSCHID></TAXSCHID>
        <VNDCNTCT></VNDCNTCT>
        <ADDRESS1>Unit 42</ADDRESS1>
        <ADDRESS2>XYZ Building</ADDRESS2>
        <ADDRESS3>100 Main Street</ADDRESS3>
        <COUNTRY>Australia</COUNTRY>
        <CITY>Perth</CITY>
        <STATE>WA</STATE>
        <ZIPCODE>6000</ZIPCODE>
        <PHNUMBR1></PHNUMBR1>
        <PHNUMBR2></PHNUMBR2>
        <PHNUMBR3></PHNUMBR3>
        <FAXNUMBR></FAXNUMBR>
        <UpdateIfExists>1</UpdateIfExists>
        <RequesterTrx></RequesterTrx>
        <CCode></CCode>
        <USRDEFND1></USRDEFND1>
        <USRDEFND2></USRDEFND2>
        <USRDEFND3></USRDEFND3>
        <USRDEFND4></USRDEFND4>
        <USRDEFND5></USRDEFND5>
      </taCreateVendorAddress>
    </taCreateVendorAddress_Items>
  </PMVendorAddressType>
</eConnect>

 This creates the address as shown in the screenshot below:

 

 So now we want to update this address.  We no longer want the XYZ Building line in the address and so change address lines in the above xml document to be:

        <ADDRESS1>Unit 42</ADDRESS1>
        <ADDRESS2>100 Main Street</ADDRESS2>
        <ADDRESS3></ADDRESS3>

Note: We also have the element <UpdateIfExists> set to 1, so it can both create and update addresses.

After importing the update XML document our address looks like the screenshot below: 

As you can see, the Address Line 3 has not been cleared from its previous value. The reason is that the eConnect code only updates fields where a value is passed and we did not pass a value for that field.

One suggestion I have heard was to pass a space through for the <Address3> element, however the space is counted as white space and ignored.

Below are two solutions. The first is the method which has been suggested in the past and the second is a much simpler solution that works well.

 

Solution 1: Custom Post Stored Procedure

This solution use the Post Custom Procedure to update the Address Line 3 when a special token value is passed updated on the field. So we will use the string "[Blank]" to signify that we want this field to be blank.

We then modify the taCreateVendorAddressPost stored procedure by adding the following update statement just before the return (@O_iErrorState) statement.

update PM00300 set ADDRESS3 = ''
where VENDORID = @I_vVENDORID and ADRSCODE = @I_vADRSCODE
and ADDRESS3 = '[Blank]'

So when we import the xml with the address lines as shown below, the custom post procedure will clear Address Line 3 for us.  

<ADDRESS1>Unit 42</ADDRESS1>
<ADDRESS2>100 Main Street</ADDRESS2>
<ADDRESS3>[Blank]</ADDRESS3>

 

Solution 2: Using CDATA to pass a space 

This solution was suggested by my colleague Allan Cahill and is even simpler, it does not need any custom stored procedures. It works on the idea of passing a space character to stored procedure in such a way that it is not ignored as white space. Using the syntax <![CDATA[ ]]>, we can pass the space through. This will update the Address Line 3 field with a space, but as Dynamics GP automatically strips leading and trailing spaces, this will be the equivalent of a blank or empty field. Below is the address lines from the XML document:

<ADDRESS1>Unit 42</ADDRESS1>
<ADDRESS2>100 Main Street</ADDRESS2>
<ADDRESS3><![CDATA[ ]]></ADDRESS3>

For more information on this method have a look at Chris Roehrich's post: Serializing CDATA tags in eConnect XML Documents.

 

Result

Both of these solutions work, you can decide which one is best for you. The screenshot below shows the updated address field with Address Line 3 now blank:

 

Hope this information is useful to you.

David