Sounds like their Portal records have two fields with one field identifying the shot (77A) and another that identifies the "take" (tk 1, tk 2...)
I think you have two choices:
- In their table, define a calculation field that combines these two values and relate to it.
- In your table, define two calculations that split your key (77A tk 1) -> key 1 = 77A, key 2 = tk 1 and then relate both of these fields to the matching pair of fields in their table.
Either approach should work.
Can you explain what you mean by:
I'd also like to harvest data they've recorded that's specific to every take.
BTW, if you ask me, their setup of 'one shot has many takes' makes more sense. Your way, you need to duplicate shot attributes in every take record of the same shot.
Each person is using the dominance that makes sense for what they do. In editorial, each shot has to be its own record, as it relates to one specific roll of the camera with a specific negative keycode address, etc. So discrete pieces of media are what is needed. The Shot is king.
But for VFX, the setup is king. And there are lots of takes with identical data. But sadly, it's not always identical. And being VFX I have to note the differences.
Let me try and describe this better because I don't think I decribed this very well.
In my database, in the tabled called [CODEBOOK] I have unique fields for
Take # (1, 2, 3, etc)And a bunch of other data unique to each rolling of the camera.
In their database in one table [SET DATA] they have
And a bunch of other data unique to the VFX needs of that set up.
Then portaled into that table (from another table called [TAKES] via a relationship of a random# not specific to anything), they bring in the takes and other data that they've chosen to record in the Takes table.
Now, I can pull in the SET DATA info by creating a table relationship using Slate to connect the CODEBOOK and SET DATA tables.
But I can't figure out how to pull in the unique Take information into each take in my database.
Furthering the issue, though not related to my current problem, is that they are grouping information informally in their takes table.
Example-- they might enter "1-3" in the takes field since nothing changed for those 3 takes. Human readable, yes, but when I'm turning over VFX shots for work and I've picked take 2 I don't CARE about takes 1 and 3. I'm prepared that I'm going to have to create more records in their takes table to handle this, duplicating several repetitive values. No biggy.
I hope that makes more sense.
"...Then portaled into that table (from another table called [TAKES] via a relationship of a random# not specific to anything),... "
That doesn't make much sense. I assume from a given Setup, their portal shows all the "takes" for that set up. If so, I suspect there's a field in takes that shows the slate (77a) number. That should be what you need.
Surprisingly there is not. Instead they set up a random ID and then named it differently in [Takes] and [Set Data]. That's the correlating field.
But visualizing tables for a second, Having related the "slate" field between [Codebook] and [Set Data], [Takes] is still floating off on its own.
Are you saying that I should create a calculation in [Takes] that gives me [Set Data]'s "slate" field and connect the tables that way instead?
Let's clarify a key point here: What's the relationship between Takes and SetData that is used by the portal you mentioned?
I was imagining SetData::Slate = Takes::Slate
If there's a serial number field in SetData that's being used, that's actually a much better design. Perhaps you should import that serial number and use it as your key on your side of the fence as well.
It is not.
in [SetData] that field is called "setupID" In [Takes] that field is called "takeID"
And if you have a moment, what makes this superior from a database design point of view?
I'd rather not link anything to it because it's so arbitrary, but I'll happily learn new philosophy that makes my life better.
But in essence I need to be connecting [Codebook] via [Takes] rather than [Set_Data] if I'm interpreting you correctly. (and then I have to make sure each take is represented in [Takes] or the record won't come into my codebook.
So the relationship is SetData :: SetupID = Takes :: takeID, where SetData is a serial number field?
The arbitrary nature of this Id code is what makes it a better design for using as a primary key. The generation of the serial ID is dirt simple and easy to enforce as a unique primary key. Other options require considerable more programming support with no added value to using them in a relationship. If you need other fields to label the records such as your slate ID, you can keep that as a separate field and use it in searches and sorts, but not as a key field in a relationship.
If I've read these posts correctly and am building the correct mental model of the tables. SetData has two fields of interest: SetupID and the "Slate" number. If you import the SetupID values into a new field in your Codebood table ( you could use the slate ID field as your "matching" value perhaps), you could use it to relate Codebook to Takes.
Well, it's after 5 and I got get started on my weekend. Perhaps others will help you out over the weekend. I'll try to check back here on Monday.
Try defining a calculation field in your CODEBOOK table =
where SetID is the "random# not specific to anything" field used as the matchfield to TAKES.
Then define the relationship from CODEBOOK to TAKES as matching both the calculation field and the take.
they are grouping information informally in their takes table.
BTW, I am not sure you follow my earlier remark. You have a table of takes, and so do they. The difference is that they also have a parent table for each group of takes - and you don't. Their structure is normalized, and yours isn't. You have no place to enter information that is specific to a group of takes (identified by a common Slate value), and enter it only once.