Calculating new, default date and time values

Similar to performing elapsed time calculations, creating a new date or time value based on a previous value used to require code – not anymore! With InfoPath 2007 you can use the new “addDays() ” and “addSeconds() ” functions to calculate a new date or time based on a previous value.

To illustrate this, think of a daily task planner: you enter a start time for the first task and specify a duration time and interval. The starting value for the next task should be the prior tasks’ starting time plus the prior tasks’ duration. Using the addDays and addSeconds functions will allow you to specify the new starting date and time based on a duration specified in days, hours, minutes or seconds without writing any code!

The following steps will create a sample Daily Task Planner to demonstrate how to implement these functions. A sample form template that implements this functionality is attached; make sure to save it locally before running it.

1. Create the data structure

a. Create a new browser-compatible form template
b. Add a repeating group named: gpTasks to the myFields collection
c. Add the following nodes to gpTasks:

d. Add the following node to the myFields collection:

Note: The “NextStartTimeValue” field is needed because using XPATH functions such as “preceding-sibling”, which could be used to get the prior elements values, are not supported in browser forms.

2. Create the View

a. Add gpTasks to the View as a Repeating Table
b. Change the Interval Text Box to a Dropdown List Box
c. Add the following entries to the Interval Dropdown list box:
        Days
        Hours
        Minutes
d. Add a new column to the right of the StartDateTime field and re-bind the newly created field to StartDateTime (this field will be used to display just the time value)
e. Change the format of this new field to: *9:46 AM

3. Add the expressions

a. Set a default value for the StartDateTime field to the current date and time when the form is first launched (note - we don’t want the current date and time to be entered once we have rows of data:)
      Add a Rule to the Open event of the form
      Add the following Conditions to the Rule:
          - The “count” of gpTasks = 1 (i.e. count(my:gpTasks) = 1)
          - The NextStartTimeValue field is blank
      Add an Action to set the StartDateTime field to the “now()” function

b. Add a “Set a field’s value” Rule to the Interval field to set the NextStartTimeValue field, with a condition that the rule should only fire if the Duration field is not blank. In the Value property for this Rule, we need to check the Duration and what Interval was selected so we can populate the NextStartTimeValue field with the correct date and time. Let’s take a look at how these expressions are created. (This information is based on using the advanced default value logic documented in this blog post)

You can use the “concat”, “substring” and “string-length” functions to create an “if/else” type of statement. First, we want to see if the selected Interval was “Days” – to do this, we use the “contains” function along with the substring function.

substring(xdDate:addDays(../my:StartDateTime, ../my:Duration), 1, contains(../my:Interval, "Days") * string-length(xdDate:addDays(../my:StartDateTime, ../my:Duration))

You would actually interpret this expression right-to-left in this manner: “If the Interval field contains “Days” then use the addDays function to add the value in the Duration field to the value in the StartDateTime field.”

We use a similar expression to check for the value of “Hours” in the Interval field and then use the “addSeconds” function to add the appropriate number of seconds:

substring(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60 * 60), 1, contains(../my:Interval, "Hours") * string-length(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60 * 60))

Lastly, if the Interval field does not contain Days or Hours then it must contain “Minutes”:

substring(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60), 1, contains(../my:Interval, "Minutes") * string-length(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60))

We then use the “concat” function to bring all of these expressions together to set the Value property:

concat(substring(xdDate:addDays(../my:StartDateTime, ../my:Duration), 1, contains(../my:Interval, "Days") * string-length(xdDate:addDays(../my:StartDateTime, ../my:Duration))), substring(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60 * 60), 1, contains(../my:Interval, "Hours") * string-length(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60 * 60))), substring(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60), 1, contains(../my:Interval, "Minutes") * string-length(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60))))

c. Add a “Set a field’s value” Rule to the Duration field to also set the NextStartTimeValue field – again, add a condition where we only want this rule to fire if the Interval field is not blank. Use the same expression as above to set the Value property:

concat(substring(xdDate:addDays(../my:StartDateTime, ../my:Duration), 1, contains(../my:Interval, "Days") * string-length(xdDate:addDays(../my:StartDateTime, ../my:Duration))), substring(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60 * 60), 1, contains(../my:Interval, "Hours") * string-length(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60 * 60))), substring(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60), 1, contains(../my:Interval, "Minutes") * string-length(xdDate:addSeconds(../my:StartDateTime, ../my:Duration * 60))))

d. Add a “Set a field’s value” Rule to the Repeating Table to set the StartDateTime field to the value stored in the NextStartTimeValue field (this is what will set the Start Date and Time fields when a new row is added to the table)

4.  Test!

Here is a sample completed Task Planner:

NOTE: Because this is a “no code” solution, if you modify an existing start date or time there is no way to enumerate the balance of the nodes to reflect this change. A feature such as this would require code.

Scott Heim
Support Engineer

DailyTaskPlanner.xsn