I 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?
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_vADRSCODEand 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.
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.
Posting from Jivtesh Singh at About Dynamics, Development and Life
Posting from Mark Polino at DynamicAccounting.net
Excellent tip David!
can we give 'FacilityId' in USRDEFND1 element . if yes, how ?
The 5 User defined parameters available in eConnect can be used to pass any data.
You can then use the pre and post customisation stored procedure scripts to use that data and update or insert it into tables as required.
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.