AnsweredAssumed Answered

Calculating each record based off the previous

Question asked by dennis.a on Aug 15, 2018
Latest reply on Aug 16, 2018 by dennis.a

I'm trying to make a database that can calculate the "In Time" and "Out Time" of each Act of a TV show based on the "Duration". Each record "In Time" should start 1 frame later than the "Out Time" of the previous record, except the first record which always starts at "01:00:00:00".


I've attached a screen shot showing what a finished version would look like. The only two modifiable fields are the "FPS" and the "Duration" indicated in red. The "FPS" is a global variable. The green arrow indicates that the "Out" of one record is one frame earlier than the "In" of the next.


Screen Shot 2018-08-15 at 4.47.31 PM.png


Side note: I'm using a plugin to calculate Timecode. It lets me do timecode math in different frame rates, the most common being "24" and "30d", though there are several others.


I've succeeded in creating a rudimentary version of this file (attached along with the plug-in to make it work), but I can't use a global variable for the frame rate because the field for "In" refers to the previous "Out" which refers to that records "In", so variables have to be stored to get the database to work. As such, global variables aren't allowed.


The global variable would be very useful because sometimes I'll need to calculate a show at 24 FPS, sometimes at 30d FPS. Hard coding those values is how I got the rudimentary file to work, but I'd like my database to be more malleable.


Is this possible? Or have I pushed the limits of Filemaker Pro?


If you'd rather not download the file and install the plugin, here are the calculations for In and Out


IN Timecode - TC Add & Subtract is ( Timecode ; Frame rate ; Timecode )

If ( Get ( RecordNumber ) = 1 ; "01:00:00:00" ; TCFXM_Add ( GetNthRecord ( FOR_Act Out TC ; Get ( RecordNumber ) - 1 ) ;  24; "00:00:00:01") )


Out Timecode

TCFXM_Subtract ( TCFXM_Add ( FOR_Act In TC ; 24 ; FOR_Section Duration ) ; 24 ; "00:00:00:01" )