Serialize Records By Category?

[nycpost]

I need to serialize records by category.

My database tracks takes for film production. There are many takes on a camera roll and many camera rolls within the table.

The takes on a camera roll were shot in a specific order.

The takes were shot on video, so they have timecode which we can use to determine the sort order.

I need to attribute new "Ink Code" numbers (which are similiar to KeyKode or timecode if you're familiar with film production) to each take. Ink Code numbers count in feet and frames and look like this:

0000+00

The first four digits count the feet. The last two digits after the "+" count the frames. There are 16 frames per foot. We don't need to concern ourselves with counting or rolling over the frames - a separate application will handle that - all I need to do is increment the feet.

The first take on every camera roll needs to have a starting Ink Code number of 0000+00.

All subsequent takes need to have a starting Ink Code number equal to the starting Ink Code number of the previous take + the duration of the previous take + a 10 foot buffer.

For instance, if take 1's starting Ink Code is 0000+00 and has a duration of 67+00, then take 2's starting Ink Code needs to = 0077+00.

I need to end up with records serialized with new Ink Code numbers like this:

I'd like to try to accomplish this using calculation fields and without scripting.

There are several bits of existing information that we can use to make the calculation:

Take #

Camera Roll #

Timecode Start

Timecode End

Timecode Duration

Ink Code Duration

I've successfully made this work using GetNthRecord to look at the previous record's Starting Ink and Duration and calculate a new Starting Ink. The problem with GetNthRecord is it's dependent on the sort order when referencing the current table. If I re-sort the table, the Ink numbers change. I need these numbers to remain fixed and never change.

I tried doing a self-join with a relationship based on Camera Roll, but the calculations keep returning "?" or pulling the wrong record's duration. I also tried sorting this relationship, but that had no effect.

Can anyone help me with this? Is there a way to determine a fixed sort order for a self-joined table and reference the correct record from that?

Thanks,

Mike

[comment]

I tried doing a self-join with a relationship based on Camera Roll, but the calculations keep returning "?" or pulling the wrong record's duration. I also tried sorting this relationship, but that had no effect.

I believe this should work. Can you post your file?

[nycpost]

Hi Comment,

Thanks for the help. Attached is the file. As you can see, it's only pulling data for one camera roll. All the others get ignored. The relationship is currently sorted, but when you unsort it there's no change.

Thanks,

Mike

Ink_Codes.fp7.zip

[comment]

See if this works for you:

Ink_CodesMod.zip

[nycpost]

OK, this is amazing. I've just been trying to wrap my head around what you did so I can really understand it. Thanks for this!

First of all, you made a self-join relationship that would pull a related record when the camera roll numbers were equal and the timecode start numbers were smaller in the related table. This is what allowed you to find the previous starting ink and duration, right? Do I need the relationship sorted in order for this to work, or could I undo that?

Then you used the not IsValid function to make sure there was always at least a "0000+00" value entered in the field.

Then you used the let function to define variables for the previous start, previous duration, sum of frames and sum of feet.

Your sumFr totals the frames from prevStart and prevDur and then sumFt uses Div to convert those frames to feet and adds them to prevStart and prevDur.

Then you used sumFr and sumFt in a calculation using SerialIncrement to increment from 0 by the required amount for the feet. And then SerialIncrement again in conjunction with Mod to return the number of frames left over.

This works great. When sorted and re-storted the numbers remain fixed. Quite excellent.

Now, if I wanted to zero out the frames for each new take, all I would need to do is augment the calc by removing the sumFr variable and simply concoctenate the first SerialIncrement with "+00", correct?

This is really fantastic. Thank you, Comment.

Mike

[comment]

Do I need the relationship sorted in order for this to work, or could I undo that?

The relationship MUST be sorted by timecode (or by take, if they are in the same order), descending, so that the first related record is the previous take - not an earlier one.

if I wanted to zero out the frames for each new take

Then you would have a constant result of "00000+00", would you not?

[nycpost]

The relationship MUST be sorted by timecode (or by take, if they are in the same order), descending, so that the first related record is the previous take - not an earlier one.

Ah, I see. In descending order the next least record would be the previous. Got it. I'll keep it sorted by timecode because sometimes takes are interrupted - like if they stop to shoot a cutaway.

Then you would have a constant result of "00000+00", would you not?

No, it should continue to increment the feet, but only zero-out the frames. For instance, if instead of doing this:

Let ( [

prevStart = Ink Codes_INK CODES::cInk Code Start ;

prevDur = Ink Codes_INK CODES::Ink Code Duration ;

sumFr = RightWords ( prevStart ; 1 ) + RightWords ( prevDur ; 1 ) ;

sumFt = LeftWords ( prevStart ; 1 ) + LeftWords ( prevDur ; 1 ) + Div ( sumFr ; 16 ) + 10

] ;

SerialIncrement ( "00000" ; sumFt ) & SerialIncrement ( "+00" ; Mod ( sumFr ; 16 ) )

)

We did this:

Let ( [

prevStart = Ink Codes_INK CODES::cInk Code Start ;

prevDur = Ink Codes_INK CODES::Ink Code Duration ;

sumFt = LeftWords ( prevStart ; 1 ) + LeftWords ( prevDur ; 1 ) + 10

] ;

SerialIncrement ( "00000" ; sumFt ) & "+00"

)

That would increment the feet, but leave the frames for that starting ink number at zero, right? It's nice to have the frames zeroed-out for each take just so it's a little easier to read and check a list by eye.

Mike

[comment]

Oh, I see - you are talking about rounding. I suppose that with a 10-foot buffer you can afford to truncate (i.e. round down always) - the maximum error will be 15 frames. But you should keep the + Div ( sumFr ; 16 ) part in sumFt. Otherwise your rounding error will go up to a maximum of 30 frames.