making fields respond to values in other records
I'm hoping there is a simple solution to this, but it evades me.
I work on film and video projects and I'd like a way to easily generate what is known as an "EDL" or edit decision list.
Here is the information that a typical EDL includes in the following order:
source tc start (tc stands for timecode--the unique address of a frame)
source tc finish
record tc start
record tc finish
or, as an example:
001 VTR001 VA1A2 05:23:14:06 05:23:16:09 01:00:00:00 01:00:02:03
002 VTR003 VA1A2 07:04:10:00 07:04:14:00 01:00:02:03 01:00:06:03
So in essence, this list places two video clips of approximately 2 seconds each onto a timeline, one after the other.
My problem lies in the record TC start and finish, as I need them to calculate and CHANGE based on shot sort order.
How can I define a field for the record TC start such that it takes the Record tc finish value from the previous record in a sort? I can then calculate the out value based on the length of the source tc values and continue to build my list.
And, in a sort, how can that same field be made "aware" that it is the FIRST field in the sort and insert a value of tc 00:00:00:00 (or whatever starting timecode I desire).
I will clarify further if necessary, but in essence, how can I define a calculation that is based on a previous record and how do I make that same field aware that there is NO previous record and should instead use a value that I give it? (suspect the later is a use of the IF or CASE command, but since I don't know how to make filemaker look at values in other records, I cannot experiment).
The end result is to create a machine readable EDL that will assemble the clips for viewing in the order I have defined.
Thanks heaps for the help.