AnsweredAssumed Answered

Separating and utilizing different field datasets within a table

Question asked by shudder on Feb 1, 2010
Latest reply on Feb 2, 2010 by philmodjunk

Title

Separating and utilizing different field datasets within a table

Post

Hi Folks --

 

Sorry for the obtuse Subject of this post, but I really didn't know what to call it!  Please forgive the length of this post as well, but I want to be very clear about what I am doing so that maybe someone can see the wood for the trees (which I am currently unable to see...).  Please bear with me.

 

SYSTEM DESCRIPTION:

I have created a relational system from scratch for a post production group.  There are three main tables in this system:

 

SHOWS

PROJECTS

WORK UNITS

 

In the system, users create master SHOWS records that have related PROJECTS records under them, then PROJECTS have related WORK UNITS (hours) records under them.  Related records are all created via primary and foreign key associations and are entered via portals. 

 

All of this is working well and the records work nice and cleanly, and I am able to do just about any report they need by manipulating the three tables in concert with each other.  Spiffy.

 

A set of SHOWS records might look a little like this, with their related records under them:

 

SHOW 1

--PROJECT

----WORK UNIT

----WORK UNIT

----WORK UNIT

--PROJECT

----WORK UNIT

----WORK UNIT

 

SHOW 2

--PROJECT

----WORK UNIT

 --PROJECT

----WORK UNIT

 

----WORK UNIT

----WORK UNIT

 

SHOW 3

--PROJECT

----WORK UNIT

----WORK UNIT

----WORK UNIT

----WORK UNIT

--PROJECT

----WORK UNIT

--PROJECT

----WORK UNIT

 

HERE'S THE PROBLEM:

Inside the PROJECTS table is a field called "Format" that is a drop down with several options (Film, HD, 3D, 4x3, etc.).  These are static film format values: they are a set of custom values that are the same for all PROJECTS.  Inside this same table there are two fields called "Size" and "Colorspace" that I need to auto-populate based on the value of the Format value within that SHOW only as I create the PROJECTS records for that SHOW.  IOW, I may have one SHOW where the Format for the related PROJECTS under it is "HD" with a Size value of "1920x1080" and a Colorspace value of "Log."  The next SHOW that has PROJECTS under it may have that same Format of "HD," but alternately have a Size of "2048x1556" as well as a different Colorspace value of "WDFA-D65," so the values can be different from SHOW to SHOW, but all Size and Colorspace values within each PROJECT under a single SHOW will always be limited to whatever has been set up for that SHOW via the values we input for each Format (they stay the same throughout each SHOW, but can and will differ from SHOW to SHOW), like this (forgetting about WORK UNITS for the moment as they are not part of the trouble):

 

SHOW 1

--PROJECT - Format: HD; Size: 1920x1080; Colorspace: Log

--PROJECT - Format: HD; Size: 1920x1080; Colorspace: Log

--PROJECT - Format: HD; Size: 1920x1080; Colorspace: Log

--PROJECT - Format: 3D; Size: 2048x1556; Colorspace: Log

--PROJECT - Format: 3D; Size: 2048x1556; Colorspace: Log 

 

SHOW 2

--PROJECT - Format: HD; Size: 2048x1556; Colorspace: WDFA-D65

--PROJECT - Format: HD; Size: 2048x1556; Colorspace: WDFA-D65

--PROJECT - Format: 4x3; Size: 2048x1556; Colorspace: WDFA-D65

--PROJECT - Format: 4x3; Size: 2048x1556; Colorspace: WDFA-D65

--PROJECT - Format: 4x3; Size: 2048x1556; Colorspace: WDFA-D65

--PROJECT - Format: Film; Size: 1920x1080; Colorspace: Log

--PROJECT - Format: Film; Size: 1920x1080; Colorspace: Log 

 

Note that SHOW 1's PROJECT Format of HD has a different Size and Colorspace than SHOW 2's value for this same Format! 

 

So, if a user creates a new PROJECTS record in SHOW 2, for instance, then when they select Format and choose "HD" I will need the Size and Colorspace values to look at the PROJECTS records in that SHOW only and determine that since "HD" was selected as the Format value, then Size and Colorspace need to be set to the proper values for that show.  In this case, Size would be auto-entered as "2048x1556," and Colorspace would get "WDFA-D65" (I will be able to do some of this with script triggering once I figure out a way to actually get the needed data from the PROJECTS records for that particular SHOW).  Also, inspecting the above record sets, you can see that Size and Colorspace for each Format in a PROJECTS record are always the same -- they never differ -- but each Format can differ within the SHOW.

 

The hard part (for me) is figuring out a way to address PROJECTS records as a separate group within the overall PROJECTS table, such that when I create a new record in PROJECTS, I get the values associated to the SHOW only, and do not see nor am offered the other values from the other PROJECTS records.  In fact, these need to be auto-entered as the user selects the Format value!  If the Project is new, then of course, there won't be any Size or Colorspace values to auto-enter, so they will be left blank: The user will manually enter both Size and Colorspace for that new Project (both Size and Colorspace will be set up with a static list of all the sizes and colorspaces they use).  Thereafter when a new PROJECTS record is created, and the same format is selected as a previous PROJECTS record for that SHOW, then the Size and Colorspace values must be auto-entered (dealing with the issue of a user manually changing one of the PROJECTS records to something wrong/different I have not yet even begun to think about...)

 

So, I need to figure out a way to do this.  I've kicked around some ideas in my head but keep running into roadblocks as I play them out (you should see my white board!!).  I was thinking I might be able to create yet another table for just Size and Colorspace as they relate to each PROJECT, but that seems overkill, and I'm not quite sure if it would work in any case.  But, nothing I have thought up so far seems to be malleable enough to handle the varying sets of data that might come to me following the above scenarios of data entry and access, so I'm really open to anything (assuming I will understand the answer you might provide...).

 

Thank you if you've read this far!  Can anyone assist?

 

TIA.

 

- Michael

Outcomes