Infopath codeless programming (walkthrough) 2
Tested for SP2007 web forms, SP2010 list and library forms (note: the form has to be redesigned significiantly for list form because of different data fields structure).
Updated: 5 more forms added at the bottom: “work days calculation (excluding weekends and holidays)”, “work days calculation in the list form”, “day of the week calculation”, “Days since form was created” and “Hours Calculations”.
Special thanks: Karl D. Swartzendruber, Karin Edmondson
So what about so called “codeless programming” in InfoPath? As we all probably know, InfoPath can performed only 16 calulations without user interaction. But what if your business rules require you to iterate through 17 repeating elements? You will get a message box from InfoPath with phrase “The rules or code may be causing an infinite loop”. Yes, in InfoPath 16 is a very big number and 17 is considered infinite. To you and I this might seem stupid and arbitrarily restrictive, but Microsoft’s wisdom is greater than ours. Perhaps they know that 17th iteration would give us developers too much power. It would break the fine balance of Microsoft applications, make other development tools obsolete, and become unbearable for non InfoPath developers. But let’s drop the light irony and just accept that InfoPath already has more codeless programming power than mere mortals should ever need.
Today’s challenge is to find the difference in days between 2 datepicker controls disregarding the absence of datediff() function in InfoPath. We are going to show that the 16 iterations limit just can’t stop the true codeless programmer.
Disclaimer: During developing cycle author has been having in mind “finite automates” principles as well as primitive binary search.
Anyway, lets get to the business. Date Difference Calculation. Form to download.
Our recipy is the following: We will need 3 fields. 2 of them are date type fields with datepicker controls (startDate, endDate) and one (dayDifference) is a text type field represented by a simple textbox. All rules have exactly 1 action.
condition expession: ../my:endDate != ""
string value: 0;4096
condition expession: ../my:startDate != ""
string value: 0;4096
xdDate:AddDays(../my:startDate, substring-before(., ";")) != ../my:endDate and contains(., ";")
xPath expression: concat(substring-before(., ";") + substring-after(., ";") * ((translate(xdDate:AddDays(../my:startDate, substring-before(., ";")), "-", "") < translate(../my:endDate, "-", "")) * 2 – 1), ";", substring-after(., ";") div 2)
condition expession: contains(., ";")
xPath expression: substring-before(., ";")
Your form is ready!
What else can be done by applying same principles:
Here is form that shows amount of days since it was created
Form that shows current day of the week and day of the week from Date Picker control.
Same but with Hours: Hours calculation
The tricky one: Work days calculation (business days)
Work days implemented in the list form. This form was kindly provided by blog reader Karin Edmondson. The form won’t open in preview mode because of the obvious reason – the connections are pointed to Karin’s server. So your only option is to examine his formulas and try to reproduce them in your own form. Also another nice thing about this form is it takes into account half days.
That’s it. Happy codeless programming!
Posted under tags: Infopath, Date Picker Control