6 Replies Latest reply on Jan 30, 2012 2:27 PM by ijontichy

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

    ijontichy

      Title

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

      Post

      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 & " " &
      edl_work_RECTCOUT)

      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.

       

       

        • 1. Re: Calculation showing up as "?" after 100 records--more complex than before.
          philmodjunk

          Like I posted on the record number issue, my best guess is that you've reached a limit inherent to the use of getNthRecord determined by the size of your found set. That doesn't solve the issue, however and I'm not sure as to what "fix" is best to use. Filemaker can use a summary field to compute a running total and this can work for many more than 100 records so if you can figure out a way to use such a summary field (and it looks to me like you can, if you can come up with a frame count or time figure to use in the running total...)

          Another option is to use a script with a loop or a single Replace Field Contents to put the running total type value in a data field immediately after pulling up the records. I don't recommend this approach if you can find an alternative. It can cause a pretty significant delay and if two users access the same data at the same time, the scripts run by each user will interfere and cause this to fail.

          • 2. Re: Calculation showing up as "?" after 100 records--more complex than before.
            ijontichy

            Thanks.   100 seems like a whimpy limit.   Is this worth reporting to the bug squad?

            • 3. Re: Calculation showing up as "?" after 100 records--more complex than before.
              philmodjunk

              I agree that 100 seems real small for FileMaker Pro (FM GO on an iOS, not so much...)

              It's worth a report, I do wonder if the custom function you are using adds to the "load" here...

              • 4. Re: Calculation showing up as "?" after 100 records--more complex than before.
                ijontichy

                The custom function is as follows...

                Turning Timecode into frames (assuming 24 frame TC)

                (Middle ( Right ( "00000000" & Int ( number ) ; 8 ) ; 1 ; 2 ) * 86400) +
                (Middle ( Right ( "00000000" & Int ( number ) ; 8 ) ; 3 ; 2 ) * 1440) +
                (Middle ( Right ( "00000000" & Int ( number ) ; 8 ) ; 5 ; 2 ) * 24) +
                Right("00" & Int ( number );2)

                Turning frames back into timecode (again assuming 24 frame per second TC)

                Right ( "00" & Div ( number ; 86400 ) ; 2 ) & ":" &
                Right ( "00" & Div (( Mod ( number ; 86400 )) ; 1440) ; 2 ) & ":" &
                Right ( "00" & Div (( Mod ( number ; 1440 )) ; 24) ; 2 ) & ":" &
                Right ( "00" & Div (( Mod ( number ; 24 )) ; 1) ; 2 )

                 

                Still seems pretty simple to me.   But I hear your point.

                I'm experimenting with using indexed calcs which are returning data, just not accurate (not starting from 1:00:00:00 for example).   Is there a way to force an index after a find?

                 

                • 5. Re: Calculation showing up as "?" after 100 records--more complex than before.
                  philmodjunk

                  I'm experimenting with using indexed calcs which are returning data, just not accurate (not starting from 1:00:00:00 for example).   Is there a way to force an index after a find?

                  I don't see why you would need to do that if you used a summary field set up as a running total of a Frames or Time field. The index has nothing to do with the value returned by the calculation field, it's a data structure used to facilitate fasters sorts and searches on your database table.

                  • 6. Re: Calculation showing up as "?" after 100 records--more complex than before.
                    ijontichy

                    Summary field works a charm...Set up a "counting field" that adds 86400 frames to the first record (giving me the 1 hour timecode start I prefer).

                    Summarized it and turned that into timecode for the rec out.

                    Took the same summary, subtracted duration and turned that into timecode for rec in.

                    Honkey Dorey.

                     

                    Thanks!