7 Replies Latest reply on Jan 13, 2009 8:35 PM by ijontichy

    calculations dependent on fields in other records

    ijontichy

      Title

      calculations dependent on fields in other records

      Post

      I have a cumulative counting task having to do with multiple records.

      It's a timecode thing from editing but in essence...

      imagine three records

      A

      B

      C

       

      I need to be able to have a "timecode start" field in Record C that takes its value from the "timecode end" field in Record B.   In turn, the "timecode start" field of record B needs to take its value from the "timecode end" field in Record A.

       

      And, depending on how the fields are sorted, whichever record is first (in this case Record A), I need the field to know that it's the first in the list and to accept a global value.  

       

      I hope that's clear enough.

        • 1. Re: calculations dependent on fields in other records
          fitch
            

          Try these calculations. 

           

          Value from previous record:

          GetNthRecord( timecode end; Get(RecordNumber) - 1 )

           

          First in list:

          Get(RecordNumber) = 1

          • 2. Re: calculations dependent on fields in other records
            ijontichy
              

            Fitch.   Interesting and I will try it...but if I'm not mistaken, you're assuming that the order of the files never changes--that by assigning a record number I always know which record came before and which record came after.   

             

            But what if I sort for different criteria?   (Date of last submission, for example, rather than linear story order).    I suppose I could have a record number field that I just repopulate ever time I need to publish an EDL.     But I'm still confused about the first field in the run of records.  

             

            but I will play a bit and try what you're saying.   Thanks!

            • 3. Re: calculations dependent on fields in other records
              flybynight
                 Will the timecodes stay once they are entered, or will sorting the records change the timecodes? If they stay, you could do it with an auto-enter calculation, rather than a calculation field. That way, it will do the calculation based on the other record like the post above, but then it won't change that value when you sort.
              • 4. Re: calculations dependent on fields in other records
                ijontichy
                  

                They will change with each sort.

                 

                I'm attempting to create an edit decision list for video clips.    So an edl consists of the following information, at a minimum

                 

                event #

                source (tape# in my case)

                tracks (are you cutting video or audio or both--in my case it's all video)

                Transition (is it a cut or dissolve?   In my case it's all hard cuts)

                source TC in (the starting timecode of the source clip)

                source TC out (the ending timecode of the source clip)

                rec TC in (where the source clip starts on the record timeline)

                rec TC out (where the source clip stops on the record timeline)

                 

                I want to be able to sort my source clips into the order I want and then position themselves sequentially on the timeline in that order.   So the record in and out will change with every sort (and be dependent on what came before it), with the exception of the very first clip that has to have a specificred TC in value.

                • 5. Re: calculations dependent on fields in other records
                  raybaudi
                    

                  Hi pirxx

                   

                  Case (
                  Get ( RecordNumber ) = 1 ; Global value ;
                  GetNthRecord ( timecode end ; Get ( RecordNumber ) - 1 )
                  )

                   

                  To obtain different values by record position, simply make the calculation UNSTORED.


                  • 6. Re: calculations dependent on fields in other records
                    GrahamKing
                      

                    I have a similar problem.

                    My Filemaker database has one record per person per month (every month is usually different).

                     

                    Some calculations used in Micky Mouse's June record need his year-to-date (YTD) values, and so is dependent upon all of his "previous" records, but not at all on any of Donald Duck's records.

                     

                    I could define a calculated YTD field in each record, such that the February record uses the January record, and "passes on" to the calculated field in the March record.

                     

                    Or, a calculated YTD field in e.g. his June record could fetch all of the previous months.

                     

                    Or there is some other magic wand to use...

                     

                    But how to do any of the above, given that all the records are pretty randomly sorted...?

                    • 7. Re: calculations dependent on fields in other records
                      ijontichy
                         Thanks!   That did it.