Friday, May 28, 2010

Clearing Existing Field Values Using eConnect

Let's say that you have developed an eConnect customer import for Dynamics GP. It works great, importing new customer records quickly and efficiently. Because customers occasionally change their address, phone number, e-mail address, or other contact information, you set your customer import to use the UpdateIfExists flag to update existing customer records.

What's the catch with this technique?

Well, if you are using the eConnect serialization assemblies, you will find that if you set a property to an empty string, such as rmCustomer.ADDRESS2 = "", if the existing customer already has a value for ADDRESS2 in GP, it will not be cleared from the customer record.

Chris Roehrich has an excellent article on the Developing for Dynamics GP blog explaining why this happens and how to properly clear the field using the CDATA tag.

Chris gives a simple example of how you might serialize your eConnect XML, and then insert a new node for the field that you want to clear out, and then set the value of that node to the CDATA[ ] value.

It's a good example for demonstration purposes, but yesterday, when I realized I had to implement the CDATA trick, I wasn't keen on serializing the XML, and then manually inserting an arbitrary number of nodes back into the XML. I had at least 15 different fields that could be blank, and I didn't want to have to write statements to manually insert up to 15 different nodes into an XML document. Me, lazy? Guilty as charged.

After sleeping on it, I came up with an idea that I think is a more elegant solution. This trick is one that I learned back in 1994 while writing custom macro "programs" in Word Perfect 5 for DOS--when your tools are very rudimentary, you come up with some creative workarounds to meet your requirements. It is a technique that is also sometimes used with complex Regular Expressions, and it has come in handy several times over the years to make complex string problems much easier to solve.

Here's my thinking: Instead of using the eConnect serializer to output XML, and then manually insert missing nodes back into the XML, why don't we let the serializer do its job and output all of the customer nodes? "Well, duh,", you might say, "because if we leave the nodes blank, the serializer will not output them!".

True. But who said that we needed to leave them blank?

Instead of assigning an empty string to a property when the field value is blank, I shall instead assign a 'dummy' value to the property. In my project, I arbitrarily chose "~~~", because I'm pretty confident that my customer data will not contain 3 consecutive tildes. "^^^" or any other very unlikely combination of characters would work just as well.

So, I first setup a string with my dummy value, that I've called "clearValue", and also setup a string called "cdataValue".

I then add an if statement to my customer import routine, before the serialization process, that will replace empty properties with my dummy value.

I don't need to know, or want to know, anything about the specific node names or XML structures, and I don't want to create or insert or append any nodes. I just set the property values to my substituted version of an empty string.

Now that the "blank" values have been set, I serialize the XML to a string as I would normally do, and we get this stunning work of art:

After admiring that for a second, I then use the Replace function to replace all of the dummy values in my XML with the CDATA value.

transactionXML.Replace(clearValue, cdataValue);

Bam! All of my blank fields / nodes now have the CDATA value.

And when I sent this lovely morsel of XML goodness over to eConnect, it does its magic and duly clears out any fields for nodes having the CDATA value.

For any disbelievers out there, here is a screen shot before and after.

Works pretty slick, if I do say so myself...


Kevin said...

Why even use clearValue? Why don't you just set the value to cdataValue, get rid of the replace, and be done with it?

Steve Endow said...

Hi Kevin,

You need to read Chris' original article that I reference.

He explains that the CDATA tag must be added to the serialized XML, as it will be escaped out by the eConnect serialization process.



Stephan said...

Very nice! I might be too much of a fan of workarounds, but this method does please me a lot! I'll try to remember it if I have to use eConnect in the near future.