We all have those projects where there is some dynamic portion that requires end user configuration. Those areas of the database always seem to be in churn because there is always one more field to add to control some aspect of the configuration. Or you have that project where the data you will be storing is different depending on other factors, so you end up with either a bunch of generic field names and some chart somewhere so you know what is in which field based on the type of record, or you have a bunch of fields that only a portion is ever used, and again it always seems you need to add one more field.

Storing that type of data as XML data in the database has resolved many of those types of issues for me. This week we will explore a couple of different situations where storing XML data provided a nice solution. So please read on…

Scenario One – Storing Different Data depending on other factors

Amost every aspect of TeamAlogy.com is based on configuration, the survey configuration, the resulting survey screens that are generated, everything about it. One of the areas of that configuration is what we call Place Holders. This is just a variation on old fashion mail merge. When the survey request email or the survey screens are configured, place holders can be used in the text, and at runtime they are replaced with the correct values.

Let me tell you we have a lot of place holders that can be used. Here is a screen shot of the Survey configuration page, with the place holder legend being displayed.

As you can see there is a lot going on there. We have 28 standard place holders, up to 10 Attribute Value place holders, and we have just added the two Multi Language place holders for the reminder phrases. But what we are looking at today is the Custom Place Holders. Believe it or not with all of those place holders, we still run into the need for additional custom place holders for clients!

Let’s look at the standard configuration and logic first as a foundation, then we will look at how the Custom Place Holders add to that. Here is the configuration screen for the Survey Request email, with all the place holders highlighted.

And here is the resulting email with the place holders replaced with their actual values.

The logic is fairly straight forward. As the email is being built, the subject line and the body are passed through a function called FillPlaceHolders.

There is obviously a lot more going on with building the email but those are the two lines we are concerned with this AM. And if we take a look at the FillPlaceHolders function we will see that for the most part it is just a bunch of replace statements, like these.

So that is the basics of the Place Holder system. But we run into issue with clients that need (or think they need!) to customize the emails and screens with additional data. For instance we work with a Summer Camp Program that uses our system to track Customer Satisfaction. But the challenge is they really have two customers, the parent that is actually paying for the camp and taking the survey, and the child, the camper that actually attends the camp. This gets further complicated because many parents send more than one child to camp, therefore we need to let the parent know which child’s experience we are asking about, as that effects which attributes (Age Group, Camp Attended, Gender, etc.) that the resulting data is segmented into.

So along with all of our standard place holders, and the custom attributes we also needed to know the campers names. We could have used an attribute for that, but there are only 10 allowed, and attributes are for segmenting the data for later analytics, and its doubtful that knowing that campers named Suzy are more satisfied than campers named Katie!

Custom Place Holders to the rescue. This is a prime example of additional data we need to collect, but we have no idea what that data would be. Our choices were.

1. Add a new field called CamperName to the database. What are the odds of that field being used by any other clients, and would start us down the path of adding additional fields for every client and before long the database would be a complete mess.

2. Add a new generic field called CustomPlaceHolder1. This option is a little more temping, but how many fields do we add (hint you will always need one more than you add), how long should they be, etc. etc. Then from an end user perspective that have to remember Camper First Name is in CustomPlaceHolder1 and Camper Last Name is in CustomPlaceHolder2.

3. Come up with a better solution. Which we did by adding one field to the database called CustomPlaceHolders.

Notice this is a Text memo field so it can hold a bunch of data. And we are going to store the data in XML format. The advantage to storing the data in XML format is along with the values, we will get field names, etc. so the data is still structured data, but now can be completely different for each client. So now all the client has to do is submit to us any custom place holders they need as XML structured data for that request. Here is the content of a request for a camper named Joe Smith.

Side Note: We actually build this XML data for many of our clients, but we do that via our dynamic import engine, that has been the subject of a few webinars. This allows the client to submit Excel or CSV data with all the fields in it, we configure a import routine to create the structured data, and then the rest of the system can deal with the structured data without having to do any additional programming.

So now that we have the XML data, all we have to do is configure the email to use the custom place holder **CamperFirstName**, notice the name matches the XML name exactly. Again none of the logic knows what data will be coming. The next client could be a vet and the XML data might contain PetsName and the place holder would be **PetsName**.

So the only piece of code that needs to handle this Place Holder is the FillPlaceHolders function. So lets look at the portion of code that handles the XML data.

I bet you thought it was going to be a lot more code than that, unless of course you have read any of my other articles then you would recognize the recurring theme, of how much can be done in WX with so little code.

Line 64 checks to see if there is any XML data. If not then no need to go any further.

Line 65 creates a special XMLDocument variable type. WX has a lot of XML specific features that make working with XML data a breeze. Many of them work with the XMLDocument variable type.

Line 66 moves the data from the database into the XMLDocument variable.

Line 67 makes sure that we have 1 and only 1 root node. What is a root node? In the example content we looked at that is the <CustomPlaceHolders> tag. The first tag is a root node, and as long as everything in the document is contained inside that opening and its corresponding closing tag </CustomPlaceHolders>, then there is only 1 root node.

Like 68 sets up a loop to loop through all of the attributes of the root node. This is where the real power of XML and the WX XML functions start to shine. Notice we don’t have to know what the values are or even what the root node was. In our example data this will loop twice, once for CamperFirstName, and a second time for CamperLastName.

Line 69 performs a replace statement, similar to the other replace statements in the function. The only different is it gets the name of the place holder it is looking for from the XML data. So in our first Loop, anAttribute..Name would contain CamperFirstName. That get concatenated together to read **CamperFirstName**, and the value of that attribute is Joe. So the replacement statement is the equivalent of

And with just those few lines of code, any custom place holder can be place in the XML data and automagically replaced at runtime.

 Scenario 2 – Storing Additional Options

Our second example also involves sending customized emails. But in this example from theTaxCure.com we are sending several different types of emails depending on different actions the user takes. It didn’t take long for use to realize having the content and options for those emails hard coded into the system was not the way to go. I always look for ways to make things end user configuration, instead of programmer configurable. What can I say I am lazy, as any good programmer should be!!!

So what we did was created a content table, that contained all the of the text and formatting for the email. That way if we decide to change the wording or format of the email it can be done via configuration. The only challenge is these are complicated HTML emails with inline images, etc., so the email procedure has to know what inline images to include with the email, what if we changed one of them? Things get further complicated with some additional options we need such as scheduling an email, instead of it being sent immediately.

After a few times of adding additional logic and database fields, I decided there had to be a better way. And again storing XML data was the answer. Let’s look at the revalent code from theTaxCure.com’s email function.

And some example data for one of the email configuration entries.

Lines 8,9  should look very familiar to you as they are very similar to our first example. We are setting up an XMLDocument Variable type, loading the XML data from the database.

Line 11 is slightly different. In this example, I require a specific root note, in this case <additionaloptions> to be present, and only loop through the attributes of that node. I will explain that a little later with some bonus material. Notice that I am able to reference the name of the node using a .property syntax, this is a really powerful and easy way to access XML data if you are aware of its structure.

Line 12 sets up a switch statement. Unlike our first example in this example, I need to have specific logic to handle the different possible attributes as they come in.

Line 13 checks to see if the attribute is <inlinefiles>, if so it sets up a loop that loops through all the attributes of <inlinefiles>, again you can see that I use the .property syntax to get the filename and mimetype from the attributes. Now you can start to see the real power of storing XML data, because it is structured data, we are able to store related child records. So in this case 6 different inline image files are configured for this email.

Line 17 checks to see if the attriubte is schedule, if so it sets up the delievertime variable for the email API.

And finally Line 21 just passes any other attributes to the email API, this lets us pass Subject, To, and any additional parameters the email API adds direct via the XML configuration.

So for a little bonus material, remember in this example we were looking specific for the <additionaloptions> root node. That also us to store different root node and other XML text in the additionaloptions database field without the two interfering with one another. For instance, along with Emails, our content table also contains our privacy policy and a few other generic display files, that we want to be able to edit from time to time without programming.

This content is displayed in a generic popup window that we created that looks like a prescription pad. This was shown in a previous webinar.

Depending on the content we may or may not want the Print button to be visible.  We control that via an entry in the XML configuration

And the code for doing that shows yet another method of working with XML data.

This time instead of placing the text in an XMLDocument variable type, which use the XMLExtractString function to look for the <PrintButton> XML attribute and retrieve its value. No need to go through all the other steps for such simple content as this configuration requires.

As you see with that example the use of XML configuration data let us have two completely different types of configuration, both contained in the same field.

There is are lot of explanations and examples of working with XML in the online help. I would suggest a good starting point would be here. There are two completely different methods of working with XML data which are shown here and here.

And one last tip for you, remember when working with XML data, everything is case sensitive. <MyAttributeName> and <myattributename> are two completely different attributes. And that becomes very important if you try searching for an attribute or referencing it by the .property syntax.

Be sure to go over to wxLive.us and watch the Uncle Pete’s corner webinar that is the companion to this article. 

Uncle Pete’s Corner is webinar series on all things WX, to watch the recorded version of this webinar, many other WX related webinars or to watch future ones live go to wxLive.us

[suffusion-the-author display=’author’]

[suffusion-the-author display=’description’]

• WEBDEV
• WINDEV
• WX