By: Christian Holslin
The enterprise edition of SharePoint provides a powerful Forms interface called InfoPath Forms Services. This utility allows you to build electronic Forms which can be filled out, saved, and printed without leaving the browser. As is common with most Forms, where InfoPath is no exception, a certain degree of formatting is required to put the values in the Form into a business context. Conditional Formatting in InfoPath allows Form developers to meet the needs of their business users through highlighting, color-coding, and hiding (or showing) Form controls. With plain-text this is very easy, as it is with numbers as well. What do you do when you need to flag a text box in red because the date value has passed today's date? But InfoPath Forms Services loads SharePoint date fields as strings; this doesn't seem very helpful. So let's get creative.
Using a simple formula which I thought up while working on a Business Intelligence project, you can easily split a date into its individual components: Year, Month, and Day. Using these components you can create an integer representation of the date which can then be used by InfoPath's Conditional Formatting rules:
( YEAR * 10,000 ) + ( MONTH * 100 ) + DAY
If we apply this to today's date, we get:
( 2010 * 10,000 ) + ( 3 * 100 ) + 7 = 20,100,000 + 300 + 7 = 20,100,307
Using this formula, yesterday will always be less than today, tomorrow will always be greater than today, and today will always be equal to today. The important thing to mention here is: we can do this without code-behind. Code-behind severely complicates deployment; I have built wildly massive and complicated Forms implementing oodles of business rules, but I have never once used code-behind. You also don't need to use a Web Service. This can be implemented entirely in the InfoPath Form itself. Here's how you do it:
1. Get Yourself a Date
Yes, sometimes this can be hard. But it's easy with InfoPath: add or use an existing string or date field in your InfoPath Form. The example below is based on a rule which sets the value of a date field to today(). The code below assumes this field is called TodaysDate. The value resulting from the formula is stored into a separate, numeric field. You can use this on a string field as well, like the Created or Modified column pulled from a SharePoint list / library.
2. Convert to Integer
Using XSLT string manipulation, split the date string into parts and convert it to an integer using this XSLT formula. Use the Set a field's value action to run this formula:
number(concat(substring(string(TodaysDate), 1, 4), substring(string(TodaysDate), 6, 2), substring(string(TodaysDate), 9, 2)))
Uhh, what? That doesn't look like math? You're right, it isn't. But this formula applies the concept of the ( YEAR * 10,000 ) + ( MONTH * 100 ) + DAY equation without doing the math. Since InfoPath stores dates in ISO 8601 format you can simply extract all the numbers, concatenate them all together, then cast the result to an integer:
ISO Date: "2010-03-07"
Split ISO Date: "2010", "03", "07"
Concatenate Parts: "20100307"
Convert to Number: 20,100,307
3. Use It
That's it! Any date you convert to an integer can be used with most standard math Boolean expressions and operators like equals, less than, and greater than. You can't use add or subtract, however, because the integer doesn't represent the number of days since Year 0. But, if you create two number fields using this algorithm:
TodaysDate: 20,100,307 (representing March 7, 2010)
DateOnForm: 20,100,205 (representing February 5, 2010)
you can now tell which date is older, newer, or if they are the same using basic Conditional Formatting rules and XSLT formulas.
If you aren't a coder, can't use code-behind (or loathe the prospect of doing so), or don't want to create a Web Service just to check two dates, use this method directly in the InfoPath Form Template without ever having to leave the designer. You can meet your business goals and keep the deployment task simple using this one little formula.
--By: Christian Holslin