In the post Using InfoPath 2007 Translate function to do what XPath lower-case function does I talked about how to use the “translate” function in a XPath expression box and today I just wanted to continue down the path of using XPath expression box and XPath to compare two values in a Condition statement. In a normal circumstance this would be a very easy thing to do because you would just go select the two fields you want to compare and boom you’re done but what if the two field values are not always the same case. Then what do you do? How do you make everything the same case for both fields? This is a situation that I ran into not long ago and below is how I went about solving the problem.

Whenever building a condition statement you only have the options of choosing an existing Field, Group or “The expression” for the first dropdown list and in normal circumstances this would be fine. In normal circumstances if I wanted to compare two values and they were not fields already in the data source and they would not be visible fields then I would create a new Group called “HiddenFields” and then add the fields to that group and populate them in an opening rule. An example of the condition statement that I’m talking about is below.

This works great if all you want to do is a simple comparison and the field values will always be the same text case. As I stated above what if you run across a situation when the text case is going to be different in each field but you still want to compare. You might be thinking that you can use the "Use a Formula" option for each field but unfortunately the "Use a Formula" option is only available for the third dropdown. Below you can see what options are available for each dropdown.

After looking at the pictures you might have noticed you don't have all the selections available in the first dropdown list that you have in the third one. You're probably thinking that this is going to be very difficult to accomplish but in all actuality it's pretty simple if you use the "The expression" option.

When using the "The expression" option there are a couple of things that you can do to make your life a lot easier when building the XPath statement. If you first build the formula for the third dropdown list before changing the first dropdown list to "The expression" then it will copy everything you did into the XPath statement. I usually set the second and third dropdown list values before switching to "The expression" option in the first dropdown list. If you do it this way then you should get something like the below image when you switch.

The formula should look like this.

. = translate(my:HiddenFields/my:Field2, "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz")

The formula is now 2/3 the way done and now all we have to do is build and add the formula for the first field. The easiest way to do it for this example since the fields are in the same "Group" is to copy the current formula and replace the "." with it and then change the "my:Field2" text to "my:Field1". You might be saying that field box is too small and you wish you had more room to view the formula. If that's the case then open "Notepad" and do all you're editing in there and then paste it back into the field when you're done. At the end the condition box should look like the image below.

The formula should look like this.

translate(my:HiddenFields/my:Field1, "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz") = translate(my:HiddenFields/my:Field2, "ABCDEFGHIJKLMNOPQRSTUVWYXZ", "abcdefghijklmnopqrstuvwyxz")

Having the flexibility to add a formula to your fields when you compare them should open your world to a lot of new options.  Well it's time for me to get back to work and start preparing for my next gig that starts next week.  I'm pretty excited about this one because it sounds like I'll be doing a lot of cool SharePoint development work.

Cheers

Several days ago Joe and I found ourselves wanting to make sure we made the InfoPath “UserName()” function return all lower case characters and we decided to use the common XPath function “lower-case” but was surprised because it didn’t work. With a little research I found that we could use the InfoPath “Translate” function instead. I must say that it’s a little frustrating that some common XPath functions don’t work and you have to find the work around but if you do any InfoPath development you come accustom to it. Below is what we tried that didn’t work and what we did that did work.

What didn’t work: lower-case(xdUser:get-UserName())

What did work: translate(xdUser:get-UserName(), “ABCDEFGHIJKLMNOPQRSTUVWYXZ”, “abcdefghijklmnopqrstuvwyxz”)

Basically for “Translate” we had to specify every letter in the alphabet in UPPERCASE and then lowercase to make sure every character will get translated. For more information on “Translate” checkout this link http://office.microsoft.com/en-us/infopath/HA012330391033.aspx and do a search for “Translate” for the official description (It’s towards the bottom of the page).

If you foresee yourself using this more often then something to consider is creating a “Template Part” for this. It would be very simple because it would have two fields one called “translate_uppercase” and another “translate_lowercase” and each field default value would be the alphabet in the correct case. Then whenever you would want to use the “Translate” function you would add the “Translate Lower Case Template Part” to the form and build the expression as translate(fieldname, translate_uppercase, translate_lowercase). This would be a lot simpler than typing in all those letters and if you have more than one person doing InfoPath development it would create a standard way of doing it.

Cheers