10 Replies Latest reply on Feb 2, 2010 12:00 PM by philmodjunk

    Separating and utilizing different field datasets within a table

    shudder

      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

        • 1. Re: Separating and utilizing different field datasets within a table
          philmodjunk
            

          Seems like a looked up value base on a self join on your projects file could do the trick. 

           

          In your projects table you have a foreign key linking it to Shows, I'll call it ShowIDfk.

           

          In your relationships graph drag from ShowIDfk to outside the box and back to this same field. This creates a new table occurrence. Name this "instance": "SameShowFormat".

          Now drag from Projects::Format to SameShowFormat::Format to include it in the relationship. If you double-click the line linking your two tables, you should see:

           

          Projects::ShowIDfk = SameShowFormat::ShowIDfk AND

          Projects::Format = ShameShowFormat::Format

           

          For both your size and color space fields, select the looked up value auto-enter option and specify the field of same name from SameShowFormat for each.

           

          Then when you fill in project records, these fields will be blank for the first such record in a given show, but when you fill out a second record of the same show and format, these fields will automaticall look up values from the first such record for the same show.

           

          • 2. Re: Separating and utilizing different field datasets within a table
            mrvodka
              

            Why dont you have another related table that stores the different types. Lets call this table types.

             

            Types:

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

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

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

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

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

             

            You could make this show specific if need be. Then each project can reference a different "type". There is no need to duplicate this info each time.

             

             

            • 3. Re: Separating and utilizing different field datasets within a table
              philmodjunk
                

              I agree.

               

              A separate table is definitely a better approach to keep data consistent--particularly if you accidentally enter some information incorrectly and have to go back and change it at a later date.

              • 4. Re: Separating and utilizing different field datasets within a table
                shudder
                  

                I put Phil's solution to the test and it works for what we need, I think.  Thanks much, Phil!  You (both!) were very quick with comebacks to a post that was long and convoluted and I appreciate your efforts.

                 

                Vodka -- I'd considered an additional table (see my OP), but couldn't bring it together in my head.  You've offered up a solution by not only having the Size and Colorspace in that table, but also the Format, and I had not considered that until I saw you post.  Very cool.  

                 

                I did not yet put your solution to the test, but at first blush, it feels like it will potentially have a LOT of records in that table to house all the Format/Size/Colorspace permutations that could be out there, and I just don't know if that would be a good solution.  Hmmm...

                 

                As I see it at this early juncture, Phil's solution allows a SHOW to have no pre-existing Size or Colorspace set up for a new Project, but once I have set it (by populating that data in one PROJECTS record for a given SHOW), the lookup will then do the dirty work to bring that dataset into Size and Colorspace thereafter as I add PROJECTS to that SHOW.

                 

                Does this prevent a user from changing any particular PROJECTS record's Size and Colorspace values to something different after they have been auto-entered?  No, it does not, but 99.99% of the time, if the user sees something already there, then they won't touch it (and if it is the first PROJECTS record for a given SHOW, then it is fine if they set it manually!).  It is, of course, that .01% that could cause potential damage, I know...  So, I am going to have to think about how to make the users aware of this potential gaffe, and see if that is okay with them.

                 

                Thanks!

                 

                - Michael 

                • 5. Re: Separating and utilizing different field datasets within a table
                  philmodjunk
                    

                  Just keep in mind the main drawback to this approach: If you enter information incorrectly and then go on to create several additional project records for the same show and format before you catch the error, you'll have to update all of the records with the same show and format. With a table you'd only have to change one record.

                   

                  Relookup can ease the pain of such after the fact corrections, but the separate table approach is even cleaner and better normalization of your data.

                   

                  Where I described a self join, you'd link a separate table to your Projects table by the same two fields. You could use a filtered portal into this table to easily add/modify the records relevant to the current show.

                  • 6. Re: Separating and utilizing different field datasets within a table
                    shudder
                      

                    Hey Phil --

                     

                    Briefly: It works fine in a portal of PRJECTS from the SHOWS LO, but not on a layout associated to the PROJECTS table.  Is that right?  I have to get going right now, so haven't put much elbow grease into this as yet, but I was surprised it didn't just work since it works fine in a portal...  [shrug]

                     

                    ====

                    EDIT

                    ====

                     

                    Thought this over, and realized that NO, it will not work unless you are on a layout for the SHOWS table so that the self-join can complete itself.  I'd like to be able to go to the main PROJECTS LO and have it work there as well (I've allowed folks to create new PROJECTS records on the SHOWS LO via portal, but they may also do so from the PROJECTS LO as well... but these fields will not auto-enter because the relationship has no influence on the main PROJECTS LO).  Do I now need yet another self-join to get it to work on the main PROJECTS LO?

                     

                    - Michael  

                    • 7. Re: Separating and utilizing different field datasets within a table
                      shudder
                        

                      I seem to be getting wrapped around the pole here...

                       

                      Perhaps I am not thinking clearly, but I can't seem to make Vodka's separate table approach work at all.  Phil, I know you described the alternate approach as well as the relationship to build, but I'm not able to make it work at all.

                       

                      Again, my needs (as simply as I can state them):

                       

                      1. Be able to set unique values on PROJECTS records for Format, Size and Colorspace fields per SHOW from drop down lists of common values; then

                      2. Have the values of Size and Colorspace auto-enter on subsequent new PROJECTS records when I select the Format (based on previous values on PROJECTS records for that SHOW);

                       

                      NOTE: 1 and 2 work based on Phil's initial solution above when on a SHOWS LO only (where the table is SHOWS) and I am entering new PROJECTS in a portal there, but they do not work when I go to a PROJECTS table LO and am looking directly at PROJECTS records (not via portal).

                       

                      3. Be able to go to a PROJECTS LO and create new Projects records there, and have the same efficacy insofar as when I set a Format, the Size and Colorspace fields need to auto-enter data in from previous PROJECTS records for the SHOW (if they exist -- otherwise they are left blank) .

                       

                      Of course, having a separate table that houses Format, Size and Colorspace is the best approach, as Vodka has pointed out (call this new table "FORMAT_TYPES"), but I just can't seem to get my arms around how to make this happen without it feeling kludgey.  

                       

                      Certainly, I need the portal on the SHOWS LO to work as it does in Phil's solution, but on the PROJECTS LO I do not want a portal of multiple Format/Size/Colorspaces in view there.  Rather, what I need is a single set of the FORMAT_TYPES fields only on the PROJECTS LO (FORMAT_TYPES::Format, FORMAT_TYPES::Size and FORMAT_TYPES::Colorspace), and then when I set the Format field value on a new PROJECTS record, it will allow me to set Size and Colorspace manually.  Then when I create another PROJECTS record attached to that SHOW, and select the same Format value, the Size and Colorspace auto-enter the previous data.

                       

                      Thank you.

                       

                      - Michael 

                      • 8. Re: Separating and utilizing different field datasets within a table
                        philmodjunk
                          

                        Actually, my original suggestion should work from your projects layout just as well as in a portal on the Shows layout. Don't see why it wouldn't from this side of the computer screen. The relationship is based on a link between two TO's that both refer to projects--not shows after all.

                         

                        To implement a separate table, you'd need the following:

                         

                        New table as you've described with fields for Show, format, size and colorspace.

                         

                        A relationship:

                        Projects::ShowIDfk = Format_Types::ShowIDfk AND

                        Projects::Format = Format_Types::Format

                         

                        A layout for adding new Format_Types records to the table. A portal on a Shows layout is one option. You might have a second relationship for the portal as follows:

                        Shows::ShowIDpk = Format_TypesbyShow::ShowIDfk  (Format_TypesbyShow would have the same data source table as Format_Types).

                         

                        Then, you'd use this portal set up new format types records first, then create new projects records the refer to these types.

                        On your projects layout, you'd simply use the field tool to add the size and color space fields from Format_Types to the layout. When you then select a format, the matching size and colorspace data from the format types table will show on the layout.

                         

                        • 9. Re: Separating and utilizing different field datasets within a table
                          shudder
                            

                          > Actually, my original suggestion should work from your projects layout just as well as in a portal on the Shows layout. Don't see why it wouldn't from this side of the computer screen. The relationship is based on a link between two TO's that both refer to projects--not shows after all.

                           

                          Ack!  My bad.  It does work after all.  Sorry.  When I was on the PROJECTS LO, I was testing by hitting Cmd+N for a new record, and of course that works to create a new PROJECTS record there, but no SHOWS value is brought in because the foreign key is not copied in -- it is blank on a Cmd+N created record.  I'll have to work on that aspect in order to make it work to my liking.  Not quite there yet.

                           

                          Thank you for the detailed description on how to set up the additional table, but I am not liking how one deals with new records in it that are then later addressed by the SHOWS and PROJECTS:

                           

                          A layout for adding new Format_Types records to the table. A portal on a Shows layout is one option.

                          ...and...

                          Then, you'd use this portal set up new format types records first, then create new projects records the refer to these types.

                           

                          I just don't want the users to have to do this two-step -- that is, create SHOWS formats records first, then utilize them later in the PROJECTS records for that specific SHOW.  Too complicated.  Your original solution actuates this very nicely, though normalization of data is weak there, as you have pointed out.

                           

                          Thank you for your help.

                           

                          - Michael 

                          • 10. Re: Separating and utilizing different field datasets within a table
                            philmodjunk
                               Hmmm, come to think of it, if you allow creation of related records for the format_types table occurrence, you should be able to simply type the data into the fields on your project layout--providing you have valid values in your show and format fields. Muy bad for not realizing that the first time around...