Custom Time Field Solution

[jklick]

Hello,

I'm working on a database that needs to document the time stamps of films. This includes a "start time code" and an "end time code" which are used to calculate a duration field (end time - start time = duration). I have succeeded in doing this using the standard hh:mm:ss format, but it was brought to my attention that they need me to extend this format to include a frame number. Example:

hour:minute:second:frame

(where "frame" is a number between 1 and 30)

From my limited experience with FileMaker, I see two possible solutions and I think I would need help in performing either. I'm also open to any other potential solutions that are available.

The first potential solution consists of creating a custom field that has all the necessary elements and can be subtracted to calculate a duration field. I do not know if FileMaker is capable of doing this or if it would even be worth my time.

The second solution (that I've already tried to implement) involves creating a second - much tinier - field next to each time field so that I can include the frame numbers. The only issue I ran into was during the calculation of duration. When the number of frames is negative (start time frames > end time frames), this forces me to find a way to subtract a second out of the duration time field and add 30 frames to the duration frame field. I've been unable to successfully do this conditional calculation.

Hopefully, my explanation has been less confusing than useful. Any help is appreciated.

Thank you.

[comment]

I'd suggest you start from here:

http://www.fmforums.com/forum/showpost.php?post/209041/

[jklick]

Thank you so much for your help! I've thoroughly looked through all the links and I am reporting back.

By altering one of example projects that were posted, I've created a rough working copy of what I want to do.

http://www.pureoptimism.com/uploads/timecode.fp7

However, there are two problems I'm running into:

1. No data validation. In particular, when the duration is calculated, I hope to be able to utilize the appropriate maximum values (24 hours, 60 minutes, 60 seconds, and 30 frames).

2. If I want to change a single number, I have to retype everything or else I get some crazy result.

Thank you again for your guidance.

[comment]

I am sorry to say you haven't done your homework. In the post I have linked to:

For a more complex formula that includes entry validation, see the file attached here.

The same validation formula used in the file (or a very similar one) can also be seen in an earlier post in the same thread:

http://fmforums.com/forum/showpost.php?post/143387/

If I want to change a single number, I have to retype everything

That could be solved by making the replacement conditional:

Case (

PatternCount ( Field ; ":" ) ;

Field ;

Replace ( ... < as before > ... )

)

Or you could leave the entry as is and use a non-enterable calculation field for the display, with the entry field hiding behind it.

[comment]

BTW, I hope you know that you cannot calculate duration by just subtracting in from out, as if they were decimal numbers.

[jklick]

Sorry for taking so long to get back to you. You've been a wonderful help and your most recent posts gave me the extra "push" I needed to solve the problem.

Thank you so much!

-- Jonathan