Repeating Tables - Calculated difference between two DateTime fiel

[phamilton7733]

Hi, I am new to Infopath and am using the Office 2007 Beta 2 for evaluation.
I have a requirement for a form that requires a date difference calculation
in a repeating table. Hey, doing this in Excel is easy, doing it in Access
is easy, but I want to use Infopath.

Basically, I need two repeating tables that look something like this:

Task | StartDate/Time | EndDate/Time | TotalMinutesForTask
aaa | Dec 25, 2006 9:00 AM | Dec 26, 2006 9:00 AM | (I need this auto
calculated).

The table will need to have unlimited rows that can be added/deleted (like
Excel would do). The reason why we want to use Infopath for the RAD
development approach, and we want to use the Windows Workflow services in
Office Sharepoint Server 2007 to attach a routing workflow to this Infopath
document.

I haven't found a way to have the calculated cell (the TotalMinutesForTask)
automatically update when a dependent cell is changed as I can't seem to use
the built in Formulas in the expression editor for Datetime calculations. I
have written a .NET function using VSTA that does the calculation, so this is
an option to use if someone can advise as to how I can wrap a .NET function
in the Expression editor for the text box. Other options may be to write a
"Calculate" button that will iterate through the rows and recalculate them.

I am hoping that I am not the first developer that has encountered this
challenge. If anyone can help me out it would be appreciated.

If Infopath is not the appropriate techology to use, please let me know of
another way I might accomplish this, we have all the MS development tools
available.

 

[[MSFT] AlexWein]

InfoPath is definitely the right technology to get this working.

Try setting a rule on the StartDateTime field - whenever StartDateTime
changes, set the value of EndDateTime to dateAdd(StartDateTime, 1 day).
DateAdd is a built-in InfoPath function that you can find in the Expression
Builder.

You may also want to set EndDateTime to be read-only.

 

[phamilton7733]

Thanks Alex. I don't think that your solution will work unless "DateAdd" can
be used to calculate a difference between two dates. Perhaps you can read
through the details again as I may have missed something if your solution
will meet my needs.

InfoPath is definitely the right technology to get this working.

Try setting a rule on the StartDateTime field - whenever StartDateTime
changes, set the value of EndDateTime to dateAdd(StartDateTime, 1 day).
DateAdd is a built-in InfoPath function that you can find in the Expression
Builder.

You may also want to set EndDateTime to be read-only.

Hi, I am new to Infopath and am using the Office 2007 Beta 2 for evaluation.
I have a requirement for a form that requires a date difference calculation
in a repeating table. Hey, doing this in Excel is easy, doing it in Access
is easy, but I want to use Infopath.

Basically, I need two repeating tables that look something like this:

Task | StartDate/Time | EndDate/Time | TotalMinutesForTask
aaa | Dec 25, 2006 9:00 AM | Dec 26, 2006 9:00 AM | (I need this auto
calculated).

The table will need to have unlimited rows that can be added/deleted (like
Excel would do). The reason why we want to use Infopath for the RAD
development approach, and we want to use the Windows Workflow services in
Office Sharepoint Server 2007 to attach a routing workflow to this Infopath
document.

I haven't found a way to have the calculated cell (the TotalMinutesForTask)
automatically update when a dependent cell is changed as I can't seem to use
the built in Formulas in the expression editor for Datetime calculations. I
have written a .NET function using VSTA that does the calculation, so this is
an option to use if someone can advise as to how I can wrap a .NET function
in the Expression editor for the text box. Other options may be to write a
"Calculate" button that will iterate through the rows and recalculate them.

I am hoping that I am not the first developer that has encountered this
challenge. If anyone can help me out it would be appreciated.

If Infopath is not the appropriate techology to use, please let me know of
another way I might accomplish this, we have all the MS development tools
available.

 

[phamilton7733]

I resolved this by implementing the code found in another post. To resolve
it, I created one "CalcLineItemMinutes" function and pass the DataDOMEvent
argument from the OnAfterChange event of each of the two dates to this
function.

The following is the code from the CalcLineItemMinutes function:

public void CalcLineItemMinutes(DataDOMEvent e)

{
IXMLDOMNode oNode = e.Site.selectSingleNode("..");
IXMLDOMNode date1Node = oNode.selectSingleNode("my:StartTime");
IXMLDOMNode date2Node = oNode.selectSingleNode("my:EndTime");

string s = "";
if (date1Node != null && date2Node != null)
{
try
{
DateTime date1 = DateTime.Parse(date1Node.text); //
Iso8601ToDate(date1Node.text);
DateTime date2 = DateTime.Parse(date2Node.text); //
Iso8601ToDate(date2Node.text);
TimeSpan ts = date2.Subtract(date1); // dt2 - dt1;
s = ts.TotalMinutes.ToString();
}
catch (Exception ex)
{
s = ex.ToString();
}
}
IXMLDOMNode diffNode =
oNode.selectSingleNode("my:LineItemMinutes");
diffNode.text = s;


}

The code for the "StartTime" and "EndTime" OnAfterChange events is identical
and has the following:

// The following function handler is created by Microsoft Office
InfoPath. Do not
// modify the type or number of arguments.
[InfoPathEventHandler(MatchPath =
"/my:myFields/my:group1/myataRow/my:EndTime", EventType =
InfoPathEventType.OnAfterChange)]
public void EndTime_OnAfterChange(DataDOMEvent e)
{
// Write your code here to restore the global state.

if (e.IsUndoRedo)
{
// An undo or redo operation has occurred and the DOM is
read-only.
return;
}
// A field change has occurred and the DOM is writable.
// Write your code here.
CalcLineItemMinutes(e);

}

Thanks Alex. I don't think that your solution will work unless "DateAdd" can
be used to calculate a difference between two dates. Perhaps you can read
through the details again as I may have missed something if your solution
will meet my needs.

InfoPath is definitely the right technology to get this working.

Try setting a rule on the StartDateTime field - whenever StartDateTime
changes, set the value of EndDateTime to dateAdd(StartDateTime, 1 day).
DateAdd is a built-in InfoPath function that you can find in the Expression
Builder.

You may also want to set EndDateTime to be read-only.

Hi, I am new to Infopath and am using the Office 2007 Beta 2 for evaluation.
I have a requirement for a form that requires a date difference calculation
in a repeating table. Hey, doing this in Excel is easy, doing it in Access
is easy, but I want to use Infopath.

Basically, I need two repeating tables that look something like this:

Task | StartDate/Time | EndDate/Time | TotalMinutesForTask
aaa | Dec 25, 2006 9:00 AM | Dec 26, 2006 9:00 AM | (I need this auto
calculated).

The table will need to have unlimited rows that can be added/deleted (like
Excel would do). The reason why we want to use Infopath for the RAD
development approach, and we want to use the Windows Workflow services in
Office Sharepoint Server 2007 to attach a routing workflow to this Infopath
document.

I haven't found a way to have the calculated cell (the TotalMinutesForTask)
automatically update when a dependent cell is changed as I can't seem to use
the built in Formulas in the expression editor for Datetime calculations. I
have written a .NET function using VSTA that does the calculation, so this is
an option to use if someone can advise as to how I can wrap a .NET function
in the Expression editor for the text box. Other options may be to write a
"Calculate" button that will iterate through the rows and recalculate them.

I am hoping that I am not the first developer that has encountered this
challenge. If anyone can help me out it would be appreciated.

If Infopath is not the appropriate techology to use, please let me know of
another way I might accomplish this, we have all the MS development tools
available.