TimeElapsed calculation help needed

[Johnnycode]

Hello, I need help with what would appear to be a basic calculation, but it's not working and I haven't been able to determine why not. My calculation is written as follows:

TimeElapsed = Table1::TimeEnded - Table1::TimeBegin with the result a Time.

TimeBegin is auto-entered; TimeEnded is a scripted button entry.

Both entry fields have good data, properly formatted. I want the result to appear as a time and formatted: hh:mm:ss

Problem: The result field is blank.

Can an amount of time be calculated this way? Or is it more involved?

Thanks

[Raybaudi]

Maybe that the solution is as simple as add this step at the end of your script:

Committ record/request [ ]

[comment]

Why are you referencing fully qualified field names? Is the calculation field in another table?

[Johnnycode]

No it isn't and I'm puzzled by that as well. FM added that to the calc and will not allow me to rewrite it as simply:

Result = Field1 - Field2

It is in a related table, but that should have nothing to do with it, I wouldn't think. Seems buggy.

[comment]

No it isn't, but it is? I am not sure how to read this. If the calculation is in a related table, it can have a LOT to do with it. For example, there might be no related record to supply the data.

[Lee Smith]

TimeElapsed = Table1::TimeEnded - Table1::TimeBegin with the result a Time.

Result = Field1 - Field2

Result should be a number

[Raybaudi]

Result should be a number

Should ?

[Johnnycode]

Daniele,

Thanks for your tip, but unfortunately it didn't help.

John

[Johnnycode]

Sorry for the confusion. The calculated field and the source fields are in the same table. The table has a relationship to another one.

[Johnnycode]

Thanks Lee, but it didn't fix it.

Will I be able to have the hhmmss formatting in a number field? Assuming I can ever get it to work.

[comment]

It seems that you have more than one occurrence of this table. Why don't you try the following:

Go to a layout of the table;

Go to Define Database and open the calculation formula for the field;

Make sure that the popup menu above the field names says "Current table";

Make sure that the popup menu 'Evaluate this calculation from the context of…" is set to the same table occurence;

Re-enter your formula.

If that doesn't help, post your file.

[Raybaudi]

John

probably you made a calculation in the wrong contest...

Can you post your file ? ( a clone w/o records )

Edit: comment is always quikier than me :

[Lee Smith]

No, you will need to covert it through a calculation.

I posted a CF on Brian Dunnings site that may help you. Link

There are others.

[Johnnycode]

Here's the file. It's a work in progress. The calc field is named Duration.

Thanks

CommunicationsLog_Clone.fp7.zip

[Lee Smith]

your duration field is broken, because it is coming from a file not attached, called communications log

Lee

[Johnnycode]

Did that happen when it was cloned for the upload?

[Raybaudi]

Try this one.

CommunicationsLog1.zip

[Johnnycode]

Yes, this is doing what I wanted it to, with one exception.

It is leaving the hh portion of the result set at 12, rather than 00. The times entered were 12:xx:xx, so it's as if it isn't evaluating the hh portion.

That's a little strange.

I can work around it by setting the Result field Time formatting to mmss, but it kills the ability to track events longer than 60 minutes.

Thanks for the help.

Edited January 12, 2009 by Guest

[Raybaudi]

there was an error on your formatting of the time

CommunicationsLog1.zip

[Johnnycode]

Thanks. That fixed it. I really appreciate your help.

John