If formula problem ..

[sboyd]

I have two tables. Parent table, and a child table.

I need a calculation in a repeating table (the child table) that will
take one formula if a situation is true and take the other formula if
the sitution is false. I'm getting a date calculation difference
(actually the hours in between) and the dates can cross over into a
next day. I have gotten this to work in another form but I had to add
fields to the table design to get it to work. This form will allow
corrections direction in the database table. The database is designed
not to allow changes once records are closed. This is my way around
it. My problem is I can't add any fields to the table design.

These are my formulas

This is the true / false formula which is checking to see the days are
the same:
substring(@ACTUAL_DEPARTING_DATE, 9, 2) =
substring(@ACTUAL_ARRIVING_DATE, 9, 2)

If true then I want this formula:

round((((substring(@ACTUAL_ARRIVING_DATE, 12, 2) * 60) +
substring(@ACTUAL_ARRIVING_DATE, 15, 2)) -
((substring(@ACTUAL_DEPARTING_DATE, 12, 2) * 60) +
substring(@ACTUAL_DEPARTING_DATE, 15, 2))) / 60 * 10) / 10

If false I want this one.

round((((substring(@ACTUAL_ARRIVING_DATE, 12, 2) * 60) +
substring(@ACTUAL_ARRIVING_DATE, 15, 2)) -
((substring(@ACTUAL_DEPARTING_DATE, 12, 2) * 60) +
substring(@ACTUAL_DEPARTING_DATE, 15, 2))) / 60 * 10) / 10 + 24


All of the expressions are giving the correct numbers individually. I
know I could write My Formulas if the calculations that I needed were
from the primary table; however, I can't figure out how to write a
formula if the calcuation is needed in a child table.

Does anyone have a solution? And I don't know how to write code.
Thanks
siboyd

 

[sboyd]

I have two tables.  Parent table, and a child table.

I need a calculation in a repeating table (the child table) that will
take one formula if a situation is true and take the other formula if
the sitution is false.   I'm getting a date calculation difference
(actually the hours in between) and the dates can cross over into a
next day.  I have gotten this to work in another form but I had to add
fields to the table design to get it to work.  This form will allow
corrections direction in the database table.  The database is designed
not to allow changes once records are closed.  This is my way around
it.  My problem is I can't add any fields to the table design.

These are my formulas

This is the true / false formula which is checking to see the days are
the same:
substring(@ACTUAL_DEPARTING_DATE, 9, 2) =
substring(@ACTUAL_ARRIVING_DATE, 9, 2)

If true then I want this formula:

round((((substring(@ACTUAL_ARRIVING_DATE, 12, 2) * 60) +
substring(@ACTUAL_ARRIVING_DATE, 15, 2)) -
((substring(@ACTUAL_DEPARTING_DATE, 12, 2) * 60) +
substring(@ACTUAL_DEPARTING_DATE, 15, 2))) / 60 * 10) / 10

If false I want this one.

round((((substring(@ACTUAL_ARRIVING_DATE, 12, 2) * 60) +
substring(@ACTUAL_ARRIVING_DATE, 15, 2)) -
((substring(@ACTUAL_DEPARTING_DATE, 12, 2) * 60) +
substring(@ACTUAL_DEPARTING_DATE, 15, 2))) / 60 * 10) / 10 + 24

All of the expressions are giving the correct numbers individually.  I
know I could write My Formulas if the calculations that I needed were
from the primary table; however, I can't figure out how to write a
formula if the calcuation is needed in a child table.

Does anyone have a solution?  And I don't know how to write code.
Thanks
siboyd

I actually figured the formula out without the if's. Date and Time
differences are a significant shortfall for InfoPath. I hope this is
addressed in their next version. Until then this formula is working
for me to take one date and time and subtract another date and time to
get the number of hours difference in a fractional format. I hope it
helps someone else who has struggled with this.

round(((round((((substring(@ACTUAL_ARRIVING_DATE, 12, 2) * 60) +
substring(@ACTUAL_ARRIVING_DATE, 15, 2)) -
((substring(@ACTUAL_DEPARTING_DATE, 12, 2) * 60) +
substring(@ACTUAL_DEPARTING_DATE, 15, 2))) / 60 * 10) / 10 +
(substring(@ACTUAL_ARRIVING_DATE, 9, 2) -
substring(@ACTUAL_DEPARTING_DATE, 9, 2)) * 24) -
((substring(@ACTUAL_ARRIVING_DATE, 9, 2) -
substring(@ACTUAL_DEPARTING_DATE, 9, 2)) * 24) +
((substring(@ACTUAL_ARRIVING_DATE, 9, 2) -
substring(@ACTUAL_DEPARTING_DATE, 9, 2)) * 24)) * 10) / 10