Other parts: 1, 3.
Bookmark that post
Updated: 3 more forms added at the bottom: “work days calculation (excluding weekends and holidays)”, “day of the week calculation” and “Days since form was created”.
Special thanks: Karl D. Swartzendruber
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.
startDate rule:
condition expession: ../my:endDate != ""
field: dayDifference
string value: 0;4096
endDate rule:
condition expession: ../my:startDate != ""
field: dayDifference
string value: 0;4096
dayDifference rules:
MainRule:
condition expession:
xdDate:AddDays(../my:startDate, substring-before(., ";")) != ../my:endDate and contains(., ";")
field: dayDifference
xPath expression: concat(substring-before(., ";") + substring-after(., ";") * ((translate(xdDate:AddDays(../my:startDate, substring-before(., ";")), "-", "") < translate(../my:endDate, "-", "")) * 2 – 1), ";", substring-after(., ";") div 2)
finalize rule:
condition expession: contains(., ";")
field: dayDifference
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.
The tricky one: Work days calculation (business days)
That’s it. Happy codeless programming!
Posted under tags: Infopath, Date Picker Control
