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.