AnsweredAssumed Answered

Calculation showing up as "?" after 100 records--more complex than before.

Question asked by ijontichy on Jan 30, 2012
Latest reply on Jan 30, 2012 by ijontichy


Calculation showing up as "?" after 100 records--more complex than before.


PhilModJunk helped solve a smaller set of this problem last week, but I'm not completely out of the woods.

I use calculations to format data into "Edit Decision Lists" that are used by a film lab to pull scan frames of imagery for use in digital post production.


The first line of every record of this edl needs to fit a very specific format.  Example:

100  A049_L012_08042Y V     C        14:28:19:08 14:28:21:00 01:10:23:05 01:10:24:21

The 100 represents the "EVENT NUMBER", which, thanks to PhilModJunk is calculated as follows:

Right ("000" & Get (RecordNumber);3)

The AO49_L012_08042Y represents the Source File from which the media is to be pulled.

The " V    C        " represent  "video only and as a cut"

The next two numbers represent the source timecode in and out of the source clip in question.


The last two numbers represent the record timecode IN and OUT of that event.   These are calculated because, depending on which records are found and the sorting of those records, the REC IN has to match the REC OUT of the previous record.  And the REC OUT has to match the duration of the source pull.   (If I'm requesting one second of source material and it's the first event in the list, then the REC IN value would be 01:00:00:00 and the REC OUT value would be 01:00:01:01 (the extra frame is due to how EDLS count material...I won't try to explain that here.   Please trust me when I say my math works for this purpose.


So the REC IN (actually called edl_work_RECTIN) is calculated as follows:

If ( Get(RecordNumber)=1 ; "01:00:00:00" ; GetNthRecord (edl_work_RECTCOUT; Get ( RecordNumber ) - 1 ) )

This way the first event is always at 1:00:00:00

And the REC OUT (actually called edl_work_RECTCOUT) is calculated as follows:

frames_as_24TC ( TC24_as_frames ( edl_work_RECTCIN )+(TC24_as_frames ( SRC_TCOUT_LE )-TC24_as_frames ( SRC_TCIN_LE )+1) )

A little messy because I need to convert from frames into timecode and back to do the math cleanly.

ALL of these fields work, but ...

When I aggregate them into the following field calculation:

If ( AV_Confirm="AUDIO" ; "" ; edl_work_EVENT_NUMBER_FORMATTED & "  " &
SRC_Filename_LE & " V     C        " &
SRC_TCIN_LE & " " &
frames_as_24TC ( TC24_as_frames ( SRC_TCOUT_LE )+1 ) & " " &
edl_work_RECTCIN & " " &

I get "?" after 100 records rather than the calculation.   (The added IF function filters out audio only events since these do not need to be scanned by the lab.)

I've checked the unaggregated solution to show that it's the REC IN AND REC OUT that fail, but here's the interesting thing (at least to uninformed me). 

Event 100 shows everything is correct

Event 101 returns as a question mark, but all of the components are are still calculating correctly, so why would there be a "?"

Event 102 and beyond returns as question mark but ALSO shows that the REC IN and REC OUT have started returning "?s"


I can reduce my foundset for processing to 100 record chunks but that is more than a little annoying given the number of records I'm dealing with.   Hoping for better.  Even if you cannot offer a solution I'd love to know why it's failing.


Filemaker Advanced Pro 11.03   Mac.   


Again, I salute the wisdom that is this group, especially it's Commodore, PhilModJunk.