3 Replies Latest reply on Sep 22, 2009 5:41 PM by comment_1

    Related Value List Puzzle



      Related Value List Puzzle


      I have a JOBS table, a VERSIONS table and a PAGES table. (Jobs can have many versions and each version can have many pages. Every job has at least one version and every version has at least one page.) Each table contains their unique keys (jobID, versionID, pageID).


      I have a layout (Run List) that I would like to base on the PAGES table. This Run List is unique to each job and contains three main fields that together will constitute the name of the page:  job number, version and page number (example: 12345_A_001).


      I want the job number to automatically populate the first field of each record on the Run List and then have the second field as a pull down list of only the versions contained within that job. Lastly, the third field will be simply an text field for manual entry.


      Also good to know: there will be tasks associated with only versions or with only pages (such as revisions to a version as a whole or page output individually).


      I've read everything I can find on the subject, tried all kinds of table occurrences based on what I've read and I still cannot make it all work together. So now, as much as I didn't want to, I'm asking for help. Thanks in advance.


        • 1. Re: Related Value List Puzzle

          Using the relationships I think you've created, you'd have to base your layout on Jobs not pages to see this conditional value list.


          Since you are on a Page record, there is one and only one matching version record and that's what's got you blocked.


          Why not spell out the purpose of your layout in a bit more detail? The answer may be to craft an additional relationship or it may be to modify your layout design.

          • 2. Re: Related Value List Puzzle

            Thank you for such a quick response!


            The purpose of the Run List layout is to show the individual items (pages in this case) in a job. There are fields to record how a page was processed, what type of output was produced, by whom and on what date and what level of revision it is at (R1, R2, etc.).


            Each job also has bigger parts that we call Versions. For example a book may consist of Version A which is the cover and it has four pages, Version B could the text with 80 pages.


            In the Run List layout I want to account for each page by the Version it is associated with as well as separately. I'm hoping that later on, in a calculated field I can combine the Job Number field, the Version field and the Page Number field as the "name" of each individual page for the purpose of sending it to an Output Schedule layout--using a script parameter. The Output Schedule layout will be comprised of many different jobs so having the pages uniquely identified is very important.


            I'd be fine using the Jobs table as the basis, I just haven't been able to figure out how to make that work. Perhaps I need another table that I haven't yet considered.

            • 3. Re: Related Value List Puzzle

              I am not quite sure what your question is. Given a chain of relationships:


              Jobs -< Versions -< Pages


              a page should be linked directly only to its parent version. The grandparent job is given implicitly by the version's relationship  to its parent job.



              However, if you want to associate a new page to a version, and also want to limit the choices by selecting a job first, you will need add an auxiliary field and a relationship for this: a JobID field in the Pages table, and a relationship to another occurrence of the Versions table:


              Pages::JobID = Versions 2::JobID


              Then define a value list using values from field Versions 2::VersionID, showing only related values starting from Pages, ad use it to select the version for the page.



              Note that the JobID field in Pages does not play any role in the core relationships, and could be even set to a conflicting value.