Date calculations

[Johan Nilsson]

Hello,

This question is mostly directed to S.Y.M. Wong A Ton because I read his
great solution on calculating time differences without custom code on
http://enterprise-solutions.swits.net/infopath/time-calculations-using-rules.htm

I tried his example on a new blank form and it worked perfectly and I even
managed to implement a general deduction of lunch hours in that form.
However, once tested I transferred the idea over to my real form and the time
was no longer calculated =(

Could the problem occur because I have based this real form on a database
connection (access) and I have missed to reference the fields in a correct
manner? Any suggestions where in that example I easily could have missed to
reference it correctly?

 

[S.Y.M. Wong-A-Ton]

First, let's clear up that it's not HIS solution, but HER solution; I'm a
female.

If your times have the format "00:00:00" when they come out of the database,
the calculations should work. Are the times being displayed correctly in your
form? What data type are you using in your database?

 

[Johan Nilsson]

Hi again,

I would like to start by apologising for the gender mistake.

The actual date formats are not connected to the database but is only a
reference for the user of the form to calculate that the tasks entered cover
the whole working day (you enter billing information for 8 hours but you have
been at work 9 hours so obviously have missed one hour somewhere).

The database only gathers the information what kind of tasks you have
performed and after each day you submit this information to the database.
Because it worked without problems in the blank form I thought that this
connection to the data base could cause problems somehow?

The data source layout appears exactly the same as in your examples because
I have built the 'timeEntries' and 'timeEntry' groups directly from the
'myFields'.

I hope this clarified things.

 

[S.Y.M. Wong-A-Ton]

No, if the database fields are independent of the time fields, the connection
should not make a difference.

I'm afraid you probably missed a step then... The only thing I can advise
you is to try composing the formula to do the totalHours calculation,
yourself. Start out small by adding a rule on the startTime that sets the
value of totalHours to only number(substring(../my:endTime, 1, 2)); see step
16. This should return the amount of hours in the endTime. If totalHours is
being filled, continue on until the full formula is constructed. When you
have it working, you can add the conditions on the rule.

What the formula does is basically determine the total amount of minutes
after midnight for the startTime and endTime, subtract them and then divide
them by 3600 to return the total amount of hours elapsed between the two
times.

Let me know how things turn out or if you are experiencing more challenges.

 

[Johan Nilsson]

Hi,

Excellent suggestion, it was in the conditions set up for the rule that the
error occurred and I have now corrected it.

Thanks a million for your help =)

 

[S.Y.M. Wong-A-Ton]

Great! And you're very welcome!